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_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'
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_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
Here 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_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
As 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_POSITION
When I ran your query on one of my own MySQL databases, I added a c.*
to your list of SELECT
ed 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_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