DELIMITER// drop PROCEDURE if EXISTS test.express; create PROCEDURE test.express() BEGINselect count(1) into @a from test.test_user where userid='user';select @a;IF @a>1 THENselect 'hello world';ELSEselect 'error';END IF; END // CALL test.express();

通過

select count(1) into @a from test.test_user where userid='user';

通過@a變量是否大于1為條件進行判斷。

注意：在這里我們使用了@變量，那么變量有什么區別呢？

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

MySQL?has the concept of?user-defined variables.

They are loosely typed variables that may be initialized somewhere in a session and keep their value until the session ends.

They are prepended with an?@?sign, like this:?@var

You can initialize this variable with a?SET?statement or inside in a query:

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

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

DELIMITER //CREATE PROCEDURE prc_test (var INT) BEGINDECLARE var2 INT;SET var2 = 1;SELECT var2; END; //DELIMITER ;

These variables are not prepended with any prefixes.

The difference between a procedure variable and a session-specific user-defined variable is that procedure variable is reinitialized to?NULL?each time the procedure is called, while the session-specific variable is not:

CREATE PROCEDURE prc_test () BEGINDECLARE var2 INT DEFAULT 1;SET var2 := var2 + 1;SET @var2 := @var2 + 1;SELECT var2, @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 you can see,?var2?(procedure variable) is reinitialized each time the procedure is called, while?@var2?(session-specific variable) is not.

(In addition to user-defined variables, MySQL?also?has some predefined "system variables", which may be "global variables" such as?@@global.port?or "session variables" such as?@@session.sql_mode; these "session variables" are unrelated to session-specific user-defined variables.)

?

附錄：（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 you to pass values from one statement to another.?User-defined variables are session-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client session are automatically freed when that client exits.

User variables are 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 are not case sensitive in MySQL 5.0 and up, but are case sensitive before MySQL 5.0.

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

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

For?SET, either?=?or?:=?can be 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 be?:=?and not?=?because the latter is treated as the comparison operator?=?in non-SET?statements:

mysql> SET @t1=1, @t2=2, @t3:=4; mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3; +------+------+------+--------------------+ | @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 | +------+------+------+--------------------+ | 1 | 2 | 4 | 7 | +------+------+------+--------------------+

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary 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 is converted to a permissible type. For example, a value having 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 string. The coercibility of user variables is implicit as of MySQL 5.0.3. (This is the same coercibility as for table column values.)

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

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

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

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

User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the?LIMIT?clause of a?SELECT?statement, or the?IGNORE?NLINES?clause of a?LOAD DATA?statement.

As a general rule, other than in?SET?statements, you should never assign a value to a user variable and read the value within 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 you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate?@a?first and then do an assignment second:

SELECT @a, @a:=@a+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-SET?statement is that the default result type of a variable is based on its type at the start of the statement. The following example illustrates this:

mysql> SET @a='test'; mysql> SELECT @a,(@a:=20) FROM tbl_name;

For this?SELECT?statement, MySQL reports to the client that column one is a string and converts all accesses of?@ato strings, even though @a is set to a number for the second row. After the?SELECT?statement executes,?@a?is regarded as a number for the next statement.

To avoid problems with this behavior, either do not assign a value to and read the value of the same variable within a single statement, or else set the variable to?0,?0.0, or?''?to define its type before you use it.

In a?SELECT?statement, each select expression is evaluated only when sent to the client. This means that in aHAVING,?GROUP BY, or?ORDER BY?clause, referring to a variable that is assigned a value in the select expression list does?not?work as expected:

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

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

User variables are intended to provide data values. They cannot be 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 is 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 that user variables cannot be used to provide identifiers is that if you are constructing a string for use as a prepared statement to be executed later. In this case, user variables can be used to provide any part of the statement. The following example illustrates how this can be done:

mysql> SET @c = "c1"; Query OK, 0 rows affected (0.00 sec)mysql> SET @s = CONCAT("SELECT ", @c, " FROM t"); Query OK, 0 rows affected (0.00 sec)mysql> PREPARE stmt FROM @s; Query OK, 0 rows affected (0.04 sec) Statement preparedmysql> EXECUTE stmt; +----+ | c1 | +----+ | 0 | +----+ | 1 | +----+ 2 rows in set (0.00 sec)mysql> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.00 sec)

See?Section?13.5, “SQL Syntax for Prepared Statements”, for more information.

A similar technique can be used in 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”.

?

轉載于:https://www.cnblogs.com/davidwang456/p/4106039.html

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的mysql @变量和变量的区别及怎么判断记录唯一性的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。