当前位置：首页 > news >正文

mysql-造数据/列转行

news 来源：原创 2024/9/20 15:38:13

-- 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;