mysql年月日去重,mysql实战总结

1、mysql 5.7以上版本json类型字段

// key打双引号可支持中文

两种查询方式：

select * from sample_data where task_id=233 and data->'$."City"' = "Galle"

select * from sample_data where task_id=233 and JSON_EXTRACT(data,'$."书名"')="网络生活预防知识读本"

2、mysql errorCode 1130, state HY000解决方案

select host, user from user;

update user set host = '%' where user = 'root';

flush privileges;

3、mysql锁表问题

记一次因操作不当，导致mysql表锁住

产生原因：用navicat工作将label表某字段由date类型改为datetime类型

解决办法：

show full processlist

找到相应的info信息，比如：

ALTER TABLE `prd_test`.`lable`

MODIFY COLUMN `update_date` datetime(0) NULL DEFAULT NULL COMMENT '更新时间' AFTER `is_delete`

根据info找到对应的进程id，杀掉进程：

kill 1440821

4、mysql timestamp字段根据当前时间戳更新

利用这个特性，就可以利用mysql来维护create_time和update_time字段，而不需要我们来维护了

5、mysql  length函数

length: 一个汉字是算三个字符，一个数字或字母算一个字符

char_length: 不管汉字还是数字或者是字母都算是一个字符

//举例

select length('hello你好');

select CHAR_LENGTH('hello你好');

6、sql复制表结构和表数据语句

//复制表结构及其数据

create table table_name_new as select * from table_name_old

//只复制表结构

create table table_name_new like table_name_old

//只复制表数据

//如果两个表结构一样

insert into table_name_new select * from table_name_old

//如果两个表结构不一样

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

//mysql不支持SELECT ... INTO

//加where条件只复制表结构而不复制表数据

SELECT * INTO newtable FROM table1 WHERE 1=0

7、mysql设置主键自增长

--查询表自增长的值

SELECT Auto_increment

FROM information_schema.`TABLES`

WHERE Table_Schema='test'

AND table_name = 'student'

--将列修改为自增长

alter table student modify id int(11) auto_increment;

--设置表自增长值

alter table student AUTO_INCREMENT = 100;

8、mysql模糊查询表名

show tables like '%Organization%'

9、mysql查询和设置最大连接数

show variables like 'max_connections'

set GLOBAL MAX_CONNECTIONS=500;

10、mysql group_concat函数长度限制

show variables like 'group_concat_max_len'

SET GLOBAL group_concat_max_len = 10240

11、mysql根据年月日分组

select DATE(time_stamp) from tender GROUP BY DATE(time_stamp) order by time_stamp desc

12、mysql日期时间比较

字段是timestamp类型：

select time_stamp from tender where time_stamp > '2019-11-25'

或

select time_stamp from tender where time_stamp > STR_TO_DATE("2020-01-01","%Y-%m-%d")

查询本月数据：

where DATE_FORMAT(a.time_stamp,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')

查询近7天数据

where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(a.time_stamp)

13、mysql去重

delete from tender_lable_relation where id not in (

select a.id from (

select min(id) as id from tender_lable_relation GROUP BY tender_id,attr1

) a

)

不能写成这样，否则报1093的错：

delete from tender_lable_relation where id not in (

select min(id) as id from tender_lable_relation GROUP BY tender_id,attr1

)

如果mysql数据达到百万、千万级别，这种去重方式会十分缓慢，几个小时、十几个小时、几十个小时都不一定，因为每次删除数据需要更新索引；此时需要采取标题14的方式去去重

14、mysql如何快速删除大量数据

当mysql数据达到百万、千万级别时，先将需要保留的数据复制到临时表，然后drop需要删除大量数据的表，最后重命名临时表

CREATE table tender_lable_relation_tmp as select * from tender_lable_relation where id in (select min(id) from tender_lable_relation GROUP BY tender_id,attr1);

drop table tender_lable_relation;

rename table tender_lable_relation_tmp to tender_lable_relation;