![从 MYSQL 迁移到 Maria DB 后,查询返回结果集列名称的不同情况](https://linux22.com/image/746621/%E4%BB%8E%20MYSQL%20%E8%BF%81%E7%A7%BB%E5%88%B0%20Maria%20DB%20%E5%90%8E%EF%BC%8C%E6%9F%A5%E8%AF%A2%E8%BF%94%E5%9B%9E%E7%BB%93%E6%9E%9C%E9%9B%86%E5%88%97%E5%90%8D%E7%A7%B0%E7%9A%84%E4%B8%8D%E5%90%8C%E6%83%85%E5%86%B5.png)
尝试将 mysql 服务器迁移到新 AWS (amazon-linux-2) 实例上的 MariaDB。创建新实例并安装 mariadb 后,我们通过转储旧数据库
mysqldump ... --add-drop-database --triggers --routines --events
并使用导入到新系统中
mysql -u ... < dump.sql
数据库有一个由大写和小写字母混合构成的表,例如COLUMN1,COLUMN2,column3,etc
原始数据库mysqld -V
通过mysqld Ver 14.14 Distrib 5.5.62, for Linux (x86_64)
新的数据库是通过 来mysqld -V
安装mysqld Ver 15.1 Distrib 5.5.64-MariaDB, for Linux (x86_64)
的yum install mariadb-server
我已经验证了模式的列与相关表相匹配。
使用 select 语句查询表,要求列为小写。(我已连接本地应用程序并测试对两个数据库执行相同的查询)
例如SELECT column1,column2,column3,etc from TABLE1 where ..
来自原始数据库的查询结果尊重“select”语句中的大小写,但是在新的 mariadb 数据库中,查询结果与表中定义的列大小写匹配。
是否有可以应用的设置,使结果列名尊重“SELECT”语句中的大小写,从而导致两个数据库中相同选择语句返回的结果列名匹配?
MariaDB和Mysql下的测试用例
select cname from (select s.CName from (select 'A' as CNAME) s) t;
在 MariaDB 下,结果集列大小写与中间临时表中的列匹配。
使用源 mysql 数据库进行相同的查询,
结果集列名的大小写与 select 语句中的大小写匹配
答案1
(感谢完整的测试用例。)
不同的优化器:
mysql> explain
-> select cname from (select s.CName from (select 'A' as CNAME) s) t;
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
3 rows in set (0.01 sec)
mysql> select @@version;
+-----------------+
| @@version |
+-----------------+
| 5.6.22-71.0-log |
+-----------------+
相对
mysql> explain
-> select cname from (select s.CName from (select 'A' as CNAME) s) t;
+------+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| 1 | PRIMARY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+------+-------------+------------+--------+---------------+------+---------+------+------+----------------+
2 rows in set (0.00 sec)
mysql> select @@version;
+--------------------------------------+
| @@version |
+--------------------------------------+
| 10.4.1-MariaDB-1:10.4.1+maria~bionic |
+--------------------------------------+
请注意 MariaDB 是如何足够智能地丢弃查询的一层的。
(MySQL/MariaDB 问题最好在 stackoverflow.com 或 dba.stackexchange.com 上处理。)
但 Oracle 确实在某个时候赶上了:
mysql> select cname from (select s.CName from (select 'A' as CNAME) s) t;
+-------+
| CName |
+-------+
| A |
+-------+
1 row in set (0.00 sec)
mysql> explain
-> select cname from (select s.CName from (select 'A' as CNAME) s) t;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | <derived3> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.17 |
+-----------+
1 row in set (0.00 sec)