跳至主要內容

MySQL_配置篇

Jin大约 9 分钟

MySQL_配置篇

1、 SQL MODEL 配置

  • ONLY_FULL_GROUP_BY 要求sql中select后面的字段必须出现在group by后面，或者被聚合函数包裹，不然会抛出错误

  • MYSQL8以上已经取消了NO_AUTO_CREATE_USER

# [mysqld]
# ==========SQL MODEL配置[Set the SQL mode to strict]==========
# sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
# 必须加到[mysqld]下面 select @@GLOBAL.sql_mode;
# sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

2、日志配置

慢查询日志配置

# [mysqld]
# ==========慢查询日志配置[Slow logging]==========
#设置日志的输出方式-默认是输出到文件的-下面是输出到文件里面（TABLE，数据库表）log_output=FILE,TABLE
log-output=FILE
#（on表示启用，0表示关闭）
slow-query-log=on
#（慢查询日志文件，在上面的datadir目录之下）
# slow_query_log_file="JIN-slow.log"
slow_query_log_file="D:\Jin\MySQL\Log\Slow-Query\JIN-slow.log"
#（定义超过3秒的查询就是慢查询，测试时候用，生产环境应该根据实际需要调整在1到10之间的数）,默认10s
long_query_time=3
#记录执行较慢的管理语句
log_slow_admin_statements =1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1

查询日志[General]配置

# [mysqld]
# ==========查询日志[General]配置==========
#该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启
general-log=1
#设置日志的文件名 ， 如果没有指定， 默认的文件名为 host_name.log
general_log_file="D:\Jin\MySQL\Log\General_Log\JIN.log"

错误日志配置

# [mysqld]
# ==========错误日志配置[Error Logging]==========
# log-error="JIN.err"
log-error="D:\Jin\MySQL\Log\Log-Error\JIN.err"

二进制日志配置[log-bin]

# ==========二进制日志配置[log-bin]==========
# log-bin="JIN-bin"
log-bin="D:\Jin\MySQL\Log\Log-Bin\JIN-bin"
# 配置bin-log日志格式，默认为ROW
# STATEMENT：基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。
# binlog_format=STATEMENT

MySQL目录下新建四个日志文件夹Log-Bin、Log-Error、Slow-Query、General_Log

image-20220618185422989
image-20220618185422989

3、本地大数据加载配置

local_infile

问题描述

从服务器本地文件导入数据到表中时执行语句报错

load data  infile 'D:/Jin/MySQL/Data/Uploads/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

执行错误信息反馈如下：

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

问题原因

MySQL 默认关闭了本地数据加载功能

开启本地数据加载功能，参考文档： https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html

解决方案参考：https://stackoverflow.com/questions/59993844/error-loading-local-data-is-disabled-this-must-be-enabled-on-both-the-client

服务器端：

先连接 MySQL 服务：

mysql -u root -p

查看全局系统变量：

show global variables like 'local_infile';

SHOW GLOBAL VARIABLES LIKE 'local_infile';
image-20220525203957964
image-20220525203957964

然后开启本地数据加载功能

set global local_infile = ON;
#或者
SET GLOBAL local_infile = 1;

SHOW GLOBAL VARIABLES LIKE 'local_infile';

客户端：

连接服务时指定开启或关闭本地数据加载功能，如果关闭则需要通过 --load-data-local-dir 指定本地数据加载目录

mysql --local-infile=0 --load-data-local-dir=/my/local/data

当然你直接设置 local-infile 为 1 ，这样就不用设置文件夹限制了

如果启用了数据加载，则默认情况下在 MySQL 客户端库中或通过指定 --local-infile[=1]，将忽略 --load-data-local-dir 选项。

如果禁用了数据加载，则默认情况下在 MySQL 客户端库中或通过指定 --local-infile=0，则应用 --load-data-local-dir 选项

secure_file_priv

问题描述

上述如果执行仍报错：

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

报错原因：其实原因很简单，因为在安装MySQL的时候限制了导入与导出的目录权限。只允许在规定的目录下才能导入。（也就是说没权限）

解决：

用以下mysql语句 查看secure_file_priv 对应的值

SHOW GLOBAL VARIABLES LIKE 'secure_file_priv';
image-20220525204605636
image-20220525204605636

查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。

  1. secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。

  2. secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。

  3. secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

  4. 又因为 secure_file_priv 参数是只读参数，不能使用set global命令修改。

解决方法：

在my.ini 添加

# [mysqld]
# Secure File Priv.
secure-file-priv=""
# 开启本地数据加载功能
local_infile = ON

问题描述

[01000][1261] Row 220028 doesn't contain data for all columns

查看 MySQL 当前连接的 sql_mode

show variables like 'sql_mode';

临时修改 sql_mode

set sql_mode='';

最后

检查路径是否有中文

检查路程斜杠（反斜杠\ 失效）

load data  infile 'D:/Jin/MySQL/Data/Uploads/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
load data  infile 'D:/Jin/MySQL/Data/Uploads/tb_sku2.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
load data  infile 'D:/Jin/MySQL/Data/Uploads/tb_sku3.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
load data  infile 'D:/Jin/MySQL/Data/Uploads/tb_sku4.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
load data infile 'D:/Jin/MySQL/Data/Uploads/tb_sku5.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

成功

image-20220525221205930
image-20220525221205930

测试大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

可以执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;

示例演示:

A. 创建表结构

