Mysql实用命令建表/分区/加索引/条件删除与条件新增

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

)