7 月 012020
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]>