更新后MySQL查询返回的列顺序不同

Question

我的一个程序正在使用这个查询语句获取所选表格列的详细信息：

SELECT c.COLUMN_NAME as column_name,
       c.COLUMN_TYPE as column_type,
       c.CHARACTER_MAXIMUM_LENGTH as length,
       c.TABLE_NAME as table_name,
       c.COLUMN_COMMENT as column_comment,
       k.REFERENCED_TABLE_NAME as reference_table,
       k.REFERENCED_COLUMN_NAME as reference_column
FROM information_schema.COLUMNS as c
LEFT JOIN information_schema.KEY_COLUMN_USAGE as k
    ON (k.TABLE_SCHEMA=c.TABLE_SCHEMA
   AND k.TABLE_NAME=c.TABLE_NAME
   AND k.COLUMN_NAME=c.COLUMN_NAME
   AND k.POSITION_IN_UNIQUE_CONSTRAINT IS NOT NULL)
 WHERE c.TABLE_SCHEMA='$db_name'
   AND c.TABLE_NAME='$table_name'

之前的结果是:

array(36) { [0]=> array(7) { ["column_name"]=> string(2) "id" ["column_type"]=> string(7) ...

该表中的第一列是 id，在结果数组中，第一个索引应该是 id。

现在，升级了 PHP（从5.6到7.3）和 MySQL 后，结果按不同的方式排序。

它并没有根据列创建时间的优先级进行排序，而是按照一些未知元素进行排序（id 在数组中是第3个索引）。

这是一个 bug 还是其他的问题? 我怎样才能恢复原来的排序顺序呢？

---

好的，我刚刚发现了一些事情。MySQL 将带有外键的列排在首位，然后是其他列。

但我不想要这样！我怎样才能恢复原来的排序顺序呢？

---

以下是我的查询结果。首先是当前（错误的）输出, 然后是期望的输出。

错误的输出

column_name column_type length  table_name  column_comment  reference_table reference_column


qdqe_group  varchar(15)     15  md_report   name/Mach_Name  mach            Op_ID
owner       varchar(25)     25  md_report                   users           uName
Pile        varchar(12)     12  md_report                   depo            Depo_ID
Co_ID       varchar(3)       3  md_report   name/Co_NameF   contractors     Co_ID
Region_ID   varchar(2)       2  md_report   dependent/Mine_ID   regions     Region_ID
id          int(11)       NULL  md_report                   NULL            NULL
Submit_Date timestamp     NULL  md_report                   NULL            NULL
asd_ID      varchar(2)       2  md_report   input/multiple- NULL            Null
                                            asd-asd_ID,asd_
                                            nameF
wer_Report  date          NULL  md_report                   NULL            NULL
sdf_Qty     int(2)        NULL  md_report                   NULL            NULL
jyu_Time    time          NULL  md_report                   NULL            NULL
yuj_Work    float(6,2)    NULL  md_report                   NULL            NULL
Al2O3       float(4,2)    NULL  md_report                   NULL            NULL
SiO2        float(4,2)    NULL  md_report                   NULL            NULL
nh_Time     time          NULL  md_report                   NULL            NULL
ju_Work     float(6,2)    NULL  md_report                   NULL            NULL
we_Time     time          NULL  md_report                   NULL            NULL
xcv_Work    float(6,2)    NULL  md_report                   NULL            NULL
trh_Time    time          NULL  md_report                   NULL            NULL
ewr_Work    float(6,2)    NULL  md_report                   NULL            NULL
fgh_Time    time          NULL  md_report                   NULL            NULL
ert_Work    float(6,2)    NULL  md_report                   NULL            NULL
dfg_Time    time          NULL  md_report                   NULL            NULL
vf_Work     float(9,2)    NULL  md_report                   NULL            NULL
as_Time     time          NULL  md_report                   NULL            NULL

这是MySQL中它们的真实位置：(出于个人原因，我更改了一些名称)

预期输出

column_name column_type length  table_name  column_comment  reference_table reference_column


id          int(11)       NULL  md_report                   NULL            NULL
Submit_Date timestamp     NULL  md_report                   NULL            NULL
owner       varchar(25)     25  md_report                   users           uName
wer_Report  date          NULL  md_report                   NULL            NULL
Co_ID       varchar(3)       3  md_report   name/Co_NameF   contractors     Co_ID
Region_ID   varchar(2)       2  md_report   dependent/Mine_ID   regions     Region_ID
asd_ID      varchar(2)       2  md_report   input/multiple- NULL            NULL
                                            asd-asd_ID,asd_
                                            nameF
qdqe_group  varchar(15)     15  md_report   name/Mach_Name  mach            Op_ID
sdf_Qty     int(2)        NULL  md_report                   NULL            NULL
jyu_Time    time          NULL  md_report                   NULL            NULL
yuj_Work    float(6,2)    NULL  md_report                   NULL            NULL
Pile        varchar(12)     12  md_report                   depo            Depo_ID
Al2O3       float(4,2)    NULL  md_report                   NULL            NULL
SiO2        float(4,2)    NULL  md_report                   NULL            NULL
nh_Time     time          NULL  md_report                   NULL            NULL
ju_Work     float(6,2)    NULL  md_report                   NULL            NULL
we_Time     time          NULL  md_report                   NULL            NULL
xcv_Work    float(6,2)    NULL  md_report                   NULL            NULL
trh_Time    time          NULL  md_report                   NULL            NULL
ewr_Work    float(6,2)    NULL  md_report                   NULL            NULL
fgh_Time    time          NULL  md_report                   NULL            NULL
ert_Work    float(6,2)    NULL  md_report                   NULL            NULL
dfg_Time    time          NULL  md_report                   NULL            NULL
vf_Work     float(9,2)    NULL  md_report                   NULL            NULL
as_Time     time          NULL  md_report                   NULL            NULL

正如您所看到的，MySQL将所有带有引用的列都排在结果的顶部。

我想按实际位置获取它们以及相关细节。

新结果无论如何都不合逻辑。如果您能够向我解释发生了什么，我将不胜感激。

- Abolfazl74

---

我不清楚你所遇到的问题具体是什么。如果答案没有帮助，请展示你执行select语句的代码，以及至少一个返回错误数据的完整结果行。 - ysth

帮助我们帮助您：展示执行选择的代码，并至少提供一行完整的错误数据结果。 - ysth

完整解释 - Abolfazl74

Answer 1

我不知道为什么你的输出发生了变化，但我认为要得到你想要的输出，你可以使用 ORDER BY 子句按序号位置排序。

ORDER BY c.ORDINAL_POSITION

当我在我的MySQL数据库上运行您的查询时，我将c.*添加到您选择的列列表中。从显示的所有额外列中，ORDINAL_POSITION似乎是您要查找的内容。

因此，您的最终查询可能如下所示：

SELECT c.COLUMN_NAME as column_name,
       c.COLUMN_TYPE as column_type,
       c.CHARACTER_MAXIMUM_LENGTH as length,
       c.TABLE_NAME as table_name,
       c.COLUMN_COMMENT as column_comment,
       k.REFERENCED_TABLE_NAME as reference_table,
       k.REFERENCED_COLUMN_NAME as reference_column
FROM information_schema.COLUMNS as c
LEFT JOIN information_schema.KEY_COLUMN_USAGE as k
    ON (k.TABLE_SCHEMA=c.TABLE_SCHEMA
      AND k.TABLE_NAME=c.TABLE_NAME
      AND k.COLUMN_NAME=c.COLUMN_NAME
      AND k.POSITION_IN_UNIQUE_CONSTRAINT IS NOT NULL)
WHERE c.TABLE_SCHEMA='$db_name'
      AND c.TABLE_NAME='$table_name'
ORDER BY c.ORDINAL_POSITION

- Thomas F

---

ORDINAL_POSITION。这正是我不知道的关于SQL的内容，我一直在寻找。谢谢您。 - Abolfazl74

Answer 2

在SQL表中，没有“默认”排序顺序，至少没有被ANSI标准（或如你在这里看到的，MySQL实现）所要求的。如果您希望按照特定顺序获得结果集，则应添加适当的ORDER BY子句，例如：

SELECT
    c.COLUMN_NAME as column_name,
    c.COLUMN_TYPE as column_type,
    ...
ORDER BY
    c.COLUMN_NAME;

为什么排序方式发生了变化，可能与从数据库中提取记录的方式有关。随着基础数据的更改，MySQL选择访问记录的方式也可能会发生变化。

- Tim Biegeleisen

---

您的 PHP 结果集中列的位置实际上不应该改变。 - Tim Biegeleisen