mysql @变量和变量的区别及怎么判断记录唯一性

发布时间：2020-05-22 18:07:22 所属栏目：MySql 来源：互联网

导读：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 @a1 THENselect hello world;ELSEselect error;END IF;END//CALL test.express();通过

DELIMITER
    
   ()    test.test_user  userid

通过

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)

MySQLhas the concept of.

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 aSETstatement or inside in a query:

SELECT @var2 := 2

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

DELIMITER

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

DELIMITER ;

These variables are not prepended with any prefixes.

NULLeach time the procedure is called,while the session-specific variable is not:

var2 var2 : var2 : var2,SET @var2 = 1;

CALL prc_test();

var2 @var2
--- ---
2 2

CALL prc_test();

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,MySQLalsohas some predefined "system variables",which may be "global variables" such as@@global.portor "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）

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 asvar_name,where the variable namevar_nameconsists of alphanumeric characters,”,”,and”. A user variable name can contain other characters if you quote it as a string or identifier (for example,,,or).


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 astatement:

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

For,eitherorcan be used as the assignment operator.

You can also assign a value to a user variable in statements other than. In this case,the assignment operator must beand notbecause the latter is treated as the comparison operatorin non-statements:

 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,orvalue. 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,useor:
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 ofand 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 theclause of astatement,or theNLINESclause of astatement. As a general rule,other than instatements,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: For other statements,such as,you might get the results you expect,but this is not guaranteed. In the following statement,you might think that MySQL will evaluatefirst and then do an assignment second: However,the order of evaluation for expressions involving user variables is undefined. （编辑：安卓应用网）【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!