CREATE TABLE `test_100w`
(
    `id`       INT(11)     NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(50) NOT NULL,
    `name`     VARCHAR(20) NOT NULL,
    `birthday` DATE    DEFAULT NULL,
    `sex`      CHAR(1) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_user_username` (`username`)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8;

B. 设置参数

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

C. load加载数据

# cmd终端执行：
load data local infile 'D:/Jin/MySQL/Data/Uploads/load_user_100w_sort.sql' into table test_100w fields terminated by ',' lines terminated by '\n' ;

# DataGrip执行：
load data infile 'D:/Jin/MySQL/Data/Uploads/load_user_100w_sort.sql' into table test_100w fields terminated by ',' lines terminated by '\n' ;

image-20220526204121235 我们看到，插入100w的记录，根据服务器性能决定执行速度

服务器性能高的17s就完成了，性能很好。

4. innodb buffer pool

https://www.cnblogs.com/innocenter/p/14948857.html

F&Q

MySQL中varchar无法使用max()函数的解决办法

刚好今天遇到一个问题，MySQL库里的varchar类型的数据无法使用max()函数，对MySQL不怎么熟，所以查了查，看到了这两个方法不错。

1、将需要使用max()函数的字段后面加0，比如字段：field是要使用max()函数的字段，那么只要

SELECT MAX(field+0) FROM tablename

这样就可以了，这应该是MySQL内部自己转的，不是很清楚额~

MyBatisPlus

        List<Object> nodeKeys = this.baseMapper.selectObjs(Wrappers.<T>query()
                .select(" MAX(node_key + 0)").eq("parent_id", parentId)
        );

如上图所示，会生成double类型，结果例如：1001.0

2、同样情况，使用cast()函数

SELECT MAX(cast(field as decimal)) FROM tablename

这里我是转成货币类型，这样max就有效了。

MyBatisPlus

        List<Object> nodeKeys = this.baseMapper.selectObjs(Wrappers.<T>query()
                .select(" MAX(cast(node_key as decimal))").eq("parent_id", parentId)
        );

decimal

MySL DECIMAL数据类型用于在数据库中存储精确的数值。我们经常将DECIMAL数据类型用于保留准确精确度的列，例如会计系统中的货币数据。

语法:

column_name  DECIMAL(P,D);

在上面的语法中：

  • P是表示有效数字数的精度。 P范围为1〜65。

  • D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P。

  1. P是数字的最大位数，他的范围是从1-65；
  2. D是小数点后的位数，他的范围是0-30，并且不能大于P。
  3. 如果P被省略了，那么P的值默认为10，
  4. 如果D被省略了，那么D的值默认为0.
  5. DECIMAL(P，D)表示列可以存储D位小数的P位数。十进制列的实际范围取决于精度和刻度。
  6. 与INT数据类型一样，DECIMAL类型也具有UNSIGNED和ZEROFILL属性。 如果使用UNSIGNED属性，则DECIMAL UNSIGNED的列将不接受负值。
  7. 如果使用ZEROFILL，MySQL将把显示值填充到0以显示由列定义指定的宽度。 另外，如果我们对DECIMAL列使用ZERO FILL，MySQL将自动将UNSIGNED属性添加到列。

举例：

  1. decimal(5,2) 所指代的范围是-999.99~999.99 数字的最大位数是5位，小数点右侧是2位，即有两位小数。
  2. decimal(7,6)所指代的范围是-9.999999~9.999999 数字的最大位数是7位，小数点右侧是6位，即有六位小数。
  3. decimal(18,9)小数位左右各为9位数，此数据所占以上表格即为4字节整数位，4字节小数位；
  4. decimal(20,6)整数位为14位，小数位位6位，此数据需要4个字节给整数位的9位和3字节给另外的5位整数，另外小数位需要3个字节，总共是4+3+3 = 10个字节。

补充：

decimal不能够存储以“+”、“-”和“0”开头的数据，如果是“+”开头默认存为正数，而“-”开头则不存储

https://blog.csdn.net/qq_36543562/article/details/110124237

错误：2006, 'MySQL server has gone away'

报错截图

image-20230228124552172
image-20230228124552172
show global status like 'uptime';
image-20230228124656831
image-20230228124656831
show global variables like'%timeout';
image-20230228124750147
image-20230228124750147

wait_timeout 是28800秒，即mysql链接在无操作28800秒后被自动关闭

解决方案

打开navicat的菜单中的tools(工具)，选择server monitor(服务器监控)，然后在左列选择数据库，右列则点选variable(变量)表单项，寻找max_allowed_packet，将其值改大。

image-20230228135712420
image-20230228135712420
image-20230228135931659
image-20230228135931659

my.ini配置文件

  • 一是 查询太大 太长
  • 二是 连接超时自动断开（这个是最常见的错误）

这两个修改方式都是 设置[mysqld]中的变量:

[mysqld]
max_allowed_packet = 64M
wait_timeout = 2880000
interactive_timeout = 2880000

max_allowed_packet是mysql允许最大的数据包，也就是你发送的请求；

wait_timeout是等待的最长时间，这个值大家可以自定义，但如果时间太短的话，超时后就会现了MySQL server has gone away #2006错误。

max_allowed_packet参数的作用是，用来控制其通信缓冲区的最大长度

参考链接open in new window

贡献者: Jin
Jin 的个人经历、个人兴趣、个人感悟、个人思考
Copyright © 2025 Jin