Mac 下 MySQL 1366 错误(插入 emoji 表情)解决. MySQL 清除重装及配置中可能遇到的问题解决记录.

最新推荐文章于 2024-02-21 11:01:20 发布

羽扬_

最新推荐文章于 2024-02-21 11:01:20 发布

阅读量2.1k

点赞数 2

分类专栏： Bebug

用自己的话讲有助于理解. 希望对你有用. 共勉.

本文链接：https://blog.csdn.net/azitl/article/details/103031062

版权

Bebug 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

说明 : 为啥您看这篇记录 : 搜索错误代码, 百度可以搜到 n 多, 乱七八糟, 也没说为啥, 上来就是你执行就 ok, 我就是这么解决. 
可每个人安装方法, 配置文件路径都不同, 凭运气赌和你一样么? 不一样就下一个么.
看了 n 多垃圾文章, 心累.  决定解决问题后记录问题, 并说明对这个问题原因, 为啥这样改记录, 若您没遇到同一错误代码, 或问题, 直接跳过即可. 
希望对您有帮助. 开始吧.

起因 : 一条带 " 👿emoji 表情" 插入数据库, 引起报错.

背景数据库版本 : mysql 5.7

报错内容 :

"Warning Code ：1366 Incorrect string value: '\xE5\x9C\xA8' for column 'content' at row 1"

你试试 MySQL 下运行语句 :

show variables like 'character%';

utf8mb4 格式是可以显示emoji 图标不报错的.

您显示 client / database / server/等几个参数是 utf8 或其他, 而不是 utf8mb4 吧.

先说说原因 :

MySQL的“utf8”实际上不是真正的UTF-8。

“utf8”只支持每个字符最多三个字节，而真正的UTF-8是每个字符最多四个字节。

MySQL一直没有修复这个bug，他们在2010年发布了一个叫作“utf8mb4”的字符集，绕过了这个问题。

当然，他们并没有对新的字符集广而告之(可能是因为这个bug让他们觉得很尴尬)，以致于现在网络上仍然在建议开发者使用“utf8”，但这些建议都是错误的。
**简单概括如下：**

1. **MySQL的“utf8mb4”是真正的“UTF-8”。**
2. **MySQL的“utf8”是一种“专属的编码”，它能够编码的Unicode字符并不多。**

若不是, 教您怎么设置.

1.0 更改设置设 utf8 为 utf8mb4

低版本[ ≤5.5.3 ]的mysql数据库没有 'utf8mb4’这个字符集，

方案一：若您是旧于, 升级mysql数据库则可。

方案二：若您是新于这个版本，可将字符集可更改为’utf8mb4’。

1.1 修改方法 1 :

修改数据库 : ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

修改表 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

1.1.1 先介绍 mysql 常用几个文件夹

因为用户安装方式不同 [ 手动按照包 / brew … ] , 按照目录 / 配置文件位置也不同, 直接百度 google 教程 DuangDuang 复制, 只能说可能有用可能没用. ( 看你和作者按照方式是否相同了. )

介绍常见目录层级, 出现问题可以举一反三.

/usr/local/var/mysq/ 下

存放用户错误日志等信息.

/etc/my.cnf/ 或 /usr/local/etc/

有人的 mysql 配置文件在此. 可以更改默认信息等. 我记得我用 brew 装 MySQL5.7 时这里有个文件, 但是 brew 按照 MySQL 8.0 时没有 ,仅供参考.

1.2配置字符集

找到你的 my.cnf

我的内容, 仅供您参考

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost

[mysqld]

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4
init_connect='SET NAMES utf8mb4'
max_allowed_packet = 64M
bind-address = 127.0.0.1
port = 3306
socket = /tmp/mysql.sock
innodb_file_per_table=1

[mysqld_safe]
timezone = '+0:00'

更改后 : 重启MySQL

查看编码 show variables like ‘%character%’; 若为 utf8mb4, 去插入条数据试试吧.

2.0 给走投无路的您

修改配置报错. 目录找不到文件. 乱七八糟命令都试了, 运气不好没有解决. 那咱重装MySQL 吧. 我已经帮您踩完坑了.

2.1 终极方法 : 重装大法

brew uninstall mysql

之前是 MySQL 5.7, 卸载后安装 MySQL8.0

卸载了还有?

