MariaDB SQL Mode对数据库语法的影响

MariaDB SQL Mode对数据库操作语法的影响

https://mariadb.com/kb/en/sql-mode/#setting-sql_mode

自MariaDB 10.2.4版本后的默认SQL模式

STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

当SQL模式中配置NO_AUTO_CREATE_USER启用，执行GRANT授权时只有同时指定验证信息才能成功创建用户账户，否则就需要使用CREATE USER单独创建用户账户。

https://mariadb.com/kb/en/grant/#implicit-account-creation
https://mariadb.com/kb/en/create-user/
https://mariadb.com/kb/en/set-password/

查看当前数据库系统的SQL MODE设置

MariaDB [mysql]> select @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [mysql]> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [mysql]>

错误用法

MariaDB [mysql]> create database example;
Query OK, 1 row affected (0.000 sec)

MariaDB [mysql]> grant all on example.* to test_user@localhost;
ERROR 1133 (28000): Can't find any matching row in the user table
MariaDB [mysql]>

正确用法一

MariaDB [mysql]> grant all on example.* to test_user@localhost identified by 'testpwd';
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]>

正确用法二

MariaDB [mysql]> create user test_user@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> set password for test_user@localhost=password('testpwd');
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> grant all on example.* to test_user@localhost;
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]>