MySQL Version Tokens

MySQL 5.7.8或更高版本的发行版包括版本令牌,该特性支持创建和同步服务器令牌,应用程序可以使用这些令牌来防止访问不正确或过时的数据。

版本令牌接口具有这些特征:
.版本令牌是由用作键或标识符的名称和一个值组成的对
.版本令牌可以被锁定。应用程序可以使用令牌锁向其他协作应用程序表明正在使用令牌,不应该修改它们。
.每个服务器都建立版本令牌列表;例如,指定服务器分配或操作状态。此外,与服务器通信的应用程序可以注册自己的令牌列表,这些令牌表示它需要服务器处于的状态。应用程序发送到不处于所需状态的服务器的SQL语句将产生错误。这是给应用程序的一个信号,它应该寻找另一个处于所需状态的服务器来接收SQL语句。

以下部分描述了版本令牌的组件,讨论了如何安装和使用它,并为其组件提供参考信息。

版本标识组件
名为version_token的服务器端插件持有与服务器关联的版本令牌列表,并订阅语句执行事件的通知。version_token插件使用审计插件API来监视来自客户端的传入语句,并将每个客户端特定于会话的版本令牌列表与服务器版本令牌列表进行匹配。如果存在匹配,插件允许语句通过,服务器继续处理它。否则,插件将向客户端返回一个错误,语句将失败。

一组用户定义函数(udf)提供了一个sql级别的API,用于操作和检查插件维护的服务器版本令牌列表。调用任何的令牌udf版本

系统变量允许客户端指定注册所需服务器状态的版本令牌列表。如果客户端发送语句时服务器处于不同的状态,则客户端接收到一个错误

安装或卸载版本令牌

这里介绍如何安装或卸载版本令牌,这些令牌是在包含插件和用户定义函数的插件库文件中实现的。有关安装或卸载插件和udf的一般信息要使服务器可用,插件库文件必须位于MySQL插件目录中(由plugin_dir系统变量命名的目录)。如果需要,在服务器启动时设置plugin_dir的值,告诉服务器插件目录的位置

插件库的基本名是version_token。文件名后缀因平台而异(例如,对于Unix和类Unix系统,.dll为Windows).

要安装版本令牌插件和udf,请使用install plugin并创建函数语句(根据需要调整.so后缀):

mysql> INSTALL PLUGIN version_tokens SONAME 'version_token.so';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    39
Current database: mysql

Query OK, 0 rows affected (0.07 sec)

mysql> CREATE FUNCTION version_tokens_set RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION version_tokens_show RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION version_tokens_edit RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION version_tokens_delete RETURNS STRING SONAME 'version_token.so';
CREATE FUNCTION version_tokens_lock_shared RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION version_tokens_lock_shared RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION version_tokens_lock_exclusive RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION version_tokens_unlock RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.03 sec)

您必须安装udf来管理服务器的版本令牌列表,但是您还必须安装插件,因为没有它udf将无法正常工作。

如果在主复制服务器上使用插件和udf,也要将它们安装在所有从服务器上,以避免复制问题

如前所述,一旦安装完成,版本令牌插件和udf将一直保持安装状态,直到卸载为止。要删除它们,使用UNINSTALL插件和DROP FUNCTION语句:

mysql> uninstall plugin version_tokens;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> drop function version_tokens_set;
drop function version_tokens_show;
Query OK, 0 rows affected (0.02 sec)

mysql> drop function version_tokens_show;
drop function version_tokens_edit;
Query OK, 0 rows affected (0.00 sec)

mysql> drop function version_tokens_edit;
Query OK, 0 rows affected (0.00 sec)

mysql> drop function version_tokens_delete;
Query OK, 0 rows affected (0.01 sec)

mysql> drop function version_tokens_lock_shared;
Query OK, 0 rows affected (0.00 sec)

mysql> drop function version_tokens_lock_exclusive;
Query OK, 0 rows affected (0.00 sec)

mysql> drop function version_tokens_unlock;
Query OK, 0 rows affected (0.02 sec)

使用版本标记
版本令牌可能有用的一个场景是,系统访问MySQL服务器集合,但是需要通过监视它们并根据负载变化调整服务器分配来管理它们,以实现负载平衡。这样一个系统包括这些组件
.要管理的MySQL服务器集合
.与服务器通信并将它们组织成高可用性组的管理或管理应用程序。组有不同的用途,每个组中的服务器可能有不同的分配。某个组内的服务器的分配可以随时更改
.访问服务器以检索和更新数据的客户机应用程序,根据分配给它们的用途选择服务器。例如,客户端不应该向只读服务器发送更新

版本令牌允许根据分配对服务器访问进行管理,而不需要客户端重复查询服务器的分配
.管理应用程序执行服务器分配并在每个服务器上建立版本令牌以反映其分配。应用程序缓存此信息以提供对其的中央访问点。如果在某个时候管理应用程序需要更改服务器分配(例如,将其从允许写改为仅允许读),则它将更改服务器的版本令牌列表并更新其缓存。

.为了提高性能,客户端应用程序从管理应用程序获取缓存信息,使它们不必为每个语句检索关于服务器分配的信息。基于它将发出的语句类型(例如,读与写),客户端选择适当的服务器并连接到它

.此外,客户端向服务器发送自己的客户端特定版本的令牌来注册它需要的服务器分配。对于客户端发送到服务器的每个语句,服务器将自己的令牌列表与客户端令牌列表进行比较。如果服务器令牌列表包含客户端令牌列表中所有具有相同值的令牌,则存在匹配,服务器执行该语句

另一方面,可能管理应用程序更改了服务器分配及其版本令牌列表。在这种情况下,新的服务器分配现在可能与客户端需求不兼容。服务器和客户端令牌列表之间的令牌不匹配,服务器返回一个错误作为对语句的应答。这指示客户机从管理应用程序缓存中刷新其版本令牌信息,并选择要与之通信的新服务器。

检测版本令牌错误和选择新服务器的客户端逻辑可以通过不同的方式实现:
.客户端可以自己处理所有版本令牌注册、不匹配检测和连接切换
.这些操作的逻辑可以在管理客户端和MySQL服务器之间连接的连接器中实现。这样的连接器可以处理错配错误检测和语句重新发送本身,也可以将错误传递给应用程序,并将其留给应用程序重新发送语句。

下面的例子以更具体的形式说明了前面的讨论。
当版本令牌在给定服务器上初始化时,服务器的版本令牌列表为空。通过调用用户定义函数(udf)来执行令牌列表维护。调用任何版本令牌udf都需要超级特权,因此具有该特权的管理或管理应用程序需要修改令牌列表。

假设一个管理应用程序与一组服务器通信,客户端查询这些服务器以访问雇员和产品数据库(分别名为emp和prod)。所有服务器都被允许处理数据检索语句,但只有一部分服务器被允许进行数据库更新。为了在特定于数据库的基础上处理这个问题,管理应用程序在每个服务器上建立一个版本令牌列表。在给定服务器的令牌列表中,令牌名称表示数据库名称,读写令牌值取决于数据库是否必须以只读方式使用,或者是否可以进行读写。

客户端应用程序通过设置系统变量来注册它们需要服务器匹配的版本令牌列表。变量设置是在客户端特定的基础上进行的,因此不同的客户端可以注册不同的需求。默认情况下,客户端令牌列表是空的,它匹配任何服务器令牌列表。当客户端将其令牌列表设置为非空值时,匹配可能成功也可能失败,这取决于服务器版本令牌列表。

为了定义服务器的版本令牌列表,管理应用程序调用version_token_set() UDF。(稍后将介绍用于修改和显示令牌列表的udf。)例如,应用程序可能将这些语句发送到三个服务器组成的组
服务器1:

mysql> SELECT version_tokens_set('emp=read;prod=read');
+------------------------------------------+
| version_tokens_set('emp=read;prod=read') |
+------------------------------------------+
| 2 version tokens set.                    |
+------------------------------------------+
1 row in set (0.03 sec)

服务器2:

mysql> SELECT version_tokens_set('emp=write;prod=read');
+-------------------------------------------+
| version_tokens_set('emp=write;prod=read') |
+-------------------------------------------+
| 2 version tokens set.                     |
+-------------------------------------------+
1 row in set (0.00 sec)

服务器3:

mysql> SELECT version_tokens_set('emp=read;prod=write');
+-------------------------------------------+
| version_tokens_set('emp=read;prod=write') |
+-------------------------------------------+
| 2 version tokens set.                     |
+-------------------------------------------+
1 row in set (0.00 sec)

在每种情况下,令牌列表都被指定为以分号分隔的名称=值对列表。产生的令牌列表值导致这些服务器连接:
.任何服务器都会接受对两个数据库中的任意一个进行读取
.只有服务器2接受对emp数据库的更新
.只有服务器3接受对prod数据库的更新

除了为每个服务器分配一个版本令牌列表外,管理应用程序还维护一个反映服务器分配的缓存。

在与服务器通信之前,客户机应用程序与管理应用程序进行联系,并检索关于服务器分配的信息。然后客户端根据这些分配选择服务器。假设客户机希望同时执行对emp数据库的读写操作。根据前面的分配,只有服务器2合格。客户机连接到服务器2,并通过设置
version_tokens_session系统变量在服务器2上注册服务器需求:

mysql> SET @@session.version_tokens_session = 'emp=write';
Query OK, 0 rows affected (0.00 sec)

对于客户机发送到服务器2的后续语句,服务器将自己的版本令牌列表与客户机列表进行比较,以检查它们是否匹配。如果是,则语句正常执行:

