While not new to MySQL, user-defined variables are an unheralded feature. Such variables work as you would expect: you can temporarily store values in them and then use those variables where the values are needed.
You can use any alphanumeric character in your variable's name, as well as the underscore, period, and dollar sign. The variable's name is preceded by @, to indicate that it is a variable. Whatever name you use should be treated as if it were case-sensitive. This is just good form; as of MySQL 5.0 variable names are not case-sensitive.
To assign a value to a user-defined variable, use one of these SQL commands:
SELECT @var := 'value'
SET @var = 'value'
SET @var := 'value'
It doesn't matter whether you use SELECT or SET, but you must use := with SELECT, whereas SET can use = or :=. Regardless, note that you don't have to declare a variable before assigning a value to it. You can just do:
SET @num = 2
SET @name = 'Sam'
To define a variable using a value stored in a table, you can use a SELECT query:
SELECT @var:=some_column FROM tablename WHERE condition
The only trick here is that you'll want just a single row and column returned so that just one value is assigned to the variable.
A relatively minor topic is that of using comments within your SQL commands. MySQL supports three syntaxes for making comments. If you use # or (two dashes followed by a space), all text until the end of the line is a comment. Examples:
SELECT NOW() # Comment
SELECT NOW() -- Comment
If running either of these queries within the mysql client, you would place the semicolon before the comment, or else the query would not be executed.
Comment blocks can be made using /* and */. Anything between these two indicators is a comment, even over multiple lines:
SELECT /* What
time is it? */ NOW()
A variation on this uses /*! and */. The premise is the same, but you can place MySQL-specific or version-specific SQL within the tags. When running the query on MySQL (or using the right version of MySQL), the SQL within the comment tags is executed. Otherwise, the SQL is ignored.
Once you've established any variable, it can be used in other queries:
INSERT INTO some_table (some_num_col, some_text_col) VALUES (@num, @name)
The catch in such a query is the variable shouldn't be quoted, even if it is a string (like @name in that example).
As a demonstration of this I'm going to change how new accounts are added to the logins table (in the accounting database). The basic query is
INSERT INTO logins (client_id, login_name, login_pass) VALUES
(client_id, AES_ENCRYPT('name', 'w1cKet'), SHA1('password'))
At issue is the encryption salt (w1cKet), which is key to the encryption process. Having this value hard-coded into the query isn't very secure, particularly if the query is being run from PHP, Perl, or Java. Instead, I'll store the salt in a database table. To use it in a query, I will then select it and assign the value to a MySQL user variable.
To use a database-stored salt:
Log in to the mysql client and select the accounting database.
Create and populate a table (Figure 10.18
Figure 10.18. A special table, consisting of just one column, will be used to store the encryption salt value.
CREATE TABLE sodium (
salt CHAR(6) NOT NULL
INSERT INTO sodium (salt) VALUES
This table, sodium
, will store the encryption salt value in its one column. The INSERT
query stores the salt, which will be retrieved and assigned to a user-defined variable as needed.
You may also want to consider giving the table a less obvious name.
Retrieve the stored salt value (Figure 10.19
Figure 10.19. A user-defined variable is assigned the value retrieved from a table.
SELECT @salt:=salt FROM sodium;
This line retrieves the stored salt value from the sodium
table and assigns this to @salt
(the figure shows the results of the SELECT
Add a record to the logins
table (Figure 10.20
Figure 10.20. A new record is added, with the user-defined variable in place of the salt value.
INSERT INTO logins (client_id, login_name, login_pass)
VALUES (3, AES_ENCRYPT('Isabella', @salt), SHA1('atticus7'));
A standard INSERT
query is run to add a record to the logins
table. In this case, @salt
is used in the query instead of a hard-coded salt value.
Decrypt the stored login names (Figure 10.21
Figure 10.21. Every login record is retrieved and decrypted, again using @salt.
SELECT @salt:=salt FROM sodium;
SELECT client_id, AES_DECRYPT(login_name, @salt) AS name FROM logins;
The first step retrieves the salt value so that it can be used for decryption purposes. (If you followed these steps without closing the MySQL session, this step wouldn't actually be necessary, as @salt
would already be established.) The @salt
variable is then used with the AES_DECRYPT()
User variables are particular to each connection. When one script or one mysql client session connects to MySQL and establishes a variable, only that one script or session has access to that variable.
You cannot incorporate a user-defined variable as a parameter in a LIMIT clause.
Never assign a value to and later reference a user-defined variable within the same SQL statement. These two steps must always be accomplished in two separate queries, as in these examples.