19.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 9.3, “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 I.1, “Restrictions on Stored Routines and Triggers”.
