文章管理系统CMS（报错Illegal mix of collations for operation ‘UNION’）

J1yabe

已于 2023-09-09 14:05:33 修改

阅读量305

点赞数 1

文章标签： sql 数据库

于 2023-09-07 20:20:50 首次发布

本文链接：https://blog.csdn.net/qq_51979295/article/details/132745350

版权

文章管理系统CMS（报错Illegal mix of collations for operation ‘UNION’）

┌──(root🍍Shiona)-[/media/jiyabe/晚饭吃什么]
└─# docker exec -it aeb bash                
root@aebed373733d:/# cd /var/www/html
root@aebed373733d:/var/www/html# ls
 admin        footer.php   include     message.php  search.php
 attachment   header.php  index.php  notice.php   show.php
cms.sql      images       list.php   page.php     validate.php
root@aebed373733d:/var/www/html# cd include/
root@aebed373733d:/var/www/html/include# ls
common.function.php  database.inc.php  dp.SyntaxHighlighter  js
config.inc.php       db_mysql.php      fckeditor

查看数据库配置

输入cat /var/www/html/include/database.inc.php 查看数据库配置

root@aebed373733d:/var/www/html/include# cat database.inc.php 
<?php
//���ݿ������ļ�
define ('DB_TYPE','mysql');
define ('DB_HOST','localhost');
define ('DB_USER','root');
define ('DB_PWD','root');
define ('DB_NAME','cms');
define ('DB_CHARSET','utf8');
?>

获取到了当前数据库的库名和登录用户密码

登录mysql

输入 mysql -uroot -p 再输入密码，进入mysql界面

root@aebed373733d:/var/www/html/include# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 67
Server version: 5.5.50-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

输入 SHOW CHARACTER SET LIKE 'utf8'; 可以看到utf8编码的排序规则及其默认选项

mysql> SHOW CHARACTER SET LIKE 'utf8';
+---------+---------------+-------------------+--------+
| Charset | Description   | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci   |      3 |
+---------+---------------+-------------------+--------+
1 row in set (0.00 sec)

可以看到字符集utf8的默认排序规则是utf8_general_ci，information_schema的表中字符类型是utf8也就是说，information_schema库中的表使用了默认排序规则utf8_general_ci

接下来再看看网站的数据库表中的排序规则：

mysql> use cms;
mysql> show tables;
+----------------+
| Tables_in_cms  |
+----------------+
| cms_article    |
| cms_category   |
| cms_file       |
| cms_friendlink |
| cms_message    |
| cms_notice     |
| cms_page       |
| cms_users      |
+----------------+

mysql> SHOW FULL COLUMNS FROM cms_users;
+----------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+-----------+
| Field    | Type        | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment   |
+----------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+-----------+
| userid   | int(11)     | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references | ID        |
| username | varchar(20) |  utf8_unicode_ci  | NO   |     |         |                | select,insert,update,references | 用户名    |
| password | varchar(32) |  utf8_unicode_ci  | NO   |     |         |                | select,insert,update,references | 密码      |
+----------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+-----------+
3 rows in set (0.00 sec)

发现表中的默认排序为utf8_unicode_ci

更改网站数据库排序规则

格式化生成脚本，最后输入脚本生成的语句就行了

mysql> SELECT
CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;")
AS target_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="cms"
AND TABLE_TYPE="BASE TABLE"
#生成脚本
+-----------------------------------------------------------------------------------+
| target_tables                                                                     |
+-----------------------------------------------------------------------------------+
| ALTER TABLE cms_article CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;    |
| ALTER TABLE cms_category CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;   |
| ALTER TABLE cms_file CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;       |
| ALTER TABLE cms_friendlink CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; |
| ALTER TABLE cms_message CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;    |
| ALTER TABLE cms_notice CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;     |
| ALTER TABLE cms_page CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;       |
| ALTER TABLE cms_users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;      |
+-----------------------------------------------------------------------------------+

无脑操作

#先进入容器
mysql -uroot -proot
use cms;

  ALTER TABLE cms_article CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;     
  ALTER TABLE cms_category CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;    
  ALTER TABLE cms_file CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;        
  ALTER TABLE cms_friendlink CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
  ALTER TABLE cms_message CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;     
  ALTER TABLE cms_notice CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;      
  ALTER TABLE cms_page CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;        
  ALTER TABLE cms_users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;