阅读量:4
-- 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;
