1、创建表通用sql
CREATE TABLE `ext_base_config` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`description` varchar(200) DEFAULT NULL COMMENT '字段表述',
`update_by` varchar(200) DEFAULT NULL COMMENT '更新者',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) COMMENT='通用表';
对于数据量比较大的,可以添加索引和分区
添加普通索引:
aLTER TABLE `ext_moneybase_dAIly` ADD INDEX index2 ( trade_date )
添加联合索引
aLTER TABLE `ext_moneybase_daily` ADD INDEX index3 ( ts_code,trade_date )
用数字的列添加Hash索引
ALTER TABLE ext_moneybase_daily PARTITION BY HASH (id) PARTITIONS 100 ;
#查看分区
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM information_schema.partitions WHERE
table_schema = SCHEMA()
AND table_name='ext_moneybase_daily';
通过查询条件并删除
select 和 in 不能直接使用,需要外面包一个壳,demo如下:
delete from ext_moneybase_daily where trade_date in (
SELECT MAX(a.cal_date) AS trade_date FROM (
select cal_date from ext_moneybase_trade_cal s1
left join(
select trade_date,count(*) dcount from ext_moneybase_daily group by trade_date
) s2 on s1.cal_date=s2.trade_date
where s1.amount!=s2.dcount
) AS a GROUP BY a.cal_date
)
更新数据demo
update ext_moneybase_trade_cal set isdo='2' where isdo='1' and cal_date not in(
select distinct trade_date from ext_moneybase_daily
)