List:General Discussion« Previous MessageNext Message »
From:Moon's Father Date:February 12 2008 9:39am
Subject:Re: stored procedure, parameter type help needed
View as plain text  
Just use the type varchar.I always use it in my regular life.

On Feb 11, 2008 4:44 PM, Magne Westlie <magne@stripped> wrote:

>
>
> Jerry Schwartz wrote:
> >>    SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT
> >> uid
> >> FROM temp_uids);
> > [JS] Couldn't you replace the " WHERE user_id IN (SELECT uid FROM
> > temp_uids)"
> > with a simple JOIN? If "IN" is badly optimized, as I've read here,
> wouldn't
> > that be more efficient? Or am I (as usual) missing something?
>
> I may be the one missing something :-). I have tried to think out of the
> box and use other solutions, but haven't come up with a way that works
> without using "IN".
>
> The query are to be used in a calendar-ish application, for finding when
> people are free to attend meetings. The ids I send as parameter is the
> ids of users that I want to check availability for. The id-list may
> contain between 1 and 50 user ids.
>
> Maybe I could use
>
> JOIN ... ON (user_id=1 OR user_id=2 OR user_id=5...)  (?)
>
> but as far as I understand, I'd still have to generate this list as a
> string because I do not know how many users to check for, and then
> CONCAT the query, PREPARE etc. as described in Peter Brawley's email.
> Then I think I prefere using "IN".
>
> As for the optimization of "IN", I've read the following in the manual:
>
> "The search for the item then is done using a binary search. This means
> IN is very quick if the IN value list consists entirely of constants."
>
> (
> http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in
> ")
>
> , so if I am to rely on the doc, it should be "very quick" the way I use
> it.
>
> Thanks for your comment Jerry.
>
>
> Magne
>
>
>
>
> >
> >>    DROP PREPARE stmt;
> >> END; ::
> >> DELIMITER ;
> >>
> >> CALL get_users('(2), (3)');
> >> -----------------------------------------------------------
> >>
> >>
> >> MW
> >>
> >>
> >> Peter Brawley wrote:
> >>> Hi Magne
> >>>
> >>>  >...the query I actually want to use this in, is a 100 line query
> >> with
> >>> lots of arguments.
> >>>  >I don't feel to good about creating it into a bunch of strings
> (16)
> >>> that I have to
> >>>  >concatenate with the variables inbetween.
> >>>
> >>> The only alternative I know for current versions of MySQL is to
> >> assemble
> >>> the query in the app layer.
> >>>
> >>>  >Also, I was moving the query into a stored procedure because I
> >> wanted
> >>> to make
> >>>  >the request fast, and the concatenating and string handling takes
> >> some
> >>> of that away.
> >>>
> >>> Unfortunately MySQL sprocs do not yet deliver this advantage.
> >>>
> >>> PB
> >>>
> >>> -----
> >>>
> >>> Magne Westlie wrote:
> >>>> Hi,
> >>>>
> >>>> Thanks a lot Peter, that was useful and it worked fine. The only
> >>>> problem is that the query I actually want to use this in, is a 100
> >>>> line query with lots of arguments. I don't feel to good about
> >> creating
> >>>> it into a bunch of strings (16) that I have to concatenate with the
> >>>> variables inbetween.
> >>>> Also, I was moving the query into a stored procedure because I
> >> wanted
> >>>> to make the request fast, and the concatenating and string handling
> >>>> takes some of that away.
> >>>>
> >>>> Is there another way?
> >>>>
> >>>> Magne
> >>>>
> >>>>
> >>>>
> >>>> Peter Brawley wrote:
> >>>>> Magne,
> >>>>>
> >>>>> Sorry, the server is down at the moment, here is the entry ...
> >>>>>
> >>>>> To have an sproc accept a variable-length parameter list for an
> >>>>> |IN(...)| clause in a query, code the sproc to |PREPARE| the
> query
> >>>>> statement:
> >>>>> |
> >>>>> DROP PROCEDURE IF EXISTS passInParam;
> >>>>> DELIMITER |
> >>>>> CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
> >>>>> VARCHAR(1000) )
> >>>>> BEGIN
> >>>>>  SET @qry = CONCAT( qry, param, ')' );
> >>>>>  PREPARE stmt FROM @qry;
> >>>>>  EXECUTE stmt;
> >>>>>  DROP PREPARE stmt;
> >>>>> END;
> >>>>> |
> >>>>> DELIMITER ;
> >>>>> |
> >>>>> For this example, the query string should be of the form:
> >>>>> |
> >>>>> SELECT ... FROM ... WHERE ... IN ( |
> >>>>> but so long as it has those elements, it can be as complex as
> you
> >>>>> like. When you call the sproc:
> >>>>> 1. Quote each argument with a /pair/ of single quotes,
> >>>>> 2. Separate these quoted arguments with commas,
> >>>>> 3. Surround the whole |param| string with another set of single
> >> quotes:
> >>>>> |
> >>>>> CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
> >>>>> ('''abc'',''def'',''ghi''' ); |
> >>>>> ||
> >>>>> ||||PB
> >>>>>
> >>>>>
> >>>>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >> infoshop.com
> >
> >
> >
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>


-- 
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Thread
stored procedure, parameter type help neededMagne Westlie7 Feb
  • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
  • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
    • Re: stored procedure, parameter type help neededMagne Westlie7 Feb
      • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
        • Re: stored procedure, parameter type help neededMagne Westlie8 Feb
          • RE: stored procedure, parameter type help neededJerry Schwartz8 Feb
            • Re: stored procedure, parameter type help neededMagne Westlie11 Feb
              • Re: stored procedure, parameter type help neededMoon's Father12 Feb