Skip navigation links

Additional languages
23.2.8 Conditions and Handlers »
Section Navigation      [Toggle]

23.2.7.3. SELECT ... INTO Statement

SELECT col_name[,...] INTO var_name[,...] table_expr

This SELECT syntax stores selected columns directly into variables. Therefore, only a single row may be retrieved. 

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

User variable names are not case sensitive. See Section 8.4, “User-Defined Variables”.

Important

SQL variable names should not be the same as column names. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable. For example, in the following statement, xname is interpreted as a reference to the xname variable rather than the xname column: 

CREATE PROCEDURE sp1 (x VARCHAR(5))
  BEGIN
    DECLARE xname VARCHAR(5) DEFAULT 'bob';
    DECLARE newname VARCHAR(5);
    DECLARE xid INT;
    
    SELECT xname,id INTO newname,xid 
      FROM table1 WHERE xname = xname;
    SELECT newname;
  END;

When this procedure is called, the newname variable returns the value 'bob' regardless of the value of the table1.xname column.

See also Section D.1, “Restrictions on Stored Routines, Triggers, and Events”.


User Comments

Posted by Aurel Pekarcik on March 11 2006 10:50pm[Delete] [Edit]

When SELECT returns no rows, then the variables stay unchanged !
Dont you suppose that the variables will assigned to NULL.

Posted by Ravi Krishnan on May 9 2008 8:29am[Delete] [Edit]

If you use table aliases, then you can get around the restriction of variable names to be different from referenced table column names, since you are avoiding ambiguity.

In the example above:

SELECT T.xname,id INTO newname,xid
FROM table1 T where ...
SELECT newname;


Returns the value of the xname column of table1.

Add your own comment.