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.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 and Triggers”.