mysql> UPDATE emp.employee SET salary = salary * 1.1 WHERE id = 4981;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT last_name, first_name FROM emp.employee WHERE id = 4981;
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Smith | Abe |
+-----------+------------+
1 row in set (0.01 sec)

服务器和客户端版本令牌列表之间的差异可能以两种方式出现:
.version_tokens_session值中的令牌名称在服务器令牌列表中不存在。在这种情况下会发生ER_VTOKEN_PLUGIN_TOKEN_NOT_FOUND错误
.version_tokens_session值中的令牌值与服务器令牌列表中相应令牌的值不同。在这种情况下将出现ER_VTOKEN_PLUGIN_TOKEN_MISMATCH错误

只要服务器2的分配没有改变,客户端就会继续使用它进行读写。但是,假设管理应用程序希望更改服务器分配,以便emp数据库的写操作必须发送到服务器1而不是服务器2。为此,它使用version_tokens_edit()修改两个服务器上的emp令牌值(并更新其服务器分配缓存):
服务器1:

mysql> SELECT version_tokens_edit('emp=write');
+----------------------------------+
| version_tokens_edit('emp=write') |
+----------------------------------+
| 1 version tokens updated.        |
+----------------------------------+
1 row in set (0.00 sec)

服务器2:

mysql> SELECT version_tokens_edit('emp=read');
+---------------------------------+
| version_tokens_edit('emp=read') |
+---------------------------------+
| 1 version tokens updated.       |
+---------------------------------+
1 row in set (0.00 sec)

version_tokens_edit()修改服务器令牌列表中的指定令牌而其它的令牌不会改变。

当客户机下一次向服务器2发送一条语句时,它自己的令牌列表将不再与服务器令牌列表匹配,并出现一个错误

mysql> UPDATE emp.employee SET salary = salary * 1.1 WHERE id = 4982;
ERROR 3136 (42000): Version token mismatch for emp. Correct value read

在这种情况下,客户端应该联系管理应用程序以获得关于服务器分配的更新信息,选择一个新服务器,并将失败的语句发送到新服务器

注意:每个客户端必须与版本令牌进行协作,仅根据它在给定服务器上注册的令牌列表发送语句。例如,如果客户端注册了一个’emp=read’的令牌列表,版本令牌中没有任何内容可以阻止客户端发送emp数据库的更新。客户本身必须避免这样做

对于从客户机接收到的每个语句,服务器隐式地使用锁,如下所示:
.为客户端令牌列表(即version_tokens_session值)中命名的每个令牌获取一个共享锁
.执行服务器和客户端令牌列表之间的比较
.根据比较结果执行语句或产生错误
.释放锁

服务器使用共享锁,以便可以在不阻塞的情况下对多个会话进行比较,同时防止对任何试图在操作服务器令牌列表中具有相同名称的令牌之前获取独占锁的会话的令牌进行更改

前面的例子只使用了版本标记插件库中包含的一些用户定义,但是还有其他的。一组udf允许对服务器的版本令牌列表进行操作和检查。另一组udf允许锁定和解锁版本标记

这些udf允许创建、更改、删除和检查服务器的版本标记列表:
.version_tokens_set()完全替换当前列表并分配一个新列表。参数是一个以分号分隔的名称=值对列表。
.version_tokens_edit()支持对当前列表进行部分修改。它可以添加新的令牌或更改现有令牌的值。参数是一个以分号分隔的名称=值对列表
.version_tokens_delete()从当前列表中删除令牌。参数是一个用分号分隔的令牌名称列表
.version_tokens_show()显示当前令牌列表。不需要任何论证

这些函数中的每一个,如果成功,将返回一个指示操作发生的二进制字符串。下面的示例建立服务器令牌列表,通过添加新令牌对其进行修改,删除一些令牌,并显示生成的令牌列表:

mysql> SELECT version_tokens_set('tok1=a;tok2=b');
+-------------------------------------+
| version_tokens_set('tok1=a;tok2=b') |
+-------------------------------------+
| 2 version tokens set.               |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_edit('tok3=c');
+-------------------------------+
| version_tokens_edit('tok3=c') |
+-------------------------------+
| 1 version tokens updated.     |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_delete('tok2;tok1');
+------------------------------------+
| version_tokens_delete('tok2;tok1') |
+------------------------------------+
| 2 version tokens deleted.          |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_show();
+-----------------------+
| version_tokens_show() |
+-----------------------+
| tok3=c;               |
+-----------------------+
1 row in set (0.00 sec)

如果令牌列表格式不正确,就会出现警告:

mysql> SELECT version_tokens_set('tok1=a; =c');
+----------------------------------+
| version_tokens_set('tok1=a; =c') |
+----------------------------------+
| 1 version tokens set.            |
+----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings \G
*************************** 1. row ***************************
  Level: Warning
   Code: 42000
Message: Invalid version token pair encountered. The list provided is only partially updated.
1 row in set (0.00 sec)

如前所述,版本标记是使用一个以分号分隔的名称=值对列表来定义的。考虑一下version_tokens_set()的调用:

mysql> SELECT version_tokens_set('tok1=b;;; tok2= a = b ; tok1 = 1\'2 3"4');
+---------------------------------------------------------------+
| version_tokens_set('tok1=b;;; tok2= a = b ; tok1 = 1\'2 3"4') |
+---------------------------------------------------------------+
| 3 version tokens set.                                         |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

版本标记对参数的解释如下:
.名称和值周围的空白将被忽略。允许在名称和值中使用空格。(对于version_tokens_delete(),它接受一个没有值的名称列表,名称周围的空白将被忽略。)
.没有报价机制
.令牌的顺序并不重要,除非令牌列表包含给定令牌名称的多个实例,否则最后一个值优先于前面的值

根据这些规则,前面的version_tokens_set()调用会产生一个令牌列表,其中包含两个令牌:tok1的值是1’2 3’4,tok2的值是a = b。

mysql> SELECT version_tokens_show();
+--------------------------+
| version_tokens_show()    |
+--------------------------+
| tok2=a = b;tok1=1'2 3"4; |
+--------------------------+
1 row in set (0.01 sec)

如果令牌列表包含两个令牌,为什么version_tokens_set()返回设置的值3版本令牌?这是因为原来的令牌列表包含两个tok1定义,而第二个定义替换了第一个定义。

版本标记令牌操作udf将这些约束放在令牌名称和值上:
.令牌名称不能包含=或;字符,最大长度为64个字符
.令牌值不能包含;字符。值的长度受到max_allowed_packet系统变量的值的限制
.版本令牌将令牌名称和值视为二进制字符串,因此比较是区分大小写的

版本令牌还包括一组udf,允许对令牌进行锁定和解锁:
.version_tokens_lock_exclusive()获得独占的版本令牌锁。它接受一个或多个锁名和超时值的列表
.version_tokens_lock_shared()获得共享版本令牌锁。它接受一个或多个锁名和超时值的列表
.version_tokens_unlock()释放版本令牌锁(独占和共享)。不需要任何论证

每个锁定函数都返回非零表示成功。否则,将发生错误:

mysql> SELECT version_tokens_lock_shared('lock1', 'lock2', 0);
+-------------------------------------------------+
| version_tokens_lock_shared('lock1', 'lock2', 0) |
+-------------------------------------------------+
| 1 |
+-------------------------------------------------+
mysql> SELECT version_tokens_lock_shared(NULL, 0);
ERROR 3131 (42000): Incorrect locking service lock name '(null)'.

使用版本标记锁定功能的锁定是建议的;申请必须同意合作。

可以锁定不存在的令牌名称。这不会创建令牌。

对于版本令牌锁定函数,令牌名称参数完全按照指定的方式使用。周围的空白不被忽略,并且=和;字符是允许的。这是因为令牌只是像传递
给锁定服务一样传递要锁定的令牌名称。

版本标记引用

版本标记功能
版本令牌插件库包含几个用户定义的函数。一组udf允许对服务器的版本令牌列表进行操作和检查。另一组udf允许锁定和解锁版本标记。调用任何版本令牌UDF都需要超级特权。

下面的udf允许创建、更改、删除和检查服务器的版本令牌列表:
.version_tokens_delete (name_list)
使用name_list参数从服务器的版本令牌列表中删除令牌,并返回指示操作结果的二进制字符串。name_list是要删除的版本令牌名称的分号分隔列表。

mysql> SELECT version_tokens_delete('tok1;tok3');
+------------------------------------+
| version_tokens_delete('tok1;tok3') |
+------------------------------------+
| 2 version tokens deleted.          |
+------------------------------------+
1 row in set (0.00 sec)

从MySQL 5.7.9开始,NULL参数被视为空字符串,这对令牌列表没有影响。
version_tokens_delete()删除其参数中指定的标记(如果它们存在的话)。(删除不存在的令牌不是错误。)要在不知道列表中有哪些令牌的情况下完全清除令牌列表,请将NULL或不包含令牌的字符串传递给version_tokens_set():

mysql> SELECT version_tokens_set(NULL);
+------------------------------+
| version_tokens_set(NULL)     |
+------------------------------+
| Version tokens list cleared. |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_set('');
+------------------------------+
| version_tokens_set('')       |
+------------------------------+
| Version tokens list cleared. |
+------------------------------+
1 row in set (0.00 sec)

