The difference between MySQL @ variables and variables and how to judge the uniqueness of records

DELIMITER//Drop PROCEDURE if EXISTStest.express;Create PROCEDUREtest.express ()BEGIN  Select Count(1) into @a  fromTest.test_userwhereUserid='User'; Select @a; IF @a>1  Then   Select 'Hello World'; ELSE   Select 'Error'; END IF;END//Call test.express ();

Pass

Select COUNT (1) into @a from Test.test_user where userid= ' user ';

The condition is judged by whether the @a variable is greater than 1.

Note: Here we use the @ variable, so what is the difference between variables?

(citation: http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference)

MySQLHas the concept of user-defined variables.

They is loosely typed variables that may is initialized somewhere in a session and keep their value until the session end S.

They @ is prepended with a sign, like this:@var

Can initialize this variable with a SET statement or inside in a query:

SET @var :=1SELECT@var2 :=2

When you develop a stored procedure MySQL in, you can pass the input parameters and declare the local variables:

// CREATE PROCEDURE prc_test (varint)BEGIN    DECLARE  INT ;     SET = 1 ;     SELECT   var2; END ; // DELIMITER;

These variables is not prepended with any prefixes.

The difference between a procedure variable and a session-specific user-defined variable is this procedure variable is REI Nitialized to each time NULL the procedure are called, while the session-specific variable are not:

CREATE PROCEDUREprc_test ()BEGIN    DECLAREVar2INT DEFAULT 1; SETVAR2:=Var2+ 1; SET @var2:= @var2 + 1; SELECTVAR2,@var2;END;SET @var2 = 1; Call Prc_test (); Var2@var2---   ---2     2Call prc_test (); Var2@var2---   ---2     3Call prc_test (); Var2@var2---   ---2     4

As can see, var2 (procedure variable) are reinitialized each time the procedure are called, while @var2 (SESSION-SPECIF IC variable) is not.

(in addition to user-defined variables, MySQL also have some predefined "system variables", which may "Global V Ariables "such as @@global.port or" session variables "such as @@session.sql_mode ; these" session variables "is unrelated to session-specific user-defined variables.)

