mysql - QA 迁移到 Maria DB 后的 MYSQL，应用程序查询为列结果集元数据返回不同的情况

Question

正在尝试将一些 mysql 服务器迁移到新 AWS (amazon-linux-2) 实例上的 MariaDB。在创建一个新实例并安装 mariadb 之后，我们已经转储了旧数据库 mysqldump ... --add-drop-database --triggers --routines --events 并使用导入到新系统中 mysql -u root < dump.sql

数据库有一个表定义了大写和小写列的混合，例如COLUMN1,COLUMN2,column3,etc

原始数据库 viamysqld -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 语句中的大小写匹配

标签： mysqlmariadb

Answer 1

不对 SQL 使用编码风格并混合小写、大写或混合大小写标识符始终是一种不好的做法（“很难编写，因此应该很难阅读”）。有几个编码风格指南，例如SQL Style Guide。

除此之外，您还应该考虑升级到更新版本的 MariaDB/MySQL。MariaDB 5.5 将在 3 个月内完成。

但回到你的问题：

列标识符不区分大小写，因此优化器认为您的 SQL 语句可以简化，在较新版本的 MariaDB 中，EXPLAIN EXTENDEDandSHOW WARNINGS命令将为您提供更多信息：

MariaDB [(none)]> select version();
+--------------------------+
| version()                |
+--------------------------+
| 10.5.1-MariaDB-debug-log |
+--------------------------+
MariaDB [(none)]> explain extended 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 | filtered | Extra          |
+------+-------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
|    1 | PRIMARY     | <derived3> | system | NULL          | NULL | NULL    | NULL | 1    |   100.00 |                |
|    3 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+------+-------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

MariaDB [(none)]> show warnings;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Note  | 1003 | /* select#1 */ select 'A' AS `CName` from dual |
+-------+------+------------------------------------------------+ 

如您所见，该语句已优化并转换为select 'A' AS `CName` from dual.

显然，在 MariaDB 5.5 中，优化器已经比 MySQL 优化器更现代，但在最新版本的 MySQL 中，您将得到相同的结果：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.13    |
+-----------+
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> show warnings;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Note  | 1003 | /* select#1 */ select 'A' AS `CName` from dual |
+-------+------+------------------------------------------------+