.version_tokens_edit (token_list)
使用token_list参数修改服务器的版本令牌列表,并返回指示操作结果的二进制字符串。token_list是一个以分号分隔的名称=值对列表,它指定要定义的每个令牌的名称及其值。如果存在令牌,则使用给定的值更新其值。如果标记不存在,则使用给定的值创建它。如果参数为NULL或字符串不包含令牌,则令牌列表保持不变。

mysql> SELECT version_tokens_set('tok1=value1;tok2=value2');
+-----------------------------------------------+
| version_tokens_set('tok1=value1;tok2=value2') |
+-----------------------------------------------+
| 2 version tokens set.                         |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_edit('tok2=new_value2;tok3=new_value3');
+--------------------------------------------------------+
| version_tokens_edit('tok2=new_value2;tok3=new_value3') |
+--------------------------------------------------------+
| 2 version tokens updated.                              |
+--------------------------------------------------------+
1 row in set (0.00 sec)

.version_tokens_set (token_list)
用token_list参数中定义的令牌替换服务器的版本令牌列表,并返回指示操作结果的二进制字符串。token_list是一个用分号分隔的名称=值对列表,指定要定义的每个令牌的名称及其值。如果参数为空或字符串不包含令牌,则清除令牌列表。

mysql> SELECT version_tokens_set('tok1=value1;tok2=value2');
+-----------------------------------------------+
| version_tokens_set('tok1=value1;tok2=value2') |
+-----------------------------------------------+
| 2 version tokens set.                         |
+-----------------------------------------------+
1 row in set (0.00 sec)

.version_tokens_show()
以二进制字符串的形式返回服务器的版本标记列表,其中包含一个以分号分隔的名称=值对列表。

mysql> SELECT version_tokens_show();
+--------------------------+
| version_tokens_show()    |
+--------------------------+
| tok2=value2;tok1=value1; |
+--------------------------+
1 row in set (0.00 sec)

下面的udf允许锁定和解锁版本标记:
.version_tokens_lock_exclusive (token_name [token_name]…超时)
获取一个或多个版本令牌(按名称指定为字符串)上的独占锁,如果未在给定的超时值内获取锁,则超时并报错

mysql> SELECT version_tokens_lock_exclusive('lock1', 'lock2', 10);
+-----------------------------------------------------+
| version_tokens_lock_exclusive('lock1', 'lock2', 10) |
+-----------------------------------------------------+
| 1 |
+-----------------------------------------------------+

这个函数是在MySQL 5.7.8中添加的,名为vtoken_get_write_locks(),在5.7.9中重命名为version_tokens_lock_exclusive()

.version_tokens_lock_shared (token_name [token_name]…超时)
获取一个或多个版本令牌(按名称指定为字符串)上的共享锁,如果未在给定的超时值内获取锁,则超时并报错

mysql> SELECT version_tokens_lock_shared('lock1', 'lock2', 10);
+--------------------------------------------------+
| version_tokens_lock_shared('lock1', 'lock2', 10) |
+--------------------------------------------------+
| 1 |
+--------------------------------------------------+

这个函数是在MySQL 5.7.8中添加的,名为vtoken_get_read_locks(),在5.7.9中重命名为version_tokens_lock_shared()

.version_tokens_unlock ()
使用version_tokens_lock_exclusive()和version_tokens_lock_shared()释放在当前会话中获取的所有锁。

mysql> SELECT version_tokens_unlock();
+-------------------------+
| version_tokens_unlock() |
+-------------------------+
| 1 |
+-------------------------+

这个函数是在MySQL 5.7.8中添加的,名为vtoken_release_locks(),在5.7.9中重命名为version_tokens_unlock()。

锁定功能共享这些特性:
.对于成功,返回值为非零。否则,将发生错误
.令牌名称是字符串
.与操作服务器令牌列表的udf的参数处理不同,令牌名称参数周围的空白不会被忽略,并且=和;字符是允许的
.可以锁定不存在的令牌名称。这不会创建令牌
.超时值是非负整数,表示在出现错误超时之前等待获取锁所需的时间(以秒为单位)。如果超时为0,则不存在等待,如果不能立即获取锁,则该函数将产生一个错误
.版本令牌锁定功能基于所描述的锁定服务在Section 28.3.1 锁服务中描述。

版本令牌系统变量
版本标记支持以下系统变量。这些变量不可用,除非安装版本令牌插件

系统变量:
.version_tokens_session
命令行格式:–version-tokens-session=value
变量范围:Global,Session
动态:Yes
允许的值:类型为字符串,默认值为NULL
此变量的会话值指定客户端版本令牌列表,并指示客户端会话要求服务器版本令牌列表具有的令牌。

如果version_tokens_session变量为NULL(默认值)或值为空,则任何服务器版本令牌列表都是匹配的。(实际上,空值会禁用匹配需求。)

如果version_tokens_session变量有一个非空值,那么它的值与服务器版本令牌列表之间的任何不匹配都会导致会话发送给服务器的任何语句出错。在这种情况下会发生失配:
.version_tokens_session值中的令牌名称在服务器令牌列表中不存在。在本例中,发生了ER_VTOKEN_PLUGIN_TOKEN_NOT_FOUND错误
.version_tokens_session值中的令牌值与服务器令牌列表中相应令牌的值不同。在本例中,将出现ER_VTOKEN_PLUGIN_TOKEN_MISMATCH错误

服务器版本令牌列表中包含没有在version_tokens_session值中命名的令牌不是不匹配的

假设一个管理应用程序将服务器令牌列表设置为如下所示:

mysql> SELECT version_tokens_set('tok1=a;tok2=b;tok3=c');
+--------------------------------------------+
| version_tokens_set('tok1=a;tok2=b;tok3=c') |
+--------------------------------------------+
| 3 version tokens set. |
+--------------------------------------------+

客户端通过设置其version_tokens_session值来注册它需要服务器匹配的令牌。然后,对于客户端发送的每个后续语句,服务器将根据客户
端version_tokens_session值检查其令牌列表,如果存在不匹配,则产生一个错误:

mysql> SET @@session.version_tokens_session = 'tok1=a;tok2=b';
mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
mysql> SET @@session.version_tokens_session = 'tok1=b';
mysql> SELECT 1;
ERROR 3136 (42000): Version token mismatch for tok1. Correct value a

第一个选择成功是因为客户端令牌tok1和tok2出现在服务器令牌列表中,并且每个令牌在服务器列表中具有相同的值。第二个SELECT失败是因为,虽然tok1出现在服务器令牌列表中,但它的值与客户机指定的值不同

此时,客户端发送的任何语句都将失败,除非服务器令牌列表发生更改,使其再次匹配。假设管理应用程序按如下方式更改服务器令牌列表:

mysql> SELECT version_tokens_edit('tok1=b');
+-------------------------------+
| version_tokens_edit('tok1=b') |
+-------------------------------+
| 1 version tokens updated. |
+-------------------------------+
mysql> SELECT version_tokens_show();
+-----------------------+
| version_tokens_show() |
+-----------------------+
| tok3=c;tok1=b;tok2=b; |
+-----------------------+

现在,客户机version_tokens_session值与服务器令牌列表匹配,客户机可以再次成功执行语句:

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+

这个变量是在MySQL 5.7.8中添加的
.version_tokens_session_number
命令行格式:–version-tokens-session-number=N
变量范围:Global,Session
动态:NO
允许的值:类型为整型,默认值为0
此变量供内部使用。
这个变量是在MySQL 5.7.8中添加的。

MySQL Rewriter Query Rewrite Plugin

从MySQL 5.7.6开始,MySQL服务器支持查询重写插件它可以在服务器执行语句之前可以检查和可能修改接收到的语句。MySQL包含一个名为Rewriter的查询重写插件和安装插件与它相关组件的脚本。这些组件一起工作提供对select的重写能力:
.服务端插件名为Rewriter检查select语句并且可能基于缓存在内存中的重写规则来重写它们。标准select语句和预备语句中的select语句可能经受重写。出现在视图定义中或存储过程中的select语句不会经受重写。

.Rewriter插件使用一个包含rewrite_rules表的query_rewrite数据库。表提供了对规则的永久存储,插件使用它来决赛是否重写语句。通过存储在表中的规则集让用户与插件通信。通过设置表中记录的message列来让用户与插件通信。

.query_rewrite数据库包含一个名为flush_rewrite_rules()的存储过程用来把规则表中的内容加载到插件中。

.用户定义函数load_rewrite_rules()被flush_rewrite_rules()存储过程来调用。

.Rewriter插件显示了系统变量能让插件配置和状态变量来提供运行时操作信息。

下面将描述如何安装与使用Rewriter插件并提供与它相关组件的信息。

安装或卸载Rewriter查询重写插件
为了安装或卸载Rewriter查询重写插件,在MySQL安装目录下的share目录中选择执行合适的脚本:
.install_rewriter.sql: 使用这个脚本来安装Rewriter插件和它相关的组件。
.uninstall_rewriter.sql:使用这个脚本来卸载Rewriter插件和它相关的组件。

运行安装脚本

[mysql@localhost share]$ mysql -uroot -p < install_rewriter.sql
Enter password:

运行安装脚本将会安装与启用插件。为了验证它,连接到数据库执行以下语句:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| mysql |
| performance_schema |
| query_rewrite |
| sys |
+--------------------+
6 rows in set (0.00 sec)

mysql> show global variables like 'rewriter_enabled';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| rewriter_enabled | ON |
+------------------+-------+
1 row in set (0.00 sec)