Appendix: (http://dev.mysql.com/doc/refman/5.0/en/user-variables.html)

user-defined Variables

You can store a value in a user-defined variable in one statement and then refer to it later in another statement. This enables-pass values from one statement to another. user-defined variables is session-specific. That's, a user variable defined by one client cannot being seen or used by other clients. All variables for a given client session is automatically freed when that client exits.

User variables is written as @var_name , where the variable name var_name consists of alphanumeric characters, " "," " . _ , and " $ ". A user variable name can contain other characters if you quote it as a string or identifier (for example, @‘my-var‘ , @"my-var" , or @`my-var` ).

User variable names is not case sensitive in MySQL 5.0 and up, but is case sensitive before MySQL 5.0.

One-to-set a user-defined variable is by issuing a SET statement:

SET @ var_name   expr  [, @ var_name ] ... expr 

SETfor, either = or := can is used as the assignment operator.

You can also assign a value to a user variable in statements other than SET . In this case, the assignment operator must are and not because the latter are treated as the comparison := = Operat or in = non- SET statements:

SET @t1=1, @t2=2, @t3:=4;SELECT @t1, @t2, @t3, @t4 := @[email protected][email protected];+------+------+------+--------------------+| @t1  | @t2  | @t3  | @t4: = @[email protected][email protected] |+------+------+------+--------------------+|    1 |    2 |    4 |                  7 | +------+------+------+--------------------+

User variables can assigned a value from a limited set of data Types:integer, decimal, floating-point, binary or Nonbi Nary string, or NULL value. Assignment of decimal and real values does not preserve the precision or scale of the value. A value of a type other than one of the permissible types are converted to a permissible type. For example, a value has a temporal or spatial data type is converted to a binary string.

If a user variable is assigned a nonbinary (character) string value, it has the same character set and collation as the St Ring. The coercibility of user variables is implicit as of MySQL 5.0.3. (This was the same coercibility as for table column values.)

Bit values assigned to user variables is treated as binary strings. To assign a bit value as a number to a user variable, use CAST() or +0 :

SET @v1 = b‘1000001‘;SET @v2 = CAST(b‘1000001‘ AS UNSIGNED), @v3 = b‘1000001‘+0;SELECT @v1, @v2, @v3;+------+------+------+| @v1  | @v2  | @v3  |+------+------+------+| A    |   |   |+------+------+------+

If the value of a user variable is selected in a result set, it's returned to the client as a string.

If you refer to a variable which has not been initialized, it has a value of and NULL a type of string.

User variables may used in the most contexts where expressions is permitted. This does isn't currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT s Tatement, or the IGNORE NLINES clause of a LOAD DATA statement.

As a general rule, other than SET in statements, you should never assign a value to a user variable and read the value W Ithin the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT , you might get the results to expect, but this is not guaranteed. In the following statement, you might think that MySQL would evaluate first and then does an @a assignment second:

SELECT @a, @a:[email protected]+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

Another issue with assigning a value to a variable and reading the value within the same non-statement are that the SET D Efault result type of a variable is based on its type at the start of the statement. The following example illustrates this:

SET @a=‘test‘;tbl_name;

For this SELECT statement, MySQL reports to the client, the-column one is a string and converts all accesses of- @a str Ings, even though @a is set to a number for the second row. After SELECT the statement executes, are regarded as a number for the @a Next statement.

To avoid problems with this behavior, either does not assign a value to and read the value of the same variable within a sin GLE statement, or else set the variable to 0 , 0.0 and or to ‘‘ define their type before you use it.

In a SELECT statement, each select expression was evaluated only if sent to the client. This means a HAVING , GROUP BY , or ORDER BY clause, referring to a variable, which is assigned a value in the Select Expressi On list does does work as expected:

tbl_name HAVING b=5;

The reference to of the clause refers to a alias for a expression in the b HAVING select list that uses @aa . This does is expected: @aa contains id the value of the from the previous selected row, not from the current row .

User variables is intended to provide data values. They cannot is used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a Table or database name is expected, or as a reserved word such as select . This is true even if the variable are quoted, as shown in the following example:

mysql>   SELECT C1 from T;   +----+|  C1 |+----+|  0 |+----+| 1 |+----+2 rows in Set (0.00 sec) mysql>   set @col = "C1";   Query OK, 0 rows Affected (0.00 sec) mysql>   SELECT @col from T;   +------+| @col |+------+| C1 |+------+1 row in Set (0.00 sec) mysql>   SELECT ' @col ' from T;   error 1054 (42S22): Unknown column ' @col ' in ' Field List ' mysql> SET @col = "' C1 '"; Query OK, 0 rows Affected (0.00 sec) mysql>   SELECT @col from T;   +------+| @col |+------+| ' C1 ' |+------+1 row in Set (0.00 sec)  

An exception to this principle, user variables cannot be used to provide identifiers is so if you are Constructin G A string for use as a prepared statement to is executed later. In this case, user variables can is used to provide any part of the statement. The following example illustrates how this can is done:

SET @c = "c1";SET @s = CONCAT("SELECT ", @c, " FROM t");PREPARE stmt FROM @s;EXECUTE stmt;+----+| C1 |+----+|  0 |+----+|   DEALLOCATE PREPARE stmt;  Query OK, 0 rows Affected (0.00 sec)

See sections 13.5, "SQL Syntax for Prepared statements", for more information.

A Similar technique can be used on application programs to construct SQL statements using program variables, as shown here Using PHP 5:

<?php  $mysqli = new mysqli ("localhost", "User", "Pass", "Test");  if (Mysqli_connect_errno ()) Die    ("Connection failed:%s\n", Mysqli_connect_error ());  $col = "C1";  $query = "Select $col from T";  $result = $mysqli->query ($query);  while ($row = $result->fetch_assoc ())  {    echo "<p>". $row ["$col"]. "</p>\n";  }  $result->close ();  $mysqli->close ();? >

Assembling an SQL statement in this fashion is sometimes known as "Dynamic SQL".

The difference between MySQL @ variables and variables and how to judge the uniqueness of records