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

柔光的暖阳◎ 2022-12-03 09:27 82阅读 0赞

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是无缝的。 不是这样

![MySQL logo][]

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`模式设置为[默认][made default]模式，这打破了许多此类幼稚的查询。 实际上，请参见[此处][here]以获取更多信息。

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] ( [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/][https_www.sitepoint.com_quick-tip-how-to-permanently-change-sql-mode-in-mysql]

[MySQL logo]: https://upload.wikimedia.org/wikipedia/en/thumb/6/62/MySQL.svg/640px-MySQL.svg.png
[made default]: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
[here]: http://www.tocker.ca/2014/01/24/proposal-to-enable-sql-mode-only-full-group-by-by-default.html
[George Fekete]: https://www.sitepoint.com/author/gfekete/
[https_www.sitepoint.com_quick-tip-how-to-permanently-change-sql-mode-in-mysql]: https://www.sitepoint.com/quick-tip-how-to-permanently-change-sql-mode-in-mysql/