mysql 8结果行集大小写有什么变化?

问题描述

运行时

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.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/说:

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

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结果行集大小写有什么变化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！