Andy Niu �����ĵ�

用户变量
1、用户变量，用户变量和数据库连接有关，连接后声明变量，连接断开后，自动消失。
2、用户变量以@开头，select一个没有赋值的用户变量，返回NULL，也就是没有值，注意取值为NULL与取值为0或者""的区别。
    mysql> select @var;
    +------+
    | @var |
    +------+
    | NULL |
    +------+
3、用户变量赋值有三种方式：
    a、set @var=(select user from user limit 0,1);  
    b、select user into @var from user limit 0,1;  
    c、select @var:=user from user limit 0,1;  
    注意：a和bc的区别，比如当前@var取值为123，查询出来的记录为空，执行a，@var取值为NULL，而执行bc，取值还是123 
4、需要注意的是，mysql的变量类似于动态语言，在赋值的时候，确定变量类型。也就是说，int、string都可以赋值给同一个变量。如下：
    mysql> set @a=123;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select @a;
    +------+
    | @a   |
    +------+
    |  123 |
    +------+
    1 row in set (0.00 sec)
    mysql> set @a='abc';
    Query OK, 0 rows affected (0.00 sec)
    mysql> select @a;
    +------+
    | @a   |
    +------+
    | abc  |
    +------+
    1 row in set (0.00 sec)
##############################################################################################################
系统变量
系统变量两个@@，系统变量的作用域分为全局作用域和会话作用域，以autocommit为例，说明如下：
1、新建一个连接，只有全局autocommit，会话autocommit还不存在，这个时候会从全局autocommit拷贝过来。
2、修改全局autocommit，只会对新建立的连接起作用，对已经存在的连接不起作用。
3、修改会话autocommit，只对当前连接起作用。
4、不管是全局autocommit还是会话autocommit，通过客户端命令设置下去，只对当前运行的服务起作用。
    mysql服务重启，还是原来的配置。要想对服务起作用，必须写入到配置文件中。
5、注意：系统变量和连接有关，才有会话作用域。有些系统变量和连接无关，比如event_scheduler ，和连接无关，
    只有全局作用域，没有会话作用域。
6、全局autocommit的设置和获取
    mysql> set @@global.autocommit=1;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select @@global.autocommit;
    +---------------------+
    | @@global.autocommit |
    +---------------------+
    | 1                   |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> set @@global.autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@global.autocommit;
    +---------------------+
    | @@global.autocommit |
    +---------------------+
    | 0                   |
    +---------------------+
    1 row in set (0.00 sec)

7、会话autocommit的设置和获取
    mysql> set @@session.autocommit =1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@session.autocommit;
    +----------------------+
    | @@session.autocommit |
    +----------------------+
    | 1                    |
    +----------------------+
    1 row in set (0.00 sec)
    
    mysql> set @@session.autocommit =0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@session.autocommit;
    +----------------------+
    | @@session.autocommit |
    +----------------------+
    | 0                    |
    +----------------------+
    1 row in set (0.00 sec)
8、注意：不带作用域修饰，默认是指会话作用域。
    （特别注意：有些系统变量，不带作用域，设置不成功，比如tx_isolation，因此最好都要带上作用域）如下：
    mysql> select @@global.autocommit;
    +---------------------+
    | @@global.autocommit |
    +---------------------+
    | 1                   |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> select @@session.autocommit;
    +----------------------+
    | @@session.autocommit |
    +----------------------+
    | 1                    |
    +----------------------+
    1 row in set (0.00 sec)
    
    mysql> set @@autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@autocommit;
    +--------------+
    | @@autocommit |
    +--------------+
    | 0            |
    +--------------+
    1 row in set (0.00 sec)
    
    mysql> select @@global.autocommit;
    +---------------------+
    | @@global.autocommit |
    +---------------------+
    | 1                   |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> select @@session.autocommit;
    +----------------------+
    | @@session.autocommit |
    +----------------------+
    | 0                    |
    +----------------------+
    1 row in set (0.00 sec)
9、注意：set @@global.autocommit=1; 还有一种写法： set global autocommit=1;
    对于session是同样道理。
10、对于系统变量，除了select，还有一种办法：show variables like '%autocom%'; 如下：
    mysql> show global variables like '%autocommit%';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | ON    |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql> show session variables like '%autocommit%';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | OFF   |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql> show variables like '%autocommit%';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | OFF   |
    +---------------+-------+
    1 row in set (0.00 sec)
11、和select类似，可以show global和session，没有指定作用域，就是指session作用域
##############################################################################################################
系统参数 
    系统变量对应于启动时的选项，也就是系统参数。
##############################################################################################################
状态变量 
    sql运行过程中，还有状态变量。

参见

1、分为几个层次来看：
    a、Mysql服务端数据保存的编码，小范围的设置会覆盖大范围的设置。
    b、当前环境的编码，比如VS2005或者ssh客户端的编码
    c、交互的编码，交互的编码又分为两部分，请求和回复。
2、现在考虑交互的编码，如下：
    a、Mysql客户端发送的SQL语句，由character_set_client来指定。
    b、Mysql服务端收到SQL语句，会把编码集从character_set_client转化为character_set_connection，
        然后转化为字段设置的编码，保存起来。
    c、Mysql服务端执行完SQL语句，把数据库中查询的结果，转化为character_set_results发给客户端。
3、特别注意：乱码往往不是转化导致的，而是因为用一种编码去解释另一种编码。编码转化也会存在乱码的情况，比如：
    另一种编码中没有这个字符。
