MAC OS安装Mysql和修改my.cnf文件,增加对日期0000-00-00 00:00:00默认值的支持

搜索:

brew search mysql

安装早期版本,brew link可以理解为简易的配置环境变量的方式

brew install mysql@5.6
brew link mysql@5.6

安装新版本,不需要link

brew install mysql

启动和停止:

mysql.server start
mysql.server stop

看mysql查找my.cnf (windows系统下熟知的my.ini在mac中不用)的顺序是如何

mysqld --help --verbose | more

会得到如下内容:

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf 
The following groups are read: mysqld server mysqld-8.0
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
                        Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.

其中

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf 

就是mysql本体查找配置文件的顺序,一般在/opt/homebrew/etc/就存在当前的配置,并且该目录下还有一个default配置文件.

如果在/etc/中创建my.cnf,则会优先使用这个配置.

WordPress从mysql5.7之前版本导入的数据到mysql8时会出现

Invalid default value for 字段名称 

这样的错误.看错误语句可以发现是0000-00-00 00:00:00这样的时间格式不正确的原因.

虽然在mysql的链接session下临时使用

set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

可以更改配置但是通常我们使用navicat这样的工具做数据迁移时,他可能和query不走一个session,我测试在navica的查询中使用如上命令然后再执行数据传输工具并不可用.

修改my.cnf,增加配置

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

其他的诸如改端口号之类的配置也都在这文件了. 

重启mysql服务

 mysql.server restart