递归删除命令, 卸载干净 MySQL 目录.

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /private/var/db/receipts/*mysql*

重启, 终于 mysql 没有了.

2.2 开始安装 [ brew 安装方法 ]

若您没有 brew , 百度搜索 " brew for Mac "先按照下.

$ brew search mysql


automysqlbackup            mysql-connector-c          mysql@5.5
mysql                      mysql-connector-c++        mysql@5.6
mysql++                    mysql-sandbox              mysql@5.7
mysql-client               mysql-search-replace       mysqltuner
mysql-cluster              mysql-utilities

brew install mysql      - 这个默认安装的是 8.0,  想安装其他版本, 直接 mysql@XX 选对应即可.

注 : 我想使用 mysql8.0 这个版本. ( 5.7 处理 utf8mb4 太坑了! )

We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To start mysql:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
🍺  /usr/local/Cellar/mysql/8.0.17: 284 files, 272.5MB

按照成功, 运行试试 . 报错 ?! 错误码 2002 :

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

服务器没启动 ? 启动呗 : mysql.server start

又提示 :

. ERROR! The server quit without updating PID file (/usr/local/var/mysql/此处马赛克.Matebook.local.pid).

按照这个路径, 找到这个文件打开看看, 2000 多行… 应该是 log 类似东西.

太多了. 备份后删了, 然后重新执行 [ 为了生成最新日志, 数量上, 可以直接看 ]

2019-11-12T06:03:30.6NZ mysqld_safe Logging to '/usr/local/var/mysql/YYDCYY-HUAWEI-Matebook.local.err'.
2019-11-12T06:03:30.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2019-11-12T06:03:31.198469Z 0 [System] [MY-010116] [Server] /usr/local/Cellar/mysql/8.0.17/bin/mysqld (mysqld 8.0.17) starting as process 38859
2019-11-12T06:03:31.202179Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2019-11-12T06:03:31.207841Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2019-11-12T06:03:31.251851Z 1 [ERROR] [MY-012526] [InnoDB] Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.27. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html
2019-11-12T06:03:31.251870Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2019-11-12T06:03:31.252365Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2019-11-12T06:03:31.252565Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-11-12T06:03:31.252689Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-11-12T06:03:31.253372Z 0 [System] [MY-010910] [Server] /usr/local/Cellar/mysql/8.0.17/bin/mysqld: Shutdown complete (mysqld 8.0.17)  Homebrew.
2019-11-12T06:03:31.6NZ mysqld_safe mysqld from pid file /usr/local/var/mysql/YYDCYY-HUAWEI-Matebook.local.pid ended
2019-11-12T06:03:31.6NZ mysqld_safe Logging to '/usr/local/var/mysql/YYDCYY-HUAWEI-Matebook.local.err'.
2019-11-12T06:03:31.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2019-11-12T06:03:31.791138Z 0 [System] [MY-010116] [Server] /usr/local/opt/mysql/bin/mysqld (mysqld 8.0.17) starting as process 39090
2019-11-12T06:03:31.794479Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2019-11-12T06:03:31.799941Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2019-11-12T06:03:31.843283Z 1 [ERROR] [MY-012526] [InnoDB] Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.27. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html
2019-11-12T06:03:31.843301Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2019-11-12T06:03:31.845018Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2019-11-12T06:03:31.845316Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-11-12T06:03:31.845445Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-11-12T06:03:31.846185Z 0 [System] [MY-010910] [Server] /usr/local/opt/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.17)  Homebrew.
2019-11-12T06:03:31.6NZ mysqld_safe mysqld from pid file /usr/local/var/mysql/YYDCYY-HUAWEI-Matebook.local.pid ended
2019-11-12T06:03:42.6NZ mysqld_safe Logging to '/usr/local/var/mysql/YYDCYY-HUAWEI-Matebook.local.err'.
2019-11-12T06:03:42.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2019-11-12T06:03:42.374086Z 0 [System] [MY-010116] [Server] /usr/local/opt/mysql/bin/mysqld (mysqld 8.0.17) starting as process 39326
2019-11-12T06:03:42.377466Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2019-11-12T06:03:42.383288Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2019-11-12T06:03:42.428676Z 1 [ERROR] [MY-012526] [InnoDB] Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.27. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html
2019-11-12T06:03:42.428695Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2019-11-12T06:03:42.430097Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2019-11-12T06:03:42.430392Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-11-12T06:03:42.430540Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-11-12T06:03:42.431270Z 0 [System] [MY-010910] [Server] /usr/local/opt/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.17)  Homebrew.
2019-11-12T06:03:42.6NZ mysqld_safe mysqld from pid file /usr/local/var/mysql/YYDCYY-HUAWEI-Matebook.local.pid ended
2019-11-12T06:03:52.6NZ mysqld_safe Logging to '/usr/local/var/mysql/YYDCYY-HUAWEI-Matebook.local.err'.
2019-11-12T06:03:52.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2019-11-12T06:03:52.952016Z 0 [System] [MY-010116] [Server] /usr/local/opt/mysql/bin/mysqld (mysqld 8.0.17) starting as process 39557
2019-11-12T06:03:52.955536Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2019-11-12T06:03:52.960956Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
201xxxxxxxxxx43 1. ERROR! The server quit without updating PID file 
............ 此处省略 若干行

新日志 50 多行, 找到其中一处错误说 : " 2019-11-12T06:03:31.251851Z 1 [ERROR] [MY-012526] [InnoDB] Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.27. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html "

大概意思是说, 这个日志属于 mysql 5.7, 现在装的是 mysql8.0, 不合适不合适, 参考 XXXXX.com.

寻思原来是删除 MySQL5.7 没删干净. 看看文件夹名叫 mysql, 直接全删了再来呗.

卸了, 再来.

重装一遍, 啥问题都没了.

嗯 , 初始化个密码

2.3 8.0之前的密码设置

https://stackoverflow.com/questions/6474775/setting-the-mysql-root-user-password-on-os-x

8.0更新以后，所有之前的用法都无效了。正确方法是，terminal 下先进入 mysql 交互界面 [若是新装数据库, 直接回车, 默认没密码]

$  mysql -u root -p

mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourpass';

这个 BY ‘yourpass’; 引号中填写你想设置的密码.

去 terminal 登录试试

至此, 数据库配置完成.

运行 :

show variables like 'character%';

看看编码级是不是 utf8mb4. 若不是参考上面内容, 再改一遍, 耐心点伙计. 我看你行.

3.0 参考资料 :

https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4 [ 为啥插入 emoji 表情报错 / utf8 和 utf8 mb4 区别 ]

https://www.jianshu.com/p/8aa41d65d8d8 [ 修改编码命令 ]

https://blog.csdn.net/weixin_42271658/article/details/85604427 [ myql8 初始化密码修改 ]

羽扬_

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录