使用Rewriter查询重写插件
为了启用或禁用Rewriter查询重写插件可以通过启用或禁用rewriter_enabled系统变量来完成。默认情况是当你安装Rewriter查询重写插件时是启用的。为了显式设置初始化Rewriter查询重写插件的状态,可以在服务器启动时设置rewriter_enabled变量。例如为了在选项文件中启用Rewriter插件可以进行以下设置:
[mysqld]
rewriter_enabled=ON

也可以在运行时启用或禁用Rewriter插件:

mysql> set global rewriter_enabled=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'rewriter_enabled';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| rewriter_enabled | OFF |
+------------------+-------+
1 row in set (0.00 sec)

mysql> set global rewriter_enabled=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'rewriter_enabled';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| rewriter_enabled | ON |
+------------------+-------+
1 row in set (0.01 sec)

mysql> desc query_rewrite.rewrite_rules;
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| pattern | varchar(10000) | NO | | NULL | |
| pattern_database | varchar(20) | YES | | NULL | |
| replacement | varchar(10000) | NO | | NULL | |
| enabled | enum('YES','NO') | NO | | YES | |
| message | varchar(1000) | YES | | NULL | |
| pattern_digest | varchar(32) | YES | | NULL | |
| normalized_pattern | varchar(100) | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> select * from query_rewrite.rewrite_rules;
Empty set (0.00 sec)

假设Rewriter插件被启用,它将检查和可能修改由服务器所接收到的每个select语句。插件将基于内存中缓存的重写规则(从query_rewriter数据库的rewrite_rules表中加载的)来决定是否重写语句。

添加重写规则
为了向Rewriter插件添加规则,向rewrite_rules表中添加记录,然后调用flush_rewrite_rules()存储过程来从表中加载规则到插件中。下面的例子来创建一个简单规则来匹配单个文本值的查询语句。

mysql> insert into query_rewrite.rewrite_rules(pattern,replacement) values('select ?','select ?+1');
Query OK, 1 row affected (0.03 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from query_rewrite.rewrite_rules;
+----+----------+------------------+-------------+---------+---------+----------------+--------------------+
| id | pattern | pattern_database | replacement | enabled | message | pattern_digest | normalized_pattern |
+----+----------+------------------+-------------+---------+---------+----------------+--------------------+
| 1 | select ? | NULL | select ?+1 | YES | NULL | NULL | NULL |
+----+----------+------------------+-------------+---------+---------+----------------+--------------------+
1 row in set (0.00 sec)

mysql> select * from query_rewrite.rewrite_rules\G;
*************************** 1. row ***************************
id: 1
pattern: select ?
pattern_database: NULL
replacement: select ?+1
enabled: YES
message: NULL
pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0.00 sec)

规则指定一种模式模板指示那种查询语句会被匹配,并且替换模板指示了如何重写匹配的语句。然而添加规则到rewrite_rules表中不足以造成Rewriter插件使用这个规则。我们必须要调用flush_rewrite_rules()过程来将规则表的内容加载到插件内存缓存中:

mysql> call query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected, 1 warning (0.04 sec)

当插件从规则表中读取每种规则时,它将计算一个标准化(语句摘要)格式的模式和一个摘要哈希值并使用它们来更新normalized_pattern和pattern_digest列:

mysql> select * from query_rewrite.rewrite_rules\G;
*************************** 1. row ***************************
id: 1
pattern: select ?
pattern_database: NULL
replacement: select ?+1
enabled: YES
message: NULL
pattern_digest: 3d4fc22e33e10d7235eced3c75a84c2c
normalized_pattern: select ?
1 row in set (0.00 sec)

模式使用与预备语句相同的语法。使用模式模板,?字符实际上作为参数标记用来匹配数据值。参数标记只能用于应该出现数据值的地方,而不能用于SQL关键字、标识符等.?字符不应该使用引号括起来。

像模式一样,替换可以包含?字符。对于匹配一种模式模板的语句,重写插件将重写它,通过模式中的相关标记所匹配的数据值来替换?字符标记。替换的结果是一种完整的语句字符。重写插件要求服务器解析它并执行重写之后的语句将结果返回。

在添加和加载重写规则后,检查匹配规则模式的语句是否会被重写:

mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.02 sec)

mysql> select 10;
+------+
| 10+1 |
+------+
| 11 |
+------+
1 row in set, 1 warning (0.00 sec)

从上面的执行结果来看,第一个查询语句没有出现重写,但第二个查询被重写了。因为第二个查询语句Rewriter插件重写语句后生成了一个警告信息。为了查看这个警告信息可以使用show warnings:

mysql> show warnings\g
+-------+------+------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------+
| Note | 1105 | Query 'select 10' rewritten to 'select 10+1' by a query rewrite plugin |
+-------+------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

为了启用或禁用现有的规则,可以通过修改enabled列并重新加载规则表到重写插件。

为了禁用规则1

mysql> update query_rewrite.rewrite_rules set enabled='NO' where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> call query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from query_rewrite.rewrite_rules\G;
*************************** 1. row ***************************
id: 1
pattern: select ?
pattern_database: NULL
replacement: select ?+1
enabled: NO
message: NULL
pattern_digest: 3d4fc22e33e10d7235eced3c75a84c2c
normalized_pattern: select ?
1 row in set (0.00 sec)

mysql> select 10;
+----+
| 10 |
+----+
| 10 |
+----+
1 row in set (0.00 sec)

这就可以不同从表中删除重写规则来禁用重写规则。

为了重新启用重写规则1:

mysql> update query_rewrite.rewrite_rules set enabled='YES' where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> call query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected (0.02 sec)

mysql> select * from query_rewrite.rewrite_rules\G;
*************************** 1. row ***************************
id: 1
pattern: select ?
pattern_database: NULL
replacement: select ?+1
enabled: YES
message: NULL
pattern_digest: 3d4fc22e33e10d7235eced3c75a84c2c
normalized_pattern: select ?
1 row in set (0.00 sec)

mysql> select 10;
+------+
| 10+1 |
+------+
| 11 |
+------+
1 row in set, 1 warning (0.00 sec)

rewrite_rules表包含了一个pattern_database列它是Rewriter用来匹配没有使用数据库名限定的表名:
.如果相关的数据库和表名相同,语句中限定表名匹配模式中的限定名。
.只有默认数据库与pattern_database一样并且表名相同时语句中的非限定表名匹配模式中的非限定名

假设表mysql.cs有一个名为id的列并且应用程序从以下形式的查询中选择一个来从表中查询记录,这里第二个查询只能在默认数据库为mysql的情况下被执行:
select * from mysql.cs where id=id_value;
select * from cs where id=id_value;

现在假设id列被重命名为user_id了。这种修改意味着应用程序必须引用user_id而不是id。但是如果旧的应用程序不能进行修改,那么它们将不能工作了。Rewriter重写插件可以解决这个问题。为了匹配和重写那些不管是否有限定名的查询语句,添加以下两个规则并重新加载规则表:

mysql> select * from cs where mysql.id=1;
ERROR 1054 (42S22): Unknown column 'mysql.id' in 'where clause'
mysql> insert into query_rewrite.rewrite_rules(pattern,replacement)
-> values('select * from mysql.cs where id= ?','select * from mysql.cs where user_id= ?');
Query OK, 1 row affected (0.05 sec)

mysql> insert into query_rewrite.rewrite_rules(pattern,replacement,pattern_database)
-> values('select * from cs where id=?','select * from cs where user_id=?','mysql');
Query OK, 1 row affected (0.05 sec)

mysql> call query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> select * from query_rewrite.rewrite_rules\G;
*************************** 1. row ***************************
id: 1
pattern: select ?
pattern_database: NULL
replacement: select ?+1
enabled: YES
message: NULL
pattern_digest: 3d4fc22e33e10d7235eced3c75a84c2c
normalized_pattern: select ?
*************************** 2. row ***************************
id: 2
pattern: select * from mysql.cs where id= ?
pattern_database: NULL
replacement: select * from mysql.cs where user_id= ?
enabled: YES
message: NULL
pattern_digest: 45281da14b71c1357dd053a4fe49dfac
normalized_pattern: select `*` from `mysql`.`cs` where (`id` = ?)
*************************** 3. row ***************************
id: 3
pattern: select * from cs where id=?
pattern_database: mysql
replacement: select * from cs where user_id=?
enabled: YES
message: NULL
pattern_digest: 0da2491bc4c0e1462cc020e4fcfde16b
normalized_pattern: select `*` from `mysql`.`cs` where (`id` = ?)
3 rows in set (0.00 sec)

mysql> select * from mysql.cs where id=1;
+------+------+---------+
| id | name | user_id |
+------+------+---------+
| 1 | jy | 1 |
+------+------+---------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------+
| Note | 1105 | Query 'select * from mysql.cs where id=1' rewritten to 'select * from mysql.cs where user_id= 1' by a query rewrite plugin |
+-------+------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from cs where id=1;
+------+------+---------+
| id | name | user_id |
+------+------+---------+
| 1 | jy | 1 |
+------+------+---------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Note | 1105 | Query 'select * from cs where id=1' rewritten to 'select * from cs where user_id=1' by a query rewrite plugin |
+-------+------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Rewriter插件使用第一个规则匹配有限定名的查询,使用第二个规则匹配没有限定名,但默认数据库必须为mysql才能进行查询重写。

如何进行模式匹配
Rewriter插件使用语句摘要和摘要哈希值来使用重写规则匹配输入语句。max_digest_length系统变量决定了用于计算语句摘要的buffer大小。值越大计算的摘要越能区分更长的语句。值越小使用的内存越小但增加了更长语句使用相同摘要的可能性。


