mysql 允许选择无效日期

mysql allow invalid dates on select

提问by Damien Roche

For some reason, I can't even select invalid dates in my table. How do I force select? I just receive:

出于某种原因，我什至无法在我的表中选择无效日期。如何强制选择？我刚收到：

select * from table
>> Mysql2::Error: Invalid date: 1900-00-00

I'm not trying to insert, just select. Can I set allow invalid dates in select query?

我不是要插入，只是选择。我可以在选择查询中设置允许无效日期吗？

mysql --version
mysql  Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i686) using readline 6.1

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |

回答by John Lee

This is what I do to ignore invalid dates:

这就是我忽略无效日期的方法：

SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

回答by Kirk Powell

• Log into mysql in the command line mysql -u root -p
• Enter your password
• View the current sql-modesusing SELECT @@GLOBAL.sql_mode;
• Copy the current modes (add or delete modes as needed) and paste in next step.
• Set the sql-modesusing SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES';
• This adds ALLOW_INVALID_DATESand removes both NO_ZERO_DATE, NO_ZERO_IN_DATE
• Restart the MySQL server /etc/init.d/mysql start

• 在命令行登录mysql mysql -u root -p
• 输入您的密码
• 查看当前sql-modes使用SELECT @@GLOBAL.sql_mode;
• 复制当前模式（根据需要添加或删除模式）并粘贴到下一步。
• 设置sql-modes使用SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES';
• 这会添加ALLOW_INVALID_DATES和删除两者NO_ZERO_DATE, NO_ZERO_IN_DATE
• 重启 MySQL 服务器 /etc/init.d/mysql start

回答by Kirk Powell

SQL Server Modes [Reset configuration file]

SQL Server 模式 [重置配置文件]

Solution Link
- From the command line which mysqld
- Should get something like /usr/sbin/mysqldwhich is the location of the binary file
- Sort out the path for the configuration files /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
- Your bash response should look like this: Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
- Look at the files in successive order to see where to make changes (if a file does not exist it is not being referenced).
- Look up your current mode. Open a terminal and log into the mysql database mysql -u database_user -p -e "select @@sql_mode"
- Modify the SQL modes you want to change by adding the following code to the configuration file.
[mysqld]
sql-mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES"
- Save the configuration file and restart the mysql service

解决方案链接
- 从命令行which mysqld
- 应该得到类似于/usr/sbin/mysqld二进制文件位置的信息
- 整理出配置文件的路径/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
- 您的 bash 响应应如下所示：Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
- 按顺序查看文件以查看该文件的位置进行更改（如果文件不存在，则不会被引用）。
- 查找您当前的模式。打开终端并登录mysql数据库mysql -u database_user -p -e "select @@sql_mode"
- 通过在配置文件中添加以下代码来修改要更改的SQL模式。 - 保存配置文件并重启mysql服务
[mysqld]
sql-mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES"

回答by Kalyan Halder Raaz

This one solve my problem. I tested in local MySQL 5.7 ubuntu 18.04.

这个解决了我的问题。我在本地 MySQL 5.7 ubuntu 18.04 中进行了测试。

set global sql_mode="NO_ENGINE_SUBSTITUTION";

Before running this query globally I added a cnf file in /etc/mysql/conf.ddirectory. The cnf file name is mysql.cnfand codes

在全局运行此查询之前，我在/etc/mysql/conf.d目录中添加了一个 cnf 文件 。cnf 文件名为mysql.cnf和代码

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Then I restart mysql

然后我重新启动mysql

sudo service mysql restart

Hope this can help someone.

希望这可以帮助某人。