mysql 开启不严谨模式,mysql – 为什么innodb严格模式无法启用？

我试过在/etc/mysql/mysql.conf.d/mysqld.cnf(Ubuntu)中添加这一行

innodb_strict_mode = 1

这是在[mysqld]部分,我在其他示例中看到过这一行.我知道这个配置文件正在启动时读取,因为如果我输入废话我会收到错误.

尽管如此,SHOW VARIABLES将此变量列为OFF,显然我可以在不指定非默认字段的情况下插入行.

为什么不设置此变量？

我试过= on而不是= 1,没有区别.否则,配置将从安装默认值AFAIK,全文here中修改.

解决方法:

您可以从命令行设置此SESSION变量：

mysql> show variables like '%strict%';

+--------------------+-------+

| Variable_name | Value |

+--------------------+-------+

| innodb_strict_mode | OFF |

+--------------------+-------+

1 row in set (0.01 sec)

然后,你这样做：

mysql> set innodb_strict_mode = ON;

Query OK, 0 rows affected (0.00 sec)

然后,检查：

mysql> show variables like '%strict%';

+--------------------+-------+

| Variable_name | Value |

+--------------------+-------+

| innodb_strict_mode | ON |

+--------------------+-------+

然后,在退出并重新连接并重新检查变量时,它再次为OFF – 默认值.如果您不想在注销后重置,请使用SET GLOBAL innodb_strict_mode = ON.但是,在重新启动服务器后,在客户端中设置它不会使更改成为可能.

编辑my.cnf – 我从未见过或触及过mysqld.cnf.

将下面的行放在my.cnf的[mysqld]部分中.

sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

innodb_strict_mode = ON

(我也建议使用sql_mode行 – 否则,例如,某些GROUP BY查询将返回(ahem …)异常结果).

弹回服务器 – 然后重新登录.

mysql> show variables like '%strict%';

+--------------------+-------+

| Variable_name | Value |

+--------------------+-------+

| innodb_strict_mode | ON |

+--------------------+-------+

1 row in set (0.01 sec)

这些都在文档here中.

You can turn innodb_strict_mode ON or OFF on the command line when you

start mysqld, or in the configuration file my.cnf or my.ini. You can

also enable or disable innodb_strict_mode at runtime with the

statement SET [GLOBAL|SESSION] innodb_strict_mode=mode, where mode is

either ON or OFF. Changing the GLOBAL setting requires the SUPER

privilege and affects the operation of all clients that subsequently

connect. Any client can change the SESSION setting for

innodb_strict_mode, and the setting affects only that client.

也许你没有SUPER特权？登录并运行SHOW GRANTS以查找.

标签：mysql

来源： https://codeday.me/bug/20190806/1600632.html