4、Mysql的整个交互过程都是转化，转化过程中有可能出现乱码。只有两个口子是解释：
    a、当前环境有自己的编码，Mysql客户端用character_set_client来解释当前编码，不一致可能会乱码。
    b、结果返回来的编码是character_set_results，当前环境用自己的编码来解释character_set_results，不一致可能会乱码。
5、测试场景1：
    字段保存编码utf8
    set names utf8
    当前环境编码为utf8
    
    插入查询，都正确。
    mysql> insert into t1(v1) values('cn中国');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from t1;
    +----------+
    | v1       |
    +----------+
    | cn中国 |
    +----------+
    1 row in set (0.00 sec)
    
    a、现在把character_set_results修改为gb2312，查询，如下：
        mysql> set character_set_results=gb2312;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> select * from t1;
        +--------+
        | v1     |
        +--------+
        | cn |
        +--------+
        1 row in set (0.00 sec)
        原因是：当前环境用utf8去解释gb2312，当然乱码
    b、当前环境修改为gb2312，查询如下：
        mysql> select * from t1;
        +--------+
        | v1     |
        +--------+
        | cn中国 |
        +--------+
        1 row in set (0.00 sec)
        原因是：服务端把utf8转为gb2312，传给客户端，客户端用gb2312解释gb2312
6、测试场景2：
    字段保存编码utf8
    set names utf8
    当前环境编码为utf8
    设置character_set_client=gb2312; 插入查询，如下：
        mysql> set names utf8;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> set character_set_client=gb2312;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> insert into t1(v1) values('cn中国');
        Query OK, 1 row affected (0.00 sec)
        
        mysql> select * from t1;
        +---------+
        | v1      |
        +---------+
        | cn涓?? |
        +---------+
        1 row in set (0.00 sec)
        原因是：Mysql客户端用gb2312来解释utf8
7、测试场景3：
    字段保存编码utf8
    set names utf8
    当前环境编码为utf8
    设置character_set_connection=gb2312; 插入查询，如下：
        mysql> set names utf8;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> set character_set_connection=gb2312;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> insert into t1(v1) values('cn中国');
        Query OK, 1 row affected (0.00 sec)
        
        mysql> select * from t1;
        +----------+
        | v1       |
        +----------+
        | cn中国 |
        +----------+
        1 row in set (0.00 sec)
        原因是：Mysql客户端用utf8来解释utf8，然后转化为gb2312
8、总结，也就是说，character_set_client要与当前环境的编码保持一致。
    当前环境的编码要与character_set_results保持一致，通过set names utf8一步到位。

参见

insert into t1(v1) values('cn中国'); 
select * from t1;

1、输入gbk，交互latin1，数据库latin1
insert，客户端把gbk的输入当成latin1来解释，传给服务，服务不需要转码，把gbk当成latin1存入数据库，显示乱码。
select，服务不转码，返回给客户端，客户端latin1当成gbk解释，显示正确。
2、输入gbk，交互latin1，数据库gbk 
insert 报错：ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xB9\xFA' for column 'v1' at row 1
原因是：客户端把gbk的输入当成latin1来解释，传给服务，服务转码，把latin1转化为gbk，出错。
3、输入gbk，交互latin1，数据库utf8
和1一样，这是因为utf8英文字符一个字节，多字节字符三个字节。
4、输入gbk，交互gbk，数据库latin1
insert 报错：ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xB9\xFA' for column 'v1' at row 1
原因是：客户端把gbk的输入当成gbk来解释，传给服务，服务把gbk转化为latin1，转化失败。 
5、输入gbk，交互gbk，数据库gbk
insert，客户端把gbk的输入当成gbk来解释，传给服务，服务不需要转码，直接存入数据库，存入数据库正常。
select，服务不转码，返回给客户端，客户端gbk当成gbk解释，显示正确。 
6、输入gbk，交互gbk，数据库utf8
insert，客户端把gbk的输入当成gbk来解释，传给服务，服务把gbk转成utf8，存入数据库，存入数据库正常。
select，服务把utf8转成gbk，返回给客户端，客户端gbk当成gbk解释，显示正确。 
7、输入gbk，交互utf8，数据库latin1
insert 报错：ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xB9\xFA' for column 'v1' at row 1
原因是：客户端把gbk的输入当成utf8来解释，解释错误。
8、输入gbk，交互utf8，数据库gbk
同7
9、输入gbk，交互utf8，数据库utf8
同7

考虑下面的情况，输入gbk，交互gbk，数据库gbk，插入一条记录，查询回来正常。现在把交互改成utf8，会出现什么问题？
mysql> select * from t1;
+--------+
| v1 |
+--------+
| cn中国 |
+--------+
1 row in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+----------+
| v1 |
+----------+
| cn涓浗 |
+----------+
1 row in set (0.00 sec)

这是因为把utf8的中国，当成gbk解释，就是显示涓浗，也就是说，他们对应的Id是一样的。
反过来，也是一样，把gbk的涓浗当成utf8解释，就是中国，如下：
mysql> insert into t1(v1) values('cn涓浗');
Query OK, 1 row affected (0.08 sec)

mysql> select * from t1;
+----------+
| v1 |
+----------+
| cn涓浗 |
| cn涓浗 |
+----------+
2 rows in set (0.00 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+--------+
| v1 |
+--------+
| cn中国 |
| cn中国 |
+--------+
2 rows in set (0.00 sec)

参见