最近在做datax的数据迁移,里边涉及一些数据库表字段,由于业务原因,产生好多多字段表,为了简单,进行一个简单的整理,方便后续使用。
获取数据库表字段:
SELECT CONCAT('"',COLUMN_NAME,'",')
FROM INFORMATION_SCHEMA.Columns
WHERE table_schema ='rts_pubdata_src'
AND table_name ='rts_market'
获取单表select查询:
SELECT CONCAT('select ',COLUMN_NAME,' from ',table_name),table_name
FROM
(
SELECT GROUP_CONCAT( COLUMN_NAME ) AS COLUMN_NAME,table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema ='rts_pubdata_src'
AND table_name = 'rts_market'
GROUP BY table_name
)a;
在添加一些常用的sql记录下
ALTER TABLE rts_shop ADD opening_time DATETIME NOT NULL DEFAULT '2019-09-30 00:00:00' COMMENT '开业时间';
ALTER TABLE rts_shop ADD corporate_business_hours VARCHAR(50) NULL COMMENT '对公营业时间';