mysql> show global variables like 'max_digest_length';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| max_digest_length | 1024 |
+-------------------+-------+
1 row in set (0.00 sec)

插件匹配语句进行重写的规则如下:
1.计算语句摘要的哈希值并将它与规则摘要哈希值进行比较。这可能会出现误报,但可以作为快速的拒绝测试

2.如果语句摘要哈希值匹配任何一个模式摘要哈希值,则匹配规范化哈希值(语句摘要)将语句的形式转化为匹配规则模式所规范化的形式。

3.如果规范化语句与规则匹配,请比较语句和模式中的文字值。模式中的一个?号值与语句中的任何文字值匹配。如果语句准备了一个SELECT语句,模式中的?也匹配语句中的?。否则,对应的文字必须相同。

如果多个规则匹配一个语句,则不确定插件使用哪个规则来重写该语句。

如果一个模式包含比替换更多的标记,那么插件将丢弃多余的数据值。如果一个模式包含的标记比替换的少,这就是一个错误。当加载规则表时,插件会注意到这一点,它会向规则行的message列写入一条错误消息来传递问题,并将Rewriter_reload_error状态变量设置为ON。

重写预备语句
预备语句是在解析时被重写,而不是在执行时被重写。预备语句与非预备语句的区别在于它们可能包含?字符作为参数标记。为了匹配预备语句中的?字符,重写模式必须在同一个地方包含?字符。假设重写规则具有这种模式
select ?, 3
下面列出了几种预备语句和是否与它匹配的模式
预备语句                                模式是否匹配语句
prepare s as ‘select 3, 3’     Yes
prepare s as ‘select ?, 3’     Yes
prepare s as ‘select 3, ?’     No
prepare s as ‘select ?, ?’     No

Rewriter插件操作信息
Rewriter插件通过几种状态变量来表示它的操作信息:

mysql> show global status like 'Rewriter%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Rewriter_number_loaded_rules | 3 |
| Rewriter_number_reloads | 5 |
| Rewriter_number_rewritten_queries | 5 |
| Rewriter_reload_error | OFF |
+-----------------------------------+-------+
4 rows in set (0.01 sec)

IPv6 for MySQL

MySQL对IPv6的支持包括这些功能:
.MySQL服务器可以接受客户端通过IPv6进行TCP/IP连接。例如下面的命令是在本机通过IPv6来连接MySQL服务器:

[mysql@localhost ~]$ mysql -h ::1 -uusername -ppassword

要使用这个功能必须满足以下两个条件;
.服务器操作系统必须被配置支持IPv6
.MySQL服务器缺省配置必须除了支持IPv4的连接之外还要支持IPv6的连接。为了改变缺省配置可以在启动MySQL服务器时使用合适的–bind-address选项。

.MySQL账号名允许使用IPv6地址能让DBA为通过IPv6连接到服务器的客户端指定权限。例如:

mysql> create user 'v6root'@'::1' identified by "abcd";
mysql> grant select on mysql.* to 'v6root'@'::1';

.IPv6功能支持在字符串和内部格式IPv6地址格式之间进行转换并且检查其是否为有效的IPv6地址。例如
INET6_ATON()和INET6_NTOA()类似于INET_ATON()和INET_NTOA(),但除了能处理IPv4地址之外还能处理IPv6地址。

验证操作系统是否支持IPv6
在MySQL服务器可以接受IPv6的连接之前,服务器所在操作系统主机必须支持IPv6。例如在Linux主机上执行以下命令:

[root@localhost ~]# ping6 ::1
PING ::1(::1) 56 data bytes
64 bytes from ::1: icmp_seq=1 ttl=64 time=0.100 ms
64 bytes from ::1: icmp_seq=2 ttl=64 time=0.056 ms
64 bytes from ::1: icmp_seq=3 ttl=64 time=0.035 ms
64 bytes from ::1: icmp_seq=4 ttl=64 time=0.039 ms
....

为了对操作系统网络接口生成详细的描述,执行ifconfig -a并查看输出中的IPv6地址。

