mysql 8结果行集大小写有什么变化? [英] what are the changes in mysql 8 result rowset case?

问题描述

运行时

SELECT maxlen FROM `information_schema`.`CHARACTER_SETS`;

mysql 5.7和mysql 8产生不同的结果:

mysql 5.7 and mysql 8 produce different results:

• 在mysql 5.7上，结果行名称小写，
• 在mysql 8上，结果行名称是大写的.

NB:在CHARACTER_SETS表中，公社名称为MAXLEN(大写).

NB : in the CHARACTER_SETS table, the comumn name is MAXLEN (upper cased).

由于我找不到记录它的资源，所以我的问题是:

Since I can't find a resource documenting it, my question is :

mysql 8结果行集大小写发生了什么变化?

what are the changes in mysql 8 result rowset case ?

推荐答案

MySQL 8.0确实更改了INFORMATION_SCHEMA中某些视图的实现:

MySQL 8.0 did change the implementation of some views in the INFORMATION_SCHEMA:

https://mysqlserverteam.com/mysql-8-0- Improvements-to-information_schema/说:

现在所有数据库表的元数据都存储在事务数据字典表中，这使我们能够将INFORMATION_SCHEMA表设计为数据字典表上的数据库VIEW.这消除了成本，例如在执行过程中为每个INFORMATION_SCHEMA查询创建临时表，以及扫描文件系统目录以查找FRM文件.现在还可以利用MySQL优化器的全部功能，使用数据字典表上的索引来准备更好的查询执行计划.

Now that the metadata of all database tables is stored in transactional data dictionary tables, it enables us to design an INFORMATION_SCHEMA table as a database VIEW over the data dictionary tables. This eliminates costs such as the creation of temporary tables for each INFORMATION_SCHEMA query during execution on-the-fly, and also scanning file-system directories to find FRM files. It is also now possible to utilize the full power of the MySQL optimizer to prepare better query execution plans using indexes on data dictionary tables.

这样做是有充分的理由的，但是我了解到，当您基于列名获取关联数组中的结果时，它会使您的某些查询不满意.

So it's being done for good reasons, but I understand that it has upset some of your queries when you fetch results in associative arrays based on column name.

您可以看到视图的定义以大写形式显式声明了列名称:

You can see the definition of the view declares the column name explicitly in uppercase:

mysql 8.0.14> SHOW CREATE VIEW CHARACTER_SETS\G
*************************** 1. row ***************************
                View: CHARACTER_SETS
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `CHARACTER_SETS` AS 
  select 
    `cs`.`name` AS `CHARACTER_SET_NAME`,
    `col`.`name` AS `DEFAULT_COLLATE_NAME`,
    `cs`.`comment` AS `DESCRIPTION`,
    `cs`.`mb_max_length` AS `MAXLEN` -- delimited column explicitly uppercase
  from (`mysql`.`character_sets` `cs` 
  join `mysql`.`collations` `col` on((`cs`.`default_collation_id` = `col`.`id`)))

character_set_client: utf8
collation_connection: utf8_general_ci

您可以通过以下两种方法解决更改:

You can work around the change in a couple of ways:

查询视图时，可以根据需要声明自己的列别名:

You can declare your own column aliases in the case you want when you query a view:

mysql 8.0.14> SELECT MAXLEN AS `maxlen` 
  FROM `information_schema`.`CHARACTER_SETS` LIMIT 2;
+--------+
| maxlen |
+--------+
|      2 |
|      1 |
+--------+

您可以养成在8.0之前以大写字母查询列的习惯.这是一个显示我的5.7沙箱中的结果的测试:

You could start a habit of querying columns in uppercase prior to 8.0. Here's a test showing results in my 5.7 sandbox:

mysql 5.7.24> SELECT MAXLEN 
  FROM `information_schema`.`CHARACTER_SETS` LIMIT 2;
+--------+
| MAXLEN |
+--------+
|      2 |
|      1 |
+--------+

或者您可以将结果提取到非关联数组中，并按列号而不是名称来引用列.

Or you could fetch results into a non-associative array, and reference columns by column number, instead of by name.

这篇关于mysql 8结果行集大小写有什么变化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！