One of my programs was using this query to get the details of a selected table’s columns:
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_columnFROM information_schema.COLUMNS as cLEFT 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'The result used to be:
array(36) { [0]=> array(7) { [“column_name”]=> string(2) “id” [“column_type”]=> string(7) …
The first column in that table is id, and in the results array, the first index was id (as it’s supposed to be).
Now, however, after updating PHP (from 5.6 to 7.3) and MySQL, the results are sorting differently.
It is not sorting based on column’s creation date priority and is instead sorting based on some unknown element (id is 3rd index in array).
Is this a bug or something? How can I get the original sorting order back?
Okay, I just figured something out. MySQL is sorting the columns with a foreign keys first, and then the rest of the columns.
I don’t want this! How can I get the original sort order back?
Here are my results. First is the current (wrong) output, then the expected output.
Incorrect output
column_name column_type length table_name column_comment reference_table reference_columnqdqe_group varchar(15) 15 md_report name/Mach_Name mach Op_IDowner varchar(25) 25 md_report users uNamePile varchar(12) 12 md_report depo Depo_IDCo_ID varchar(3) 3 md_report name/Co_NameF contractors Co_IDRegion_ID varchar(2) 2 md_report dependent/Mine_ID regions Region_IDid int(11) NULL md_report NULL NULLSubmit_Date timestamp NULL md_report NULL NULLasd_ID varchar(2) 2 md_report input/multiple- NULL Null asd-asd_ID,asd_ nameFwer_Report date NULL md_report NULL NULLsdf_Qty int(2) NULL md_report NULL NULLjyu_Time time NULL md_report NULL NULLyuj_Work float(6,2) NULL md_report NULL NULLAl2O3 float(4,2) NULL md_report NULL NULLSiO2 float(4,2) NULL md_report NULL NULLnh_Time time NULL md_report NULL NULLju_Work float(6,2) NULL md_report NULL NULLwe_Time time NULL md_report NULL NULLxcv_Work float(6,2) NULL md_report NULL NULLtrh_Time time NULL md_report NULL NULLewr_Work float(6,2) NULL md_report NULL NULLfgh_Time time NULL md_report NULL NULLert_Work float(6,2) NULL md_report NULL NULLdfg_Time time NULL md_report NULL NULLvf_Work float(9,2) NULL md_report NULL NULLas_Time time NULL md_report NULL NULLHere it is their real position in MySQL: (I changed some of the names for personal reasons)
Expected output
column_name column_type length table_name column_comment reference_table reference_columnid int(11) NULL md_report NULL NULLSubmit_Date timestamp NULL md_report NULL NULLowner varchar(25) 25 md_report users uNamewer_Report date NULL md_report NULL NULLCo_ID varchar(3) 3 md_report name/Co_NameF contractors Co_IDRegion_ID varchar(2) 2 md_report dependent/Mine_ID regions Region_IDasd_ID varchar(2) 2 md_report input/multiple- NULL NULL asd-asd_ID,asd_ nameFqdqe_group varchar(15) 15 md_report name/Mach_Name mach Op_IDsdf_Qty int(2) NULL md_report NULL NULLjyu_Time time NULL md_report NULL NULLyuj_Work float(6,2) NULL md_report NULL NULLPile varchar(12) 12 md_report depo Depo_IDAl2O3 float(4,2) NULL md_report NULL NULLSiO2 float(4,2) NULL md_report NULL NULLnh_Time time NULL md_report NULL NULLju_Work float(6,2) NULL md_report NULL NULLwe_Time time NULL md_report NULL NULLxcv_Work float(6,2) NULL md_report NULL NULLtrh_Time time NULL md_report NULL NULLewr_Work float(6,2) NULL md_report NULL NULLfgh_Time time NULL md_report NULL NULLert_Work float(6,2) NULL md_report NULL NULLdfg_Time time NULL md_report NULL NULLvf_Work float(9,2) NULL md_report NULL NULLas_Time time NULL md_report NULL NULLAs you can see, MySQL sorted all the columns with references at the top of the results.
I want to get them in their real position with those details.
The new result is not logical anyway. I would really appreciate it if you could explain to me what is happening here.
Advertisement
Answer
I don’t know why your output changed, but I think that to get the output you want, you can use an ORDER BY clause to order by the ordinal position.
ORDER BY c.ORDINAL_POSITIONWhen I ran your query on one of my own MySQL databases, I added a c.* to your list of SELECTed columns. From all the extra columns displayed, the ORDINAL_POSITION seems like it might be what you are looking for.
So your final query might looks something like this:
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_columnFROM information_schema.COLUMNS as cLEFT 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