[root@localhost ~]# ifconfig -a
docker0: flags=4163  mtu 1500
        inet 172.17.0.1  netmask 255.255.0.0  broadcast 172.17.255.255
        inet6 fe80::42:15ff:fe1d:e05a  prefixlen 64  scopeid 0x20
        ether 02:42:15:1d:e0:5a  txqueuelen 0  (Ethernet)
        RX packets 15  bytes 3638 (3.5 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 33  bytes 3825 (3.7 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160: flags=4163  mtu 1500
        inet 192.168.1.250  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::250:56ff:fea3:90e6  prefixlen 64  scopeid 0x20
        ether 00:50:56:a3:90:e6  txqueuelen 1000  (Ethernet)
        RX packets 5634313  bytes 364548809 (347.6 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 19054  bytes 2337541 (2.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

如果你的操作系统不支持IPv6,查看操作系统文档查找相关指令来启用IPv6。这可能只需要重新配置现有的网络接口来增加一个IPv6地址。或者需要执行更多的修改,比如使用IPv6选项来重建内核。

配置MySQL服务器允许IPv6连接
MySQL服务器对于TCP/IP连接会监听单个网络套接字。这种套接字被绑定到单个地址,但是可能一个地址映射到多个网络接口。为了指定一个地址,在服务器启动时使用–bind-address=addr选项,addr是IPv4或IPv6地址或主机名。(在MySQL 5.5.3之前不支持IPv6地址)。如果addr是主机名,服务器会解析这个主机名为一个IP地址并绑定到这个地址。

服务器对不同类型地址的处理如下:
.如果地址为*,如果服务器主机支持IPv6那么服务器可以接受来自所有服务器上所有IPv6和IPv4网络接口的TCP/IP连接,或者否则只会接受来自所有服务器上IPv4地址的TCP/IP连接。使用这个地址允许接受来自所有服务器网络接口的IPv4和IPv6连接。这个值是缺省值。

.如果地址为0.0.0.0,服务器只能接受来自所有服务器上IPv4网络接口的TCP/IP连接。

.如果地址为::,服务器接受来自所有服务器上的IPv4和IPv6网络接口的TCP/IP连接,这使用这个地址允许接受来自所有服务器网络接口的IPv4和IPv6连接。

.如果地址为IPv4映射地址,服务器接受来自这个地址的TCP/IP连接,这个地址是IPv4或IPv6格式。例如,如果服务器被绑定到::ffff:127.0.0.1,客户端可以使用–host=127.0.0.1或–host=::ffff:127.0.0.1来进行连接。

.如果地址为常规的IPv4或IPv6地址(比如127.0.0.1或::1),服务器只能接受IPv4或IPv6地址的TCP/IP连接。

如果倾向于绑定服务器到一个特定地址,确保mysql.user授权表包含一个有管理权限的用户可以使用这个地址进行连接。否则你将不能关闭服务器。例如,如果你倾向绑定服务器到*,你可以使用现有的所有账号来连接。但是如果你绑定服务器到::1,它只能接受这个地址的连接。在这种情况下,首先确保’root’@’::1’账号在mysql.user表中存在,所以可以仍然连接到服务器并关闭它。

使用IPv6本地主机地址连接MySQL服务器
下面介绍如何配置MySQL允许客户端通过IPv6(::1本地主机地址)连接到本地服务器。
1.在启动MySQL服务器时使用合适的–bind-address选项来允许服务器接受IPv6连接。例如,在my.cnf选项文件中设置bind-address并重启服务器

[mysqld]
......
bind-address=*
......

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status mysqld
* mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since 二 2020-01-14 16:56:48 CST; 5s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 23257 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 28
   Memory: 168.2M
   CGroup: /system.slice/mysqld.service
           |-23273 /bin/sh /mysqlsoft/mysql/bin/mysqld_safe --datadir=/mysqldata/mysql --pid-file=/mysqldata/mysql/mysqld.pid
           `-23591 /mysqlsoft/mysql/bin/mysqld --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql --plugin-dir=/mysqlsoft/mysql/lib/plugin --user=mysql --log-error=/mysqldata/mysql/mysql.err --pid-file=/mysqldata/mysql/mysqld.pid ...

1月 14 16:56:46 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
1月 14 16:56:48 localhost.localdomain mysqld[23257]: Starting MySQL.. SUCCESS!
1月 14 16:56:48 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.

另外,你也可以绑定服务器到::1,但这会使用服务器更加受限于TCP/IP连接。它只能接受单个IPv6地址的连接并且会拒绝IPv4的连接。

2.作为管理员,连接到服务器创建一个本地用户账号它将使用::1本地IPv6主机地址进行连接

mysql> create user 'ipv6root'@'::1' identified by "abcd";
Query OK, 0 rows affected (0.01 sec)


mysql> grant select on *.* to 'ipv6root'@'::1';
Query OK, 0 rows affected (0.02 sec)

3.调用mysql客户端工具使用新创建的账号连接服务器

[mysql@localhost mysql]$ mysql -h ::1 -uipv6root -pabcd mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | csroot        |
| %         | root          |
| ::1       | ipv6root      |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
5 rows in set (0.00 sec)

4.执行status命令来显示连接信息

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Connection id:          7
Current database:       mysql
Current user:           ipv6root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.26-log Source distribution
Protocol version:       10
Connection:             ::1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gb2312
Conn.  characterset:    gb2312
TCP port:               3306
Uptime:                 11 min 47 sec

Threads: 1  Questions: 263  Slow queries: 0  Opens: 336  Flush tables: 1  Open tables: 329  Queries per second avg: 0.371
--------------

mysql> SELECT CURRENT_USER(), @@bind_address;
+----------------+----------------+
| CURRENT_USER() | @@bind_address |
+----------------+----------------+
| ipv6root@::1   | *              |
+----------------+----------------+
1 row in set (0.00 sec)

使用IPv6非本地主机地址连接MySQL服务器
下面将介绍如何配置MySQL服务器允许远程主机通过IPv6来连接MySQL服务器。这类似于在本地主机使用IPv6连接MySQL服务器一样,但服务器与客户端在不同的主机上,每台主机有不同的IPv6地址。
服务器主机的IPv6地址为2018::1

[root@shard1 network-scripts]# ifconfig -a
ens160: flags=4163  mtu 1500
        inet6 2018::1  prefixlen 64  scopeid 0x0
        ether 00:50:56:a0:51:0c  txqueuelen 1000  (Ethernet)
        RX packets 27089788  bytes 2770300693 (2.5 GiB)
        RX errors 0  dropped 6  overruns 0  frame 0
        TX packets 6720001  bytes 532979878 (508.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

客户端主机的IPv6地址为2018::2

ens160: flags=4163  mtu 1500
        inet6 2018::2  prefixlen 64  scopeid 0x0
        ether 00:50:56:a0:4e:69  txqueuelen 1000  (Ethernet)
        RX packets 25337245  bytes 1913014541 (1.7 GiB)
        RX errors 0  dropped 127  overruns 0  frame 0
        TX packets 1536650  bytes 166036937 (158.3 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

具体操作如下:
1.在启动MySQL服务器时使用合适的–bind-address选项来允许服务器接受IPv6连接。例如,在my.cnf选项文件中设置bind-address并重启服务器

[mysqld]
......
bind-address=*
......

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status mysqld
* mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since 二 2020-01-14 16:56:48 CST; 5s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 23257 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 28
   Memory: 168.2M
   CGroup: /system.slice/mysqld.service
           |-23273 /bin/sh /mysqlsoft/mysql/bin/mysqld_safe --datadir=/mysqldata/mysql --pid-file=/mysqldata/mysql/mysqld.pid
           `-23591 /mysqlsoft/mysql/bin/mysqld --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql --plugin-dir=/mysqlsoft/mysql/lib/plugin --user=mysql --log-error=/mysqldata/mysql/mysql.err --pid-file=/mysqldata/mysql/mysqld.pid ...

1月 14 16:56:46 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
1月 14 16:56:48 localhost.localdomain mysqld[23257]: Starting MySQL.. SUCCESS!
1月 14 16:56:48 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.

另外,可以绑定服务器到2018:db1:0:f01::1,但这会使用服务器更加受限于TCP/IP连接。它只能接受单个IPv6地址的连接并且会拒绝IPv4的连接。

2.在服务器中(2018::1)创建一个账号将从客户端主机(2018::2)上连接服务器

mysql> create user 'csroot'@'%' identified by "123456";
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'csroot'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

3.在客户端主机上(2018::2),调用mysql客户端使用创建的新账号来连接服务器(我这时使用MariaDB作为客户端工具)

-bash-4.2$ mysql -h 2018::1 -ucsroot -p123456 mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [mysql]>

4.执行status命令来显示连接信息

MySQL [mysql]> status
--------------
mysql  Ver 15.1 Distrib 10.0.38-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          6
Current database:       mysql
Current user:           csroot@2018::2
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MySQL
Server version:         5.6.23 MySQL Community Server (GPL)
Protocol version:       10
Connection:             2018::1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    gb2312
Conn.  characterset:    gb2312
TCP port:               3306
Uptime:                 34 days 50 min 24 sec

Threads: 2  Questions: 175  Slow queries: 0  Opens: 87  Flush tables: 1  Open tables: 80  Queries per second avg: 0.000
--------------

MySQL [mysql]> select current_user(),@@bind_address;
+----------------+----------------+
| current_user() | @@bind_address |
+----------------+----------------+
| csroot@%       | *              |
+----------------+----------------+
1 row in set (0.00 sec)

MySQL Strict SQL MODE

严格SQL模式控制MySQL如何处理数据改变语句(insert或update)中的无效或缺失值。一个值可能由于各种原因而无效。例如,它对于列来说有错误的数据类型,或者超过了列的范围。当新记录被插入而对于非NULL且没有显式在定义时指定DEFAULT子句的列没有包含值就会出现缺失值的情况。(对于一个NULL列,如果缺失值就会插入NULL值)严格SQL模式也会影响DDL语句比如create table。

如果严格SQL模式没有生效,MySQL对于无效或者缺失值会插入调整值并生成一个警告。在严格SQL模式中,可以通过使用insert ignore或udpate ignore来产生这种行为。

对于select这样不改变数据的语句,在严格SQL模式中无效值会生成一个警告而不是错误。

严格SQL模式对于试图创建一个键值而超过列的最大键值长度时会产生一个错误。当严格SQL模式没有启用时,会产生一个警告并且截断键值的长度使其满足最大键值长度。

严格SQL模式不影响是否对外键约束执行检查。foreign_key_checks可以被使用。

如果STRICT_ALL_TABLES或STRICT_TRANS_TABLES被启用严格SQL模式就会生效,但这些模式的影响会有不同:
.对于事务表来说,当STRICT_ALL_TABLES或STRICT_TRANS_TABLES被启用后当在数据出现无效或丢失值就会出现错误。语句就会被终止与回滚。

.对于非事务表,如果在插入或更新语句中第一行记录出现坏值这些模式的行为是一样的:语句被终止并且表仍然保持不变。如果语句插入或修改多行记录并且在第二行或之后的行记录中出现坏值,那么结果依赖于严格SQL模式是否被启用。
-对于STRICT_ALL_TABLES,MySQL会返回一个错误并忽略剩余的行记录。然而,因为早些的行记录已经被插入或被更新,会导致部分更新。为了避免这个问题,使用单行语句,就会终止而不会改变表数据。

-对于STRICT_TRANS_TABLES,MySQL会将一个无效的值转换成一个最接近的有效值并插入这个调整值。如果这个值将丢失,MySQL插入这个隐式缺省值。在这种情况下,MySQL生成一个敬告而不是一个错误并继续处理语句。

严格SQL模式对除零,零日期和日期中出现零的处理如下:
.严格SQL模式影响对除零的处理,它包括MOD(N,0):对于数据改变操作(insert,update):
-如果严格SQL模式没有被启用,除零会插入NULL并生成一个警告。
-如果严格SQL模式被启用,除非指定了ignore否则除零操作会生成一个错误。对于insert ignore和update ignore操作,除零操作会插入NULL并生成一个警告。

对于select,除零操作会返回NULL。启用严格SQL模式会导致一个警告。
.严格SQL模式会影响服务器是否允许’0000-00-00’为一个有效日期:
-如果严格SQL模式没有被启用,’0000-00-00’被允许并且插入操作不会产生警告。
-如果严格SQL模式被启用,’0000-00-00’不被允许并且插入操作会产生错误,除非你指定ignore。对于insert ignore或update ignore,’0000-00-00’被允许并且插入操作会产生警告。

.严格SQL模式影响服务器是否允许在日期中的年部分为非零但月和日部分允许为零(比如’2010-00-01’或’2010-01-00′):
-如果严格SQL模式没有被启用,有零的日期被允许并且插入操作不产生警告。
-如果严格SQL模式被启用,有零的日期不被允许并且插入操作产生错误,除非指定ignore。对于insert ignore或update ignore来说,有零的日期将以’0000-00-00’形式被插入并产生一个警告。

IGNORE关键字与严格SQL模式的对比
这里将介绍在语句执行时IGNORE关键字(它降级错误为警告)和严格SQL模式(它升级警告为错误)的对比。描述它们影响那些语句以及应用它们有那些错误。

IGNORE对语句执行的影响
MySQL中的一些语句支持可选的IGNORE关键字。此关键字将导致服务器降级某些类型的错误并生成警告。对于多行语句,IGNORE会导致语句跳到下一行,而不是中止。

例如,如果表t2有一个主键列i,试图在多行记录中插入相同的i值正常来说会产生一个重复键错误:

mysql> insert into t2(i) values(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

当使用IGNORE关键字时,包含重复键值的记录仍然不会被插入,但会使用警告来代替错误:

mysql> insert ignore into t2(i) values(1),(1);
Query OK, 1 row affected, 1 warning (0.15 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
2 rows in set (0.00 sec)

以下语句支持IGNORE关键字:
.create table… select:ignore不能应用到语句的create table或select部分但对于由select语句所提供记录来执行插入语句可以应用。对于唯一键值重复的记录会被丢弃。

.delete:ignore会导致MySQL在处理删除记录时忽略错误。

.insert:使用ignore,对于唯一键值重复的记录会被丢弃。对于重复键值的行记录会导致数据转换为最接近的有效值被插入。

对于分区表当没有匹配指定值的分区被找到时,ignore会导致包含那些不匹配值的记录的插入操作失败。
.load data,load xml:使用ignore,对于唯一键值重复的记录会被丢弃。

.update:使用ignore,对于在唯一键值出现重复键值冲突的记录不会被更新。被更新的记录可能导致数据转换为最接近的有效值被插入。

ignore关键字应用到以下错误:
ER_BAD_NULL_ERROR
ER_DUP_ENTRY
ER_DUP_ENTRY_WITH_KEY_NAME
ER_DUP_KEY
ER_NO_PARTITION_FOR_GIVEN_VALUE
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
ER_NO_REFERENCED_ROW_2
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
ER_ROW_IS_REFERENCED_2
ER_SUBQUERY_NO_1_ROW
ER_VIEW_CHECK_FAILED

严格SQL模式对语句执行的影响
MySQL服务器可以以不同的SQL模式进行操作并且可以应用这些不同模式到不同的客房端,这依赖于sql_mode系统变量。在严格SQL模式中,服务器会将特定的警告升级成错误。

例如,在非严格SQL模式中,向整数类型列插入字符串’abc’的结果是将这个字符串值转换为0并生成一个警告:

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t2(i) values('abc');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'i' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

在严格SQL模式下,无效值会被拒绝并生成错误:

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t2(i) values('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1

在某些条件下,某些值可能超出范围或将无效行插入或从表中删除,严格SQL模式适用于以下语句:
alter table
create table
create table … select
delete(单表和多表)
insert
load data
load xml
select sleep()
update(单表和多表)
在存储程序中,如果程序是在严格模式生效时定义的,则刚才列出的类型的各个语句将以严格SQL模式执行。

严格SQL模式应用于以下错误,代表输入值可能无效或丢失这类错误。如果对于列值使用了错误数据类型或超过了值的
范围那么值就是无效的。如果被插入的新行不包含NOT NULL列值但除了在列定义时显式指定了DEFAULT子句的那么就
是值丢失。
ER_BAD_NULL_ERROR
ER_CUT_VALUE_GROUP_CONCAT
ER_DATA_TOO_LONG
ER_DATETIME_FUNCTION_OVERFLOW
ER_DIVISION_BY_ZERO
ER_INVALID_ARGUMENT_FOR_LOGARITHM
ER_NO_DEFAULT_FOR_FIELD
ER_NO_DEFAULT_FOR_VIEW_FIELD
ER_TOO_LONG_KEY
ER_TRUNCATED_WRONG_VALUE
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ER_WARN_DATA_OUT_OF_RANGE
ER_WARN_NULL_TO_NOTNULL
ER_WARN_TOO_FEW_RECORDS
ER_WRONG_ARGUMENTS
ER_WRONG_VALUE_FOR_TYPE
WARN_DATA_TRUNCATED

MySQL SQL模式

MySQL服务器可以以不同的SQL模式来进行操作,并且依赖于sql_mode系统变量的值对不同的客户端可以应用这些不同的SQL模式。DBA可以设置全局SQL模式来匹配服务器操作要求,并且每种应用程序可以设置它的会话SQL模式来满足它的要求。

SQL模式会影响MySQL支持的SQL语法和数据验证检查。这可以在不同环境中让MySQL与其它数据库一起使用变得更容易。

当使用InnoDB表时,可以考虑使用innodb_strict_mode系统变量,它可以对InnoDB表启用额外的错误检查。

设置SQL模式
在MySQL 5.7中缺省的SQL模式包含:ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,和 NO_ENGINE_SUBSTITUTION。ONLY_FULL_GROUP_BY和STRICT_TRANS_TABLES是在MySQL 5.7.5中加入的。NO_AUTO_CREATE_USER是在MySQL 5.7.7中加入的。ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_IN_DATE和NO_ZERO_DATE是在MySQL 5.7.8中加入的。

为了在服务器启动时设置SQL模式,可以在命令行中使用–sql-mode=”modes”选项或在选项文件比如Unix中的my.cnf或Windows上的my.ini文件中使用sql-mode=”modes”选项。modes是用逗号分的不同模式列表。为了显式的清除SQL模式,可以在命令行中使用–sql-mode=””选项将SQL模式设置为空字符串,或者在选项文件中使用sql-mode=””。

MySQL安装程序可以在安装过程中会配置SQL模式。例如,mysql_install_db将在基本的安装目录中创建一个命名为my.cnf的缺省选项文件。这个文件包含设置SQL模式的记录。

如果SQL模式不同于缺省SQL模式或你所期待的SQL模式,可以检查服务器在启动时所读取的选项文件。

为了在运行时改变SQL模式,可以使用set语句来设置全局或会话级的sql_mode系统变量:
set global sql_mode=’modes’;
set session sql_mode=’modes’;

设置global变量需要有super权限并且影响所有连接的客户端操作。设置session变量只影响当前客户端。每个客户端可以在任何时间改变它会话的sql_mode值来达到改变SQL模式的目的。

为了判断当前全局或session级的sql_mode值,执行以下命令:

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

注意在创建分区表并插入数据之后改变服务器的SQL模式会对表的行为造成重大改变并且可能导致丢失或损坏数据。所以强烈建议在创建用户定义的分区表之后不要修改SQL模式。

当复制分区表时,在主从服务器之间不同的SQL模式也会导致一些问题。所以最好的结果就是在主从服务器上使用相同的SQL模式。

最重要的SQL模式
MySQL中最重要的sql_mode值可能是这些:
.ANSI
这种SQL模式改变语法和行为使其更接近标准SQL。它是一种特定的组合模式列表。

.STRICT_TRANS_TABLES
如果一个值不能以指定的方式插入到一个事务表,终止这个语句。对于非事务表,如果这个值在一个单行记录语句或在多行记录语句第一个出现时终止语句。从MySQL 5.7.5开始缺省的SQL模式包括STRICT_TRANS_TABLES。

.TRADITIONAL
使用MySQL行为像一个传统的SQL数据库系统。简单来说这种模式对于将一个不正确值插入到一个列中时抛出一个错误来代替一个警告。它是特定组合模式中的一种。insert或update一旦发现错误会立即终止。如果您正在使用非事务性存储引擎,这可能不是您想要的,因为在错误之前所做的数据更改可能无法回滚,从而导致“部分完成”的更新。

当提到”strict mode”严格模式时,它意味着是STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的一种或两种都被启用。

完整的SQL模式列表
下面是所有支持的SQL模式列表:
.ALLOW_INVALID_DATES
不对日期执行完全检查。只对月份的范围从1到12和日期的范围从1到31执行检查。这对于Web应用程序非常方便,您可以在三个不同的字段中获得年、月和日,并且希望准确地存储用户插入的内容(不执行日期校验)。这种SQL模式应用于date和datetime列。它不应用于timestamp列,因为它总是请求一个合法的日期值。

这种SQL模式要求月分与每天的取值是合法值,并且范围分别不能超过1到12和1到31。当严格模式被禁用时,一个无效的日期值比如’2014-04-31’将被转换成’0000-00-00’并且生成一个警告。当使用严格模式时,一个无效的日期值会生成一个错误。为了允许这样的日期值,启用ALLOW_INVALID_DATES模式。

.ANSI_QUOTES
将”作为标识引用字符(像`引用字符)而不是字符串引用字符。当使用这种SQL模式时仍然可以使用`作为引用标识符,当ANSI_QUOTES被使用时,不能使用双引号来引用文本字符串,因为它被解析作标识符了。

.ERROR_FOR_DIVISION_BY_ZERO
ERROR_FOR_DIVISION_BY_ZERO模式影响对除以零的处理,这包含MOD(N,0)。对于数据修改操作(insert,update),它的影响也依赖于是否启用了严格SQL模式。
-如果这种模式没有被启用,除以零会插入NULL值并且没有警告。
-如果这种模式被启用,除以零会插入NULL值并生成警告。
-如果这种模式与严格SQL模式被启用,除以堆生成一个错误,除非IGNORE也被指定。对于insert ignore和update ignore,除以零会插入NULL值并生成警告。

对于select语句,除以零会返回NULL值。启用ERROR_FOR_DIVISION_BY_ZERO不管是否启用严格SQL模式会导致生成一个警告。

在MySQL 5.7.4中,ERROR_FOR_DIVISION_BY_ZERO被丢弃,在MySQL 5.7.4到5.7.7中当显式命名时ERROR_FOR_DIVISION_BY_ZERO不会做任何事。代替的是,它的影响被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中,当显式命名时ERROR_FOR_DIVISION_BY_ZERO会有影响并且没有包含在严格SQL模式中,就像MySQL5.7.4之前的版本一样。然而缺省情况下当严格模式启用下应该与它联合使用。如果ERROR_FOR_DIVISION_BY_ZERO被启用而没有启用严格模式或者当启用严格模式而没有启用ERROR_FOR_DIVISION_BY_ZERO时会出现这个警告。

因为ERROR_FOR_DIVISION_BY_ZERO被丢弃,它将在将来的版本中被删除并作为一个单独的模式名并且它的影响被包含在严格SQL模式。

.HIGH_NOT_PRECEDENCE
NOT操作的优先级就像NOT a between b and c会被解析成NOT (a between b and c)一样。在一些旧版本的MySQL中,表达式会被解析成(NOT a) between b and c。旧版本更高优先级行为可以通过启用HIGH_NOT_PRECEDENCE模式来获得。

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

.IGNORE_SPACE
允许在函数名与(符号之间存在空格。这会造成内置函数名被作为保留关键字对待。因此标识符与函数名相同必须有引号。例如,因为有一个count()函数,因此在下面的语句中使用count作为表名就会出错:

mysql> CREATE TABLE count (i INT);
Query OK, 0 rows affected (0.13 sec)

mysql> drop table count cascade;
Query OK, 0 rows affected (0.09 sec)

mysql> SET sql_mode = 'IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count (i INT)' at line 1

mysql> create table `count` (i INT);
Query OK, 0 rows affected (0.16 sec)

IGNORE_SPACE模式应用到内置函数,而不是用户定义的函数或存储过程。它总是允许在用户定义的函数或存储过程名后有空格而不管是否启用了IGNORE_SAPCE模式。

.NO_AUTO_CREATE_USER
除非指定了身份验证信息,否则将阻止GRANT语句自动创建新用户帐户。这个语句必须使用identified by来指定非空密码或使用identified with来使用一种验证插件。

最好使用create user来创建MySQL账号,然后使用Grant语句。NO_AUTO_CREATE_USER已经被丢弃并且缺省的SQL模式包含了NO_AUTO_CREATE_USER模式。将sql_mode修改为NO_AUTO_CREATE_USER模式会生成一个警告,除了指定sql_mode为DEFAULT.NO_AUTO_CREATE_USER将会在将来的版本中被删除,到时它的影响将会一直被启用。

之前,在NO_AUTO_CREATE_USER被丢弃之前,一个不启用它的原因是它是不安全的复制。现在它可以被启用并且使用create user if not exists,drop user if exists和alter user if exists而不是grant语句来执行安全复制管理。这些语句当从服务器相对于主服务器有不同的授权时可以启用安全复制。

.NO_AUTO_VALUE_ON_ZERO
NO_AUTO_VALUE_ON_ZERO影响对AUTO_INCREMENT列的处理。正常来说通过向访列插入NULL或0来为列生成下一个序列值。NO_AUTO_VALUE_ON_ZERO会抑制0的行为因此只有插入NULL时会生成下一个序列值。如果0已经被存储到表的AUTO_INCREMENT列中这个SQL模式可能是有用的。(存储0是不建议的)例如,如果使用mysqldump来dump表并且然后再加载它,当遇到值0时MySQL正常来说会生成一个新的序列值,因此表的内容不同于被dump的内容。在加载dump文件之前启用NO_AUTO_VALUE_ON_ZERO来解决这个问题。mysqldump现在在它的输出中自动包含一个语句来启用NO_AUTO_VALUE_ON_ZERO来避免这个问题。

.NO_BACKSLASH_ESCAPES
禁用在字符串中使用反斜杠字符(\)作为转义字符。启用此模式后,反斜杠将成为与其他字符一样的普通字符。

.NO_DIR_IN_CREATE
创建表时,忽略所有索引目录和数据目录指令。此选项在从复制服务器上非常有用。

.NO_ENGINE_SUBSTITUTION
当一个语句比如create table或alter table指定一个存储引擎已经被禁用或没有被编译时控制缺省存储引擎的自动替换。缺省的SQL模式中包含了NO_ENGINE_SUBSTITUTION。因为存储引擎可以在运行时被附加进来,不可以存储引擎也以相同方式被对待:
当NO_ENGINE_SUBSTITUTION被禁用,对于create table的缺省存储引擎被使用并且如果期待的存储引擎不可用会出现一个警告。对于alter table,会出现一个警告并且表不能被修改。
当NO_ENGINE_SUBSTITUTION被启用时,如果期待的存储引擎不可用会出现一个警告并且表不会被创建或被修改。

.NO_FIELD_OPTIONS
在show create table输出中不打印特定MySQL列选项。这种SQL模式被mysqldump以可移植模式来使用。

.NO_KEY_OPTIONS
在show create table输出中不打印特定MySQL索引选项。这种SQL模式被mysqldump以可移植模式来使用。

.NO_TABLE_OPTIONS
在show create table输出中不打印特定MySQL表选项(比如ENGINE)。这种SQL模式被mysqldump以可移植模式来使用。

.NO_UNSIGNED_SUBTRACTION
两个整数相减,这里一种类型UNSIGNED,缺省情况下生成一个没有符号的结果。如果结果出现负数将会出现错误:

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

如果NO_UNSIGNED_SUBTRACTION模式被启用,结果将是负数:

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (0.00 sec)

如果使用此类操作的结果更新无符号整数列,则将该结果裁剪为该列类型的最大值,如果启用no_unsigned_subtract,则将其裁剪为0。如果严格SQL模式被启用,则会出现错误并且列会保持不变。

当no_unsigned_subtraction被启用时,就算任何操作数据是无符号的相减的结果是有符号的。例如比较表t1中的c2列与表t2中的c2列:

mysql> SET sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.20 sec)


mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c2    | bigint(21) unsigned | NO   |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c2    | bigint(21) | NO   |     | 0       |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

这意味着bigint unsigned在所有上下文中不是100%可以使用。

.NO_ZERO_DATE
NO_ZERO_DATE模式影响服务器是否允许’0000-00-00’作为一种有效的日期。它的影响也依赖于是否启用了严格SQL模式。
-如果模式没有被启用,’0000-00-00’被允许并且插入不会产生警告。
-如果模式被启用,’0000-00-00’被允许并且插入会产生警告。
-如果模式和严格SQL模式被启用,’0000-00-00’不被允许并且插入会出现错误,除非指定IGNORE选项。对于insert ignore和update ignore来说,’0000-00-00’被允许并且插入会产生警告。

在MySQL 5.7.4中,NO_ZERO_DATE被丢弃。在MySQL 5.7.4到5.7.7中,NO_ZERO_DATE当显式指定时不会工作。代替地是它的影响已经被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中,当显式地指定NO_ZERO_DATE模式时它会工作并且它不是严格SQL模式的一部分就像MySQL5.7.4版本之前作用一样。然而,缺省情况下它应该与严格SQL模式联合使用。如果启用NO_ZERO_DATE而没有启用严格SQL模式就是出现警告或者反之亦然。

因为NO_ZERO_DATE已经被丢弃,因此它会在将来的版本中作为单独的模式名被删除并且它的影响会包含在严格SQL模式中。

.NO_ZERO_IN_DATE
NO_ZERO_IN_DATE模式影响服务器是否允许日期中的年部分为非0但月或日部分为0。(这种模式影响日期比如’2010-00-01’或’2010-01-00’,而不是’0000-00-00’。为了控制服务器是否允许’0000-00-00’,使用NO_ZERO_DATE模式)。NO_ZERO_IN_DATE模式的影响也依赖于是否启用了严格SQL模式。
-如果这种模式没有启用,有为0部分折日期被允许并且插入不产生警告。
-如果这种模式被启用,有为0部分的日期值将以’0000-00-00’格式被插入并且生成一个警告。
-如果这种模式与严格SQL模式被启用,有为0部分的日期不被允许并且插入会产生一个错误,除非你指定ignore。对于insert ignore和update ignore来说,有为0部分的日期将以’0000-00-00’格式被插入并且生成一个警告。

在MySQL 5.7.4中,NO_ZERO_IN_DATE被丢弃。在MySQL 5.7.4到5.7.7中,NO_ZERO_DATE当显式指定时不会工作。代替地是它的影响已经被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中,当显式地指定NO_ZERO_IN_DATE模式时它会工作并且它不是严格SQL模式的一部分就像MySQL5.7.4版本之前作用一样。然而,缺省情况下它应该与严格SQL模式联合使用。如果启用NO_ZERO_IN_DATE而没有启用严格SQL模式就是出现警告或者反之亦然。

因为NO_ZERO_IN_DATE已经被丢弃,因此它会在将来的版本中作为单独的模式名被删除并且它的影响会包含在严格SQL模式中。

.ONLY_FULL_GROUP_BY
拒绝那些select list、HAVING condition或ORDER BY list引用非聚合列的查询,这些列既不在GROUP BY子句中命名,也不依赖于GROUP BY列(由GROUP BY列唯一确定)。

从MySQL 5.7.5开始,缺省的SQL模式包含ONLY_FULL_GROUP_BY模式。(在5.7.5之前,MySQL没有检测到功能依赖并且缺省情况下ONLY_FULL_GROUP_BY模式没有启用。

MySQL扩展了标准SQL来允许在having子句中引用select列表中的别名表达式。在MySQL 5.7.5之前,启用ONLY_FULL_GROUP_BY模式会禁用这种扩展,因此要求having子句以非别名表达式来书写。从MySQL5.7.5开始,这种限制被取消了因此having子句可以引用别名而不用管ONLY_FULL_GROUP_BY模式是否启用了。

.PAD_CAHR_TO_FULL_LENGTH
默认情况下,在检索时从CHAR列值中裁剪尾随空格。如果PAD_CHAR_TO_FULL_LENGTH被启用,裁剪不会发生并且在检索CHAR列值时填充到它的完整长度。这种模式不应用于varchar列。检索时为其保留尾随空格。

mysql> CREATE TABLE t1 (c1 CHAR(10));
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO t1 (c1) VALUES('xy');
Query OK, 1 row affected (0.03 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------+-----------------+
| c1   | CHAR_LENGTH(c1) |
+------+-----------------+
| xy   |               2 |
+------+-----------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------------+-----------------+
| c1         | CHAR_LENGTH(c1) |
+------------+-----------------+
| xy         |              10 |
+------------+-----------------+
1 row in set (0.00 sec)

.PIPES_AS_CONCAT
将||作为字符串连接操作符(与concat()一样)而不是作为OR的同义词。

.REAL_AS_FLOAT
将REAL作为FLOAT的同义词。缺省情况下,MySQL将REAL作为DOUBLE的同义词。

.STRICT_ALL_TABLES
对所有的存储引擎启用严格SQL模式。无效的数据会被拒绝。从MySQL 5.7.4到5.7.7,STRICT_ALL_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影响。

.STRICT_TRANS_TABLES
对事务型存储引擎启用严格SQL模式并且在可能的情况下使用非事务型存储引擎。从MySQL 5.7.4到5.7.7,STRICT_TRANS_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影响。

Proudly powered by WordPress | Indrajeet by Sus Hill.