mysql常见问题汇总

港控/mmm° 2023-03-13 05:18 27阅读 0赞

一、混合排序字符集：

1、问题描述：本人在一次创建数据库时，数据库排序规则选成了utf8mb4\_unicode\_ci，在连接查询，比如union查询，select from a表，b表，如果这几个表排序规则不一样，会报错 Illegal mix of collations  for operation '='。

2、原因：通过show variables where Variable\_name like 'collation%';查询数据库排序规则，需要这三个完全一致：

![20200511142053251.png][]

如果不一致就会出现上面的问题；

3、解决方法： 使用alert语句修改了数据库排序规则编码后，再执行show variables where Variable\_name like 'collation%';，发现已经一致了，但是查询还会报错，原因是数据库的字符集改过来了，已有的表和字段还是utf8mb4\_unicode\_ci，这时候需要把所有的表及字段改过来。批量修改方法：

SELECT
    CONCAT(
    'ALTER TABLE ',
    TABLE_NAME,
    ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
    )
    FROM
    information_schema.`TABLES`
    WHERE
    TABLE_SCHEMA = '库名';

生成所有需要执行的SQL，再批量执行这些SQL即可。

如：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70][]

二、时区问题：

插入时间参数，常用mysql系统函数NOW()，或在代码中指定Date类型的值。当在代码中使用Date设置时间时，中间涉及mysql数据库和系统的时间转换，这就涉及到时区的问题，如果数据库与系统时区不一致，哪怕new Date()和NOW()的值相等，执行SQL时这个时间参数可能就和系统不一样了。

这种情况，可以在连接数据库的url中指定时区serverTimezone（ utc为国际时间，和北京时间差8小时， GMT表示北京时间东八区）；也可以修改mysql的系统配置：

1、命令修改

\# 查看数据库当前时间  
SELECT NOW();  
\#查看时区  
show variables like '%zone%';  
select @@time\_zone;  
\#修改mysql全局时区为北京时间  
set global time\_zone = '+8:00';  
\#修改当前会话时区  
set time\_zone = '+8:00';  
\#立即生效  
flush privileges;  
2、配置文件修改

编辑配置文件vim /etc/my.cnf，找到\[mysqld\]，然后加入default-time-zone = '+8:00'。重启mysql让时区生效

[20200511142053251.png]: /images/20230312/ffb533b80c514abc84edcb3bd0f20e9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70]: /images/20230312/f8cc89da3c90492696bc211349ca086b.png