How to Use Variables in a Select Statement MySQL
- 1). Open MySQL and access the query tool.
- 2). Type in and execute the following queries:
SET @var1=5;
SET @var2='Hello';
SET @var3=True;
SELECT @var1, @var2, @var3;
MySQL displays each variable in its own column because of the differing data types. Note that the third variable uses a Boolean value, but MySQL converts it to "1," the integer equivalent of "true." - 3). Type in and execute the following queries:
SET @var1=2;
SET @var2=3;
SET @var3=4;
SELECT @var1+@var2+@var3;
MySQL displays the result in a single column and shows "9," the sum of the three variable values. - 4). Type in and execute the following queries:
SET @var1 = b'1000011';
SELECT @var1;
This query converts the binary number to a decimal, then returns the ASCII value, in this case, the capital "C" character. - 5). Type in and execute the following queries:
SET @var1 = CAST(b'10001' AS UNSIGNED), @var2 = b'10001'+10;
SELECT @var1, @var2;
MySQL uses the Cast function to convert the binary parameter to an unsigned integer, which in this case equals 17 and assigns it to the first variable. It also converts the second variable from binary to an integer and then adds 10 to it to total 27. - 6). Type in and execute the following queries:
SET @var1='value';
SELECT * FROM table_name WHERE column = @var1;
This query uses the variable in the "where" clause to filter the results of the select statement.