User-Defined Variables

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.

Using SQL Comments

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:

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:

1.

Log in to the mysql client and select the accounting database.

USE accounting;

2.

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.

Figure 10.20. A new record is added, with the user-defined variable in place of the salt value.

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.

5.

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() function.

Tips

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.