mysql服务器变量的管理及其SQL模型浅谈

lb20309

　　一、mysql的服务器变量
　　MySQL服务器维护了两大类变量，一个是服务器变量，另一个是状态变量。其中：
　　服务器变量
　　服务器变量定义MySQL服务器运行时的工作特性
　　1、 mysql服务器变量根据作用域可以分为全局变量和会话变量：
　　全局变量：作用于服务器级别，对当前会话无效，对新建会话生效。
　　查看mysql的全局变量及其值，使用如下命令：
　　#SHOW GLOBAL VARIABLES [LIKE 'STRING'];
　　会话变量：作用于当前会话级别，会话建立时，从全局变量继承各变量；会话变量只对当前会话生效，即及时生效。对其他会话无效。
　　查看mysql的会话变量及其值，使用如下命令：
　　#SHOW [SESSION] VARIABLES [LIKE 'STRING']
　　说明：不管是全局变量还是会话变量的动态调整，当mysql服务器重启时，其变量值都会失效，要想永久生效，需要将其写入到mysql配置文件中的[mysqld]配置段里。
　　2、mysql服务器变量根据生效时间，可以将其分为动态变量和静态变量：
　　动态变量：可以在mysql运行时修改 。
　　静态变量：需要在mysql配置文件中进行修改，且重启后永久生效。
　　3、查看服务器变量及其值
　　查看服务器变量及其值有4种方式，这些方式如下：
　　（a）、mysql>  SELECT @@[global.|session.]VARIABLE_NAME;
　　（b）、mysql> show [global|session] variables [like 'string'];
　　（c）、mysql> select * from information_schema.GLOBAL_VARIABLES where GLOBAL_VARIABLES.VARIABLE_NAME = 'some_variable'；用于查看全局变量。如：
　　mysql> select * from information_schema.GLOBAL_VARIABLES where GLOBAL_VARIABLES.VARIABLE_NAME = 'sql_mode';
　　（d）、mysql> select * from infomation_schema.SESSION_VARIABLES where SESSION_VARIABLES.VARIABLE_NAME = 'some_variable';用于查看会话变量。如：
　　mysql> select * from information_schema.SESSION_VARIABLES where SESSION_VARIABLES.VARIABLE_NAME = 'SQL_MODE';
　　4、设定服务器变量及其值
　　mysql> SET {GLOBALlSESSION}  VARIABLE_NAME='VALUE'
　　mysql状态变量
　　mysql状态变量保存了MySQL服务器运行时的统计数据
　　查看mysql的状态变量信息，使用如下命令：
　　#SHOW [GLOBAL|SESSION]  STATUS [LIKE 'STRING']
　　二、mysql的SQL模型
　　mysql的SQL模型用来定义某些规则，限制写入的数据，并作出相应的处理。
　　常见的SQL模型有如下几种：
　　ANSI：宽松模式，对向mysql数据库写入的数据进行校验，如果写入的数据不符合定义的类型或长度，则会对数据类型进行调整或将写入的数据截断并保存，最后报warning警告。
　　TRADITIONAL：传统模式，当向mysql数据写入的数据进行严格校验，如果数据不符合，则该数据不能被写入。并报error错误。如果用于事务时，会进行事务回滚。
　　STRICT_TRANS_TABLES：仅支持事务的表的严格模式。当有数据写入时进行严格校验，向一个支持事务的表中写入不符合的数据时，拒绝数据写入，并报error错误。仅用于支持事务的表。
　　STRICT_ALL_TABLES：对所有表使用严格模式，当有数据写入时进行严格校验，如果写入的数据不符合规则时，则拒绝写入。并返回错误。
　　1、查看默认的sql模型
　　默认mysql 的模型为空，使用如下命令可以查看当前服务器的sql模型。
　　mysql> select @@sql_mode;
　　+------------+
　　| @@sql_mode |
　　+------------+
　　|            |
　　+------------+
　　1 row in set (0.00 sec)
　　2、创建一张stu表
　　mysql> create database testdb;
　　mysql> use testdb;
　　mysql> create table stu(sid tinyint unsigned not null  auto_increment primary key,name char(4) not null,gender enum('F','M') default 'F',age tinyint unsigned not null );
　　Query OK, 0 rows affected (0.03 sec)
　　查看表stu结构
　　mysql> desc stu;
　　+--------+---------------------+------+-----+---------+----------------+
　　| Field  | Type                | Null | Key | Default | Extra          |
　　+--------+---------------------+------+-----+---------+----------------+
　　| sid    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
　　| name   | char(4)             | NO   |     | NULL    |                |
　　| gender | enum('F','M')       | YES  |     | F       |                |
　　| age    | tinyint(3) unsigned | NO   |     | NULL    |                |
　　+--------+---------------------+------+-----+---------+----------------+
　　4 rows in set (0.00 sec)
　　说明：在这个表中，我将name这个字段的长度设置为4个字符长度。
　　向表中插入2行数据
　　mysql> insert into stu (name,gender,age) value ('Linghuchong','F',30),('Huangjiguang','M',21);
　　Query OK, 2 rows affected, 2 warnings (0.01 sec)
　　Records: 2  Duplicates: 0  Warnings: 2
　　注意到了，插入完成之后，有2个warning。在这里我插入数据时，其name字段的长度都超过了4个字符。
　　接着查看数据是否被写入到数据库testdb中。
　　mysql> select * from stu;
　　+-----+------+--------+-----+
　　| sid | name | gender | age |
　　+-----+------+--------+-----+
　　|   1 | Ling | F      |  30 |
　　|   2 | Huan | M      |  21 |
　　+-----+------+--------+-----+
　　2 rows in set (0.00 sec)
　　从结果显示，数据已经写入到了表中，但是name这个字段的数据被截取了，这就是默认的sql模型的限制。当数据的长度超过规定的长度时，数据会被截取并保存。并报warning警告。
　　接下来，将sql模型该为TRADITIONAL，然后观察该模型下对数据的限制情况。
　　3、将mysql的sql模型改为TRADITIONAL。
　　mysql> set session sql_mode='TRADITIONAL';
　　Query OK, 0 rows affected (0.00 sec)
　　查看当前会话的sql模型
　　mysql> show session variables like 'sql_mode';
　　+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
　　| Variable_name | Value                                                                                                                                                |
　　+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
　　| sql_mode      | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
　　+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
　　1 row in set (0.00 sec)
　　然后插入一行数据到stu表中
　　mysql> insert into stu (name,gender,age) values ('Liyundi','F',30);
　　ERROR 1406 (22001): Data too long for column 'name' at row 1
　　看到了吧，当sql模型被修改为TRADITIONAL后，由于name这个字段的长度只有4个字符，因此，当插入的字段长度大于该字段最大长度时，其数据将会拒绝写入到表中，并报error错误。
　　查看错误信息
　　mysql> show errors;
　　+-------+------+------------------------------------------+
　　| Level | Code | Message                                  |
　　+-------+------+------------------------------------------+
　　| Error | 1406 | Data too long for column 'name' at row 1 |
　　+-------+------+------------------------------------------+
　　1 row in set (0.00 sec)