对比多种数据库 information_schema.columns中 ordinal_position
概念描述
测试验证
1、测试pg、磐维2.0、mogdb (5.0.5版本)
1.1 删除列，不会更新ordinal_position
1.2 添加列，不会更新ordinal_position
2、mysql 、中兴
2.1删除列，会更新ordinal_position
2.2增加列，更新ordinal_position
3、ob
3.1删除列，会更新ordinal_position
3.2增加列，会更新ordinal_position
知识总结
概念描述
近期同事在mogdb数据库进行交换分区，遇到ERROR: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION，问题原因是：ORDINAL_POSITION 是 INFORMATION_SCHEMA.COLUMNS 表中的一列, drop column操作，导致列在表中位置排序存在断层不再连续。
测试验证
1、测试pg、磐维2.0、mogdb (5.0.5版本)
1.1 删除列，不会更新ordinal_position
`postgres=# select table_name,column_name,ordinal_position from information_schema.columns where table_name='users1' order by ordinal_position;
table_name | column_name | ordinal_position
------------+-------------+------------------
users1 | id | 1
users1 | name | 2
users1 | birthday | 3
users1 | sex | 4
users1 | tel | 5
users1 | address | 6
users1 | hobby | 7
(7 rows)
postgres=# alter table users1 drop COLUMN sex;
ALTER TABLE
postgres=# select table_name,column_name,ordinal_position from information_schema.columns where table_name='users1' order by ordinal_position;
table_name | column_name | ordinal_position
------------+-------------+------------------
users1 | id | 1
users1 | name | 2
users1 | birthday | 3
users1 | tel | 5
users1 | address | 6
users1 | hobby | 7
(6 rows)
`
1.2 添加列，不会更新ordinal_position
第4列依然没有更新，新加的列在后面增加。
postgres=# alter table users1 add COLUMN sex int;
ALTER TABLE
postgres=# select table_name,column_name,ordinal_position from information_schema.columns where table_name='users1' order by ordinal_position;
table_name | column_name | ordinal_position
------------+-------------+------------------
users1 | id | 1
users1 | name | 2
users1 | birthday | 3 <<<<
users1 | tel | 5 <<<<
users1 | address | 6
users1 | hobby | 7
users1 | sex | 8
(7 rows)
2、mysql 、中兴
2.1删除列，会更新ordinal_position
mysql> select table_name,column_name,ordinal_position from information_schema.columns where table_name='ljccdr202405';
+--------------+------------------+------------------+
| table_name | column_name | ordinal_position |
+--------------+------------------+------------------+
| ljccdr202405 | rec_type | 1 |
| ljccdr202405 | serv_type | 2 |
| ljccdr202405 | content_type | 3 |
| ljccdr202405 | sell_prov | 4 |
| ljccdr202405 | custom_name | 5 |
| ljccdr202405 | contract_id | 6 |
| ljccdr202405 | order_id | 7 |
| ljccdr202405 | product_id | 8 |
| ljccdr202405 | action_id | 9 |
| ljccdr202405 | target_prov | 10 |
| ljccdr202405 | publication_name | 11 |
| ljccdr202405 | split_num | 12 |
| ljccdr202405 | ad_type | 13 |
| ljccdr202405 | fee | 14 |
| ljccdr202405 | telnum | 15 |
| ljccdr202405 | billingcycle | 16 |
| ljccdr202405 | subscriberid | 17 |
| ljccdr202405 | sourfilename | 18 |
| ljccdr202405 | accountid | 19 |
| ljccdr202405 | destfilename | 20 |
| ljccdr202405 | errorcode | 21 |
| ljccdr202405 | processtime | 22 |
| ljccdr202405 | city_code | 23 |
| ljccdr202405 | ad_id | 24 |
| ljccdr202405 | sub_serv_type | 25 |
| ljccdr202405 | package_info | 26 |
| ljccdr202405 | tarifftrack | 27 |
+--------------+------------------+------------------+
27 rows in set (0.01 sec)
mysql> alter table ljccdr202405 drop column target_prov ;
Query OK, 0 rows affected (0.07 sec)
mysql> select table_name,column_name,ordinal_position from information_schema.columns where table_name='ljccdr202405';
+--------------+------------------+------------------+
| table_name | column_name | ordinal_position |
+--------------+------------------+------------------+
| ljccdr202405 | rec_type | 1 |
| ljccdr202405 | serv_type | 2 |
| ljccdr202405 | content_type | 3 |
| ljccdr202405 | sell_prov | 4 |
| ljccdr202405 | custom_name | 5 |
| ljccdr202405 | contract_id | 6 |
| ljccdr202405 | order_id | 7 |
| ljccdr202405 | product_id | 8 |
| ljccdr202405 | action_id | 9 |
| ljccdr202405 | publication_name | 10 |
| ljccdr202405 | split_num | 11 |
| ljccdr202405 | ad_type | 12 |
| ljccdr202405 | fee | 13 |
| ljccdr202405 | telnum | 14 |
| ljccdr202405 | billingcycle | 15 |
| ljccdr202405 | subscriberid | 16 |
| ljccdr202405 | sourfilename | 17 |
| ljccdr202405 | accountid | 18 |
| ljccdr202405 | destfilename | 19 |
| ljccdr202405 | errorcode | 20 |
| ljccdr202405 | processtime | 21 |
| ljccdr202405 | city_code | 22 |
| ljccdr202405 | ad_id | 23 |
| ljccdr202405 | sub_serv_type | 24 |
| ljccdr202405 | package_info | 25 |
| ljccdr202405 | tarifftrack | 26 |
+--------------+------------------+------------------+
26 rows in set (0.00 sec)
2.2增加列，更新ordinal_position
mysql> alter table ljccdr202405 add column target_prov int;
Query OK, 0 rows affected (0.05 sec)
mysql> select table_name,column_name,ordinal_position from information_schema.columns where table_name='ljccdr202405';
+--------------+------------------+------------------+
| table_name | column_name | ordinal_position |
+--------------+------------------+------------------+
| ljccdr202405 | rec_type | 1 |
| ljccdr202405 | serv_type | 2 |
| ljccdr202405 | content_type | 3 |
| ljccdr202405 | sell_prov | 4 |
| ljccdr202405 | custom_name | 5 |
| ljccdr202405 | contract_id | 6 |
| ljccdr202405 | order_id | 7 |
| ljccdr202405 | product_id | 8 |
| ljccdr202405 | action_id | 9 |
| ljccdr202405 | publication_name | 10 |
| ljccdr202405 | split_num | 11 |
| ljccdr202405 | ad_type | 12 |
| ljccdr202405 | fee | 13 |
| ljccdr202405 | telnum | 14 |
| ljccdr202405 | billingcycle | 15 |
| ljccdr202405 | subscriberid | 16 |
| ljccdr202405 | sourfilename | 17 |
| ljccdr202405 | accountid | 18 |
| ljccdr202405 | destfilename | 19 |
| ljccdr202405 | errorcode | 20 |
| ljccdr202405 | processtime | 21 |
| ljccdr202405 | city_code | 22 |
| ljccdr202405 | ad_id | 23 |
| ljccdr202405 | sub_serv_type | 24 |
| ljccdr202405 | package_info | 25 |
| ljccdr202405 | tarifftrack | 26 |
| ljccdr202405 | target_prov | 29 | 《《《《《
+--------------+------------------+------------------+
27 rows in set (0.01 sec)
知道mysql库的同学都知道 ，这个不连续是不正常的，其实这个是连续的，实验的环境是中兴的库，有2个隐藏列GDB_BID ，GTID 。
mysql> select table_name,column_name,ordinal_position from information_schema.columns where table_name='ljccdr202405' order by 4,3 storagedb all;
+--------------+------------------+------------------+---------+
| TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | groupid |
+--------------+------------------+------------------+---------+
| ljccdr202405 | rec_type | 1 | 1 |
| ljccdr202405 | serv_type | 2 | 1 |
| ljccdr202405 | content_type | 3 | 1 |
| ljccdr202405 | sell_prov | 4 | 1 |
| ljccdr202405 | custom_name | 5 | 1 |
| ljccdr202405 | contract_id | 6 | 1 |
| ljccdr202405 | order_id | 7 | 1 |
| ljccdr202405 | product_id | 8 | 1 |
| ljccdr202405 | action_id | 9 | 1 |
| ljccdr202405 | publication_name | 10 | 1 |
| ljccdr202405 | split_num | 11 | 1 |
| ljccdr202405 | ad_type | 12 | 1 |
| ljccdr202405 | telnum | 13 | 1 |
| ljccdr202405 | billingcycle | 14 | 1 |
| ljccdr202405 | sourfilename | 15 | 1 |
| ljccdr202405 | accountid | 16 | 1 |
| ljccdr202405 | destfilename | 17 | 1 |
| ljccdr202405 | errorcode | 18 | 1 |
| ljccdr202405 | processtime | 19 | 1 |
| ljccdr202405 | city_code | 20 | 1 |
| ljccdr202405 | ad_id | 21 | 1 |
| ljccdr202405 | sub_serv_type | 22 | 1 |
| ljccdr202405 | package_info | 23 | 1 |
| ljccdr202405 | tarifftrack | 24 | 1 |
| ljccdr202405 | GDB_BID | 25 | 1 |
| ljccdr202405 | GTID | 26 | 1 |
| ljccdr202405 | xin1 | 27 | 1 |
| ljccdr202405 | xin2 | 28 | 1 |
| ljccdr202405 | xin3 | 29 | 1 |
| ljccdr202405 | rec_type | 1 | 2 |
| ljccdr202405 | serv_type | 2 | 2 |
| ljccdr202405 | content_type | 3 | 2 |
| ljccdr202405 | sell_prov | 4 | 2 |
| ljccdr202405 | custom_name | 5 | 2 |
| ljccdr202405 | contract_id | 6 | 2 |
| ljccdr202405 | order_id | 7 | 2 |
| ljccdr202405 | product_id | 8 | 2 |
| ljccdr202405 | action_id | 9 | 2 |
| ljccdr202405 | publication_name | 10 | 2 |
| ljccdr202405 | split_num | 11 | 2 |
| ljccdr202405 | ad_type | 12 | 2 |
| ljccdr202405 | telnum | 13 | 2 |
| ljccdr202405 | billingcycle | 14 | 2 |
| ljccdr202405 | sourfilename | 15 | 2 |
| ljccdr202405 | accountid | 16 | 2 |
| ljccdr202405 | destfilename | 17 | 2 |
| ljccdr202405 | errorcode | 18 | 2 |
| ljccdr202405 | processtime | 19 | 2 |
| ljccdr202405 | city_code | 20 | 2 |
| ljccdr202405 | ad_id | 21 | 2 |
| ljccdr202405 | sub_serv_type | 22 | 2 |
| ljccdr202405 | package_info | 23 | 2 |
| ljccdr202405 | tarifftrack | 24 | 2 |
| ljccdr202405 | GDB_BID | 25 | 2 |
| ljccdr202405 | GTID | 26 | 2 |
| ljccdr202405 | xin1 | 27 | 2 |
| ljccdr202405 | xin2 | 28 | 2 |
| ljccdr202405 | xin3 | 29 | 2 |
+--------------+------------------+------------------+---------+
58 rows in set (0.00 sec)
3、ob
3.1删除列，会更新ordinal_position
obclient [test]> select table_name,column_name,ordinal_position from information_schema.columns where table_name='gsm400settlecdr202405' order by ordinal_position;
+-----------------------+--------------------+------------------+
| table_name | column_name | ordinal_position |
+-----------------------+--------------------+------------------+
| gsm400settlecdr202405 | call_type | 1 |
| gsm400settlecdr202405 | imsi_number | 2 |
| gsm400settlecdr202405 | msisdn | 3 |
| gsm400settlecdr202405 | other_party | 4 |
| gsm400settlecdr202405 | ec_id | 5 |
| gsm400settlecdr202405 | ec_prov_code | 6 |
| gsm400settlecdr202405 | start_date_time | 7 |
| gsm400settlecdr202405 | call_duration | 8 |
| gsm400settlecdr202405 | msrn | 9 |
| gsm400settlecdr202405 | msc | 10 |
| gsm400settlecdr202405 | dest_msisdn | 11 |
| gsm400settlecdr202405 | service_type | 12 |
| gsm400settlecdr202405 | service_code | 13 |
| gsm400settlecdr202405 | visit_area_code | 14 |
| gsm400settlecdr202405 | roam_type | 15 |
| gsm400settlecdr202405 | user_type | 16 |
| gsm400settlecdr202405 | islocal | 17 |
| gsm400settlecdr202405 | infofee | 18 |
| gsm400settlecdr202405 | specialfee | 19 |
| gsm400settlecdr202405 | partialflag | 20 |
| gsm400settlecdr202405 | processtime | 21 |
| gsm400settlecdr202405 | billingcycle | 22 |
| gsm400settlecdr202405 | subscriberid | 23 |
| gsm400settlecdr202405 | sourfilename | 24 |
| gsm400settlecdr202405 | destfilename | 25 |
| gsm400settlecdr202405 | accountid | 26 |
| gsm400settlecdr202405 | errorcode | 27 |
| gsm400settlecdr202405 | tarifftrack | 28 |
| gsm400settlecdr202405 | total_free | 29 |
| gsm400settlecdr202405 | devicetype | 30 |
| gsm400settlecdr202405 | hregion | 31 |
| gsm400settlecdr202405 | grouptelnum | 32 |
| gsm400settlecdr202405 | tariffflag | 33 |
| gsm400settlecdr202405 | package_info | 34 |
| gsm400settlecdr202405 | bill_write_flag | 35 |
| gsm400settlecdr202405 | bds_proc_status | 36 |
| gsm400settlecdr202405 | cdrseq | 37 |
| gsm400settlecdr202405 | visit_locatio_code | 38 |
| gsm400settlecdr202405 | reserved1 | 39 |
| gsm400settlecdr202405 | reserved2 | 40 |
| gsm400settlecdr202405 | server_info | 41 |
| gsm400settlecdr202405 | billing_track | 42 |
+-----------------------+--------------------+------------------+
42 rows in set (0.084 sec)
obclient [test]> alter table gsm400settlecdr202405 drop COLUMN msc;
Query OK, 0 rows affected (0.198 sec)
obclient [test]> select table_name,column_name,ordinal_position from information_schema.columns where table_name='gsm400settlecdr202405' order by ordinal_position;
+-----------------------+--------------------+------------------+
| table_name | column_name | ordinal_position |
+-----------------------+--------------------+------------------+
| gsm400settlecdr202405 | call_type | 1 |
| gsm400settlecdr202405 | imsi_number | 2 |
| gsm400settlecdr202405 | msisdn | 3 |
| gsm400settlecdr202405 | other_party | 4 |
| gsm400settlecdr202405 | ec_id | 5 |
| gsm400settlecdr202405 | ec_prov_code | 6 |
| gsm400settlecdr202405 | start_date_time | 7 |
| gsm400settlecdr202405 | call_duration | 8 |
| gsm400settlecdr202405 | msrn | 9 |
| gsm400settlecdr202405 | dest_msisdn | 10 |
| gsm400settlecdr202405 | service_type | 11 |
| gsm400settlecdr202405 | service_code | 12 |
| gsm400settlecdr202405 | visit_area_code | 13 |
| gsm400settlecdr202405 | roam_type | 14 |
| gsm400settlecdr202405 | user_type | 15 |
| gsm400settlecdr202405 | islocal | 16 |
| gsm400settlecdr202405 | infofee | 17 |
| gsm400settlecdr202405 | specialfee | 18 |
| gsm400settlecdr202405 | partialflag | 19 |
| gsm400settlecdr202405 | processtime | 20 |
| gsm400settlecdr202405 | billingcycle | 21 |
| gsm400settlecdr202405 | subscriberid | 22 |
| gsm400settlecdr202405 | sourfilename | 23 |
| gsm400settlecdr202405 | destfilename | 24 |
| gsm400settlecdr202405 | accountid | 25 |
| gsm400settlecdr202405 | errorcode | 26 |
| gsm400settlecdr202405 | tarifftrack | 27 |
| gsm400settlecdr202405 | total_free | 28 |
| gsm400settlecdr202405 | devicetype | 29 |
| gsm400settlecdr202405 | hregion | 30 |
| gsm400settlecdr202405 | grouptelnum | 31 |
| gsm400settlecdr202405 | tariffflag | 32 |
| gsm400settlecdr202405 | package_info | 33 |
| gsm400settlecdr202405 | bill_write_flag | 34 |
| gsm400settlecdr202405 | bds_proc_status | 35 |
| gsm400settlecdr202405 | cdrseq | 36 |
| gsm400settlecdr202405 | visit_locatio_code | 37 |
| gsm400settlecdr202405 | reserved1 | 38 |
| gsm400settlecdr202405 | reserved2 | 39 |
| gsm400settlecdr202405 | server_info | 40 |
| gsm400settlecdr202405 | billing_track | 41 |
+-----------------------+--------------------+------------------+
41 rows in set (0.027 sec)
3.2增加列，会更新ordinal_position
obclient [test]> alter table gsm400settlecdr202405 add COLUMN msc12 varchar(13) ;
Query OK, 0 rows affected (0.124 sec)
obclient [test]> select table_name,column_name,ordinal_position from information_schema.columns where table_name='gsm400settlecdr202405' order by ordinal_position;
+-----------------------+--------------------+------------------+
| table_name | column_name | ordinal_position |
+-----------------------+--------------------+------------------+
| gsm400settlecdr202405 | call_type | 1 |
| gsm400settlecdr202405 | imsi_number | 2 |
| gsm400settlecdr202405 | msisdn | 3 |
| gsm400settlecdr202405 | other_party | 4 |
| gsm400settlecdr202405 | ec_id | 5 |
| gsm400settlecdr202405 | ec_prov_code | 6 |
| gsm400settlecdr202405 | start_date_time | 7 |
| gsm400settlecdr202405 | call_duration | 8 |
| gsm400settlecdr202405 | msrn | 9 |
| gsm400settlecdr202405 | dest_msisdn | 10 |
| gsm400settlecdr202405 | service_type | 11 |
| gsm400settlecdr202405 | service_code | 12 |
| gsm400settlecdr202405 | visit_area_code | 13 |
| gsm400settlecdr202405 | roam_type | 14 |
| gsm400settlecdr202405 | user_type | 15 |
| gsm400settlecdr202405 | islocal | 16 |
| gsm400settlecdr202405 | infofee | 17 |
| gsm400settlecdr202405 | specialfee | 18 |
| gsm400settlecdr202405 | partialflag | 19 |
| gsm400settlecdr202405 | processtime | 20 |
| gsm400settlecdr202405 | billingcycle | 21 |
| gsm400settlecdr202405 | subscriberid | 22 |
| gsm400settlecdr202405 | sourfilename | 23 |
| gsm400settlecdr202405 | destfilename | 24 |
| gsm400settlecdr202405 | accountid | 25 |
| gsm400settlecdr202405 | errorcode | 26 |
| gsm400settlecdr202405 | tarifftrack | 27 |
| gsm400settlecdr202405 | total_free | 28 |
| gsm400settlecdr202405 | devicetype | 29 |
| gsm400settlecdr202405 | hregion | 30 |
| gsm400settlecdr202405 | grouptelnum | 31 |
| gsm400settlecdr202405 | tariffflag | 32 |
| gsm400settlecdr202405 | package_info | 33 |
| gsm400settlecdr202405 | bill_write_flag | 34 |
| gsm400settlecdr202405 | bds_proc_status | 35 |
| gsm400settlecdr202405 | cdrseq | 36 |
| gsm400settlecdr202405 | visit_locatio_code | 37 |
| gsm400settlecdr202405 | reserved1 | 38 |
| gsm400settlecdr202405 | reserved2 | 39 |
| gsm400settlecdr202405 | server_info | 40 |
| gsm400settlecdr202405 | billing_track | 41 |
| gsm400settlecdr202405 | msc12 | 43 |
+-----------------------+--------------------+------------------+
42 rows in set (0.029 sec)
知识总结
1、在pg、磐维和mogdb 数据库删除列，没有更新ordinal_position ，会影响到交换分区
https://support.enmotech.com/article/search/8982
2、在mysql、中兴 和ob 数据库中，ORDINAL_POSITION 字段通常用于表示表中列的顺序，它是从1开始连续递增的整数，用于指示列在表结构中的排列位置。每一列都有一个对应的 ORDINAL_POSITION 值，这个值在没有结构变更的情况下是连续的。
浙公网安备 33010602011771号