mysql实战总结

绝地灬酷狼 2023-02-28 15:19 16阅读 0赞

# 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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hsXzE4MDM_size_16_color_FFFFFF_t_70][]

找到相应的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字段，而不需要我们来维护了

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hsXzE4MDM_size_16_color_FFFFFF_t_70 1][]

# **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;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hsXzE4MDM_size_16_color_FFFFFF_t_70]: /images/20230209/5a481199a66f46679020d87a1b6a4110.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hsXzE4MDM_size_16_color_FFFFFF_t_70 1]: /images/20230209/6574a7a5663d45a7afec03414ab05282.png