快速提示：如何在MySQL中永久更改SQL模式

I was working on a legacy project recently and needed to import some data from MySQL 5.5. All the queries in the code worked perfectly in MySQL 5.5, so I assumed an upgrade to 5.7 would be seamless. Not so.

我最近在做一个旧项目，需要从MySQL 5.5导入一些数据。 代码中的所有查询都可以在MySQL 5.5上完美运行，因此我假设升级到5.7是无缝的。 不是这样

First I got errors due to DateTime columns being populated with zeros during import, then when running this query:

首先，由于导入期间在DateTime列中填充了零，因此出现了错误，然后在运行此查询时出现了错误：

select * from ebay_order_items where z_shipmentno is null and ExternalTransactionID is not null and orderstatus = 'Completed' and timestamp > '2015-02-25' group by ExternalTransactionID order by timestamp desc

I got this:

我懂了：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column '1066export.ebay_order_items.TransactionID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

It turned out that the only_full_group_by mode was made default in version 5.7.5., which breaks many of such naïve queries. In fact, see here for more info.

事实证明，在5.7.5版中将only_full_group_by模式设置为默认模式，这打破了许多此类幼稚的查询。 实际上，请参见此处以获取更多信息。

I could rewrite all queries to be 5.7 compatible (hat tip to George Fekete for the solution) and do something as atrocious as this:

我可以将所有查询重写为5.7兼容(向解决方案的乔治·费克特 ( George Fekete )提示)，然后做一些如此糟糕的事情：

select extf.* from ( select ExternalTransactionID from ebay_order_items where ExternalTransactionID is not null group by ExternalTransactionID ) extf JOIN ebay_order_items eoi ON (eoi.ExternalTransactionID = extf.ExternalTransactionID) where eoi.z_shipmentno is null and eoi.orderstatus = 'Completed' and eoi.timestamp > '2015-02-25' order by eoi.timestamp desc

… but this would make the already complex refactoring much more difficult. It would be much better to temporarily disable these checks and force MySQL to act like it did in 5.6.

…但这会使原本就很复杂的重构变得更加困难。 最好暂时禁用这些检查并强制MySQL像5.6中那样进行操作。

永久更改SQL模式 (Permanently changing SQL mode)

First, we find out which configuration file our MySQL installation prefers. For that, we need the binary’s location:

首先，我们找出MySQL安装首选的配置文件。 为此，我们需要二进制文件的位置：

$ which mysqld /usr/sbin/mysqld

Then, we use this path to execute the lookup:

然后，我们使用以下路径执行查找：

$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options" Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

We can see that the first favored configuration file is one in the root of the etc folder. That file, however, did not exist on my system so I opted for the second one.

我们可以看到，第一个首选的配置文件在etc文件夹的根目录中。 但是，该文件在我的系统上不存在，因此我选择了第二个文件。

First, we find out the current sql mode:

首先，我们找出当前的sql模式：

mysql -u homestead -psecret -e "select @@sql_mode" +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@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 | +-------------------------------------------------------------------------------------------------------------------------------------------+

Then, we copy the current string this query produced and remove everything we don’t like. In my case, I needed to get rid of NO_ZERO_IN_DATE, NO_ZERO_DATE and of course ONLY_FULL_GROUP_BY. The newly formed string then looks like this:

然后，我们复制此查询生成的当前字符串，并删除所有我们不喜欢的内容。 就我而言，我需要摆脱NO_ZERO_IN_DATE ， NO_ZERO_DATE ，当然ONLY_FULL_GROUP_BY摆脱ONLY_FULL_GROUP_BY 。 新形成的字符串如下所示：

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

We open the configuration file we decided on before (/etc/mysql/my.cnf) and add the following line into the [mysqld] section:

我们打开之前确定的配置文件( /etc/mysql/my.cnf )，并将以下行添加到[mysqld]部分：

[mysqld] # ... other stuff will probably be here sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Save, exit, and restart MySQL:

保存，退出并重新启动MySQL：

sudo service mysql restart

Voilà, the SQL mode is permanently changed and I can continue developing the legacy project until I need some additional strictness.

Voilà，SQL模式已永久更改，我可以继续开发旧项目，直到需要更多严格性为止。

翻译自: https://www.sitepoint.com/quick-tip-how-to-permanently-change-sql-mode-in-mysql/

相关资源：jdk-8u281-windows-x64.exe