mysql创建、删除、查询索引

mysql创建、删除、查询索引

1、查询索引
SHOW INDEX FROM ‘表名’

2、创建索引
ALTER TABLE ‘表名’ ADD INDEX index_open_id ( ‘列名’)

3、删除索引
drop index index_open_id on ‘表名’ ;

完整展示：
SHOW INDEX FROM goods_prize_name_list

ALTER TABLE personal_data ADD INDEX index_open_id ( open_id )

drop index index_open_id on personal_data ;

mysql获取当天时间的查询
select *
from goods_prize
where DATE_FORMAT(goods_prize.end_time, ‘%Y-%m-%d’)=DATE_FORMAT(NOW(), ‘%Y-%m-%d’)

mysql随机获取数据库某一张表的当天的一条不为null的数据

SELECT
*
FROM goods_prize_detail t1 INNER JOIN (
SELECT
ROUND(
RAND( ) * ( ( SELECT MAX( id ) FROM goods_prize_detail ) -
( SELECT MIN( id ) FROM goods_prize_detail ) ) + ( SELECT MIN( id ) FROM goods_prize_detail )
) AS id
) AS t2
WHERE
t1.id >= t2.id
and t1.prize_code is not null
and DATE_FORMAT(t1.create_time, ‘%Y-%m-%d’)=DATE_FORMAT(NOW(), ‘%Y-%m-%d’)
ORDER BY t1.id
LIMIT 1 FOR UPDATE