沐光

记录在前端之路的点点滴滴

Mysql 的小技巧

更新于 分类于 Valine： 本文字数： 1.8k 阅读时长 ≈ 2 分钟

前言

近期开始学习 Node 和 MySQL 相关的知识了，项目也开始从纯前端转向了“全栈”（刚刚起步）。起初还是挺痛苦的，特别是数据库设计和查询，大学学习的东西基本都还回去了，不过在一次次问题中也渐渐总结了些经验，这里就记录一下“初学者”难免会遇到的一些问题。

设置默认值

其实先处理模型，然后再根据模型建库和表，基本上没什么太大问题（毕竟都是可视化操作，不写 SQL 代码生成了）。不过，在可视化建表的过程中，有时候为了图快往往容易忽视一些配置，如：

  • int 的默认值部分配置

int 类型

  • datetime 默认值部分配置

datetime 类型

根据情况配置对应默认值可以减少代码内的部分重复操作，如：

  • 配置 create_time 默认值为 CURRENT_TIMESTAMP， 那么每次新增时就可以不用带上时间戳了。
  • 配置 modify_time 默认值勾选上 “根据当前时间戳更新”，那么每次编辑时可以不用去配置修改时间，对于重复数据操作会比较方便。

日期更新

当然，也有情况是需要在进行某些操作后，更新其它数据的一些信息，这之中比较麻烦的是“日期”相关的操作，在网上也没找到比较合适的博文来解决。这里在翻查了 Mysql 的一些函数，顺利解决了这么一个问题，修改数据时间的 sql 为：

1
2
3
update xxx_table
set xxx_time = CURRENT_TIMESTAMP()
where xxx_id = 1

配合 mysql 的时间函数能比较方便的处理时间相关的操作。

ALTER 和 UPDATE 区别

最开始写 sql 时，总是习惯性的使用 ALTER，毕竟是 ALTER 是修改的意思嘛。实际上这两个区别很大，ALTER 是修改表结构的，而 UPDATE 是更新表数据信息的。因此在平时对数据进行 CRUD 时，记住使用 UPDATE 即可。这里记录一下：

  • ALTER、DROP 是结构上的更新和删除
  • UPDATE、DELETE 是数据上的更新和删除

group_concat 的长度限制

在做函数聚合处理时，会经常使用到 group_concat 函数对字段进行聚合，但是 mysql 默认对聚合的字串设有长度限制（没记错的话应该是 1024）,这会导致在取值的时候对于比较长的字段就直接被截取掉了，还比较难发现问题。

对于自己的数据库，我们可以使用如下命令查看默认长度：

1
show variables like 'group_concat_max_len';

如果要修改其长度限制的话，有两种方法：

（1）更改 Mysql 配置文件

1
2
# 在配置文件中加入如下内容：
group_concat_max_len = 1024000

（2）在 Navicat 内运行如下命令

1
2
set global group_concat_max_len = 1024000;
set session group_concat_max_len = 1024000;

注意： 该方法缺点是重启服务后设置失效

mysql 连接阻塞问题

最近遇到搭建的测试环境报错，查看日志发现是 mysql 的 max_connect_errors 报错导致服务器 ip 被禁止连接 mysql。但是自己通过 navcat 能够连接上数据库，查了下原因，发现应该是中间有一版本的代码部署后，node 代码数据库连接部分配置失误，导致错误连接数过大，ip 被禁止连接 mysql 了，针对不同情况有不同的解决方法，这里都列举出来：

max_connect_errors 值过小，容易出错

通过 navicat 连接上 mysql，查询一下 mysql 的 max_connect_errors 属性是否值过小，较小时代码更新过程中容易产生 max_connect_errors 错误导致 mysql 连接不上，查询并更新方法如下：

1
2
3
4
5
# 查询连接数
show variables like '%max_connect_errors%';

# 设置连接数
set global max_connect_errors = 1000;

累计连接错误数超出

如果查询到的 max_connect_errors 数值已经比较大了，但是仍然报 max_connect_errors 超出的错误，那么应该是 mysql 的错误连接累计数量超出了，此时需要清空一下 max_connect_errors 数，在 navicat 的查询中输入：

1
flush hosts;

mysql 的 max_connect_errors 重启后变小

因为 mysql 重启后会重新读取 mysql 的 conf 文件，如果不想每次重启 mysql 都需要重新更改 max_connect_errors 数值，那么需要修改一下 my.cnf 文件，将一下内容的值改改

1
max_connect_errors = 1000

insert 数据重复问题

在插入数据时，有时候会碰到插入的数据重复，因此可以使用 insert ... update ... 语句来进行插入/更新。

使用语法大致如下：

1
2
3
4
5
insert into target_table(unique_col1, col2, col3)
values (val1, valb, valc), (val1, val2, val3)
on duplicate key update
  col2 = values(col2),
  col3 = values(col3)

意思可理解为这样：

  • 当插入的 unique_col1 值不存在时，进行新增操作，其值分别对应为：(val1、val2、val3)
  • 当插入的 unique_col1 值存在时，进行更新操作，分别更新 col2 和 col3 的值为传入 values 组对应的 val2 和 val3

注意，唯一值重复时，后面的数据会覆盖前面的数据内容（即只取最新数据）

参考文档