-- MySQL 列转行

set global group_concat_max_len=102400; set group_concat_max_len=102400; SELECT @@global.group_concat_max_len; SELECT @@group_concat_max_len; select table_name,concat(group_concat(COLUMN_NAME order by ORDINAL_POSITION separator ',')) as all_columns from information_schema.COLUMNS tb1 where table_schema='test' and table_name='table1' group by table_name;

使用方法：

mysql> set group_concat_max_len=102400; Query OK, 0 rows affected (0.00 sec)  mysql> SELECT @@global.group_concat_max_len; +-------------------------------+ | @@global.group_concat_max_len | +-------------------------------+ |                        102400 | +-------------------------------+ 1 row in set (0.00 sec)  mysql> SELECT @@group_concat_max_len; +------------------------+ | @@group_concat_max_len | +------------------------+ |                 102400 | +------------------------+ 1 row in set (0.00 sec)  mysql> select table_name,concat(group_concat(COLUMN_NAME order by ORDINAL_POSITION separator ',')) as all_columns     -> from information_schema.COLUMNS tb1     -> where table_schema='ccic_prod_mass1'     -> and table_name='t_pa_mass_policy_index'     -> group by table_name; +------------------------+------------------------------------------------------------------------------------------------------------------------------+ | table_name             | all_columns                                                                                                                  | +------------------------+------------------------------------------------------------------------------------------------------------------------------+ | t_pa_mass_policy_index | INDEX_RECORD_ID,BUSINESS_OBJECT_ID,INDEX_ID,POLICY_ID,FIELD_VALUE,DYNAMIC_FIELDS,INSERT_TIME,UPDATE_TIME,INSERT_BY,UPDATE_BY | +------------------------+------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)  mysql>    ##把去除自增列把本表数据重复插入本表 insert into  ccic_prod_mass1.t_pa_mass_policy_index(BUSINESS_OBJECT_ID,INDEX_ID,POLICY_ID,FIELD_VALUE,DYNAMIC_FIELDS,INSERT_TIME,UPDATE_TIME,INSERT_BY,UPDATE_BY) select BUSINESS_OBJECT_ID,INDEX_ID,POLICY_ID,FIELD_VALUE,DYNAMIC_FIELDS,INSERT_TIME,UPDATE_TIME,INSERT_BY,UPDATE_BY from ccic_prod_mass2.t_pa_mass_policy_index;