mysql怎樣為表格定義索引?1、創建表通用sqlCREATE 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='通用表';，接下來我們就來聊聊關于mysql怎樣為表格定義索引?以下内容大家不妨參考一二希望能幫到您!

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

)

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!