Mysql日常使用注意事项(”坑“) , 官方文档的解读

最新推荐文章于 2022-12-07 20:21:22 发布
最新推荐文章于 2022-12-07 20:21:22 发布
阅读量2.7k 收藏
点赞数
分类专栏： mysql 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/DavinQi/article/details/111193207
版权

 

 

 

第一个 SQL DDL 为什么创建，修改表异常。

场景1，建表。

CREATE TABLE t_1 (
id INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(65535) COLLATE utf8_bin DEFAULT '',
value VARCHAR(10) COLLATE utf8_bin NOT NULL COMMENT '',
age INT(10) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表'

报错：Column length too BIG ..... name(max = "21845") 列太大了？

 

太长那我name 改成小于21845个字符的 21840个 试试？

CREATE TABLE t_1 (
id INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(21840) COLLATE utf8_bin DEFAULT '',
value VARCHAR(10) COLLATE utf8_bin NOT NULL COMMENT '',
age INT(10) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表'

继续报错：Row size too Large ..... maxmun row size is "65535"（not counting BLOBS）？ 行数据太大了？？？

 

太长那我列数据再小点？ 改成小于10000 个字符试试？

CREATE TABLE t_1 (
id INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(10000) COLLATE utf8_bin DEFAULT '',
value VARCHAR(10) COLLATE utf8_bin NOT NULL COMMENT '',
age INT(10) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表'

OK 可以了 。。。

 

场景2，可能这时我们业务变动了，改表加字段。。 （忽略Online DDl）

ALTER TABLE t_11
ADD COLUMN key VARCHAR(66535) DEFAULT '' NOT NULL ;

 

。。同样的错误.....

那么，WHY ？

这里查下官方文档的描述吧 。
https://dev.mysql.com/doc/search/?d=201&p=1&q=1118


官方文档里说到了两个限制：
1- 列数的限制 ：
MySQL对每个表有4096列的硬限制的前提下：
（InnoDB 变长列的页外存储可以避免 InnoDB行大小限制）
还有额外限制：行数据大小的限制，存储格式和字符集等因素，存储引擎的限制（InnoDB 1017列），索引等虚拟列的限制


2- 每行数据大小的限制 :
MySQL对行有 65,535字节的最大行限制的前提下
(1)InnoDB对页限制 https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html 。
- 表最多1017列
- 表最多64个 二级索引。
- 多列索引最多允许16列。
- 最大的行小于页面的一半。例如，默认的最大innodb_page_size16KB行大小 约为8000个字节。
那么我查询下我的Mysql的页是多大？

show VARIABLES like "innodb_page_size";
innodb_page_size 16384 16KB

(2)不同的存储格式使用不同数量的页面标题和尾部数据

扩展 - MySQL InnoDB 逻辑存储结构

MYSQL的文件空间管理
https://dev.mysql.com/doc/refman/8.0/en/innodb-file-space.html

总的来说 ： 每个表的列数，行大小是有限制的，

第二个 SQL select * from is null DML

CREATE TABLE t_2 (
id int(11) DEFAULT NULL,
d_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB

 

insert into t_2 (id) select 1;
insert into t_2 select 2, now();
insert into t_2 (id) select 3;

然后我查询：

SELECT * from t_2 WHERE d_time is null ;

为什么我查询的是NULL 他返回我的是 这个"0000-00..."?

这里再查下官方文档。
https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_is-null

 

解释一下：就是官方说这个查询的条件列中已经声明了这个数据是 not null 的字段列，查询时时会查到这个”0000“的，原因是这个ODBC。

这个SQL在昨天讲的时候，有同学问更改、插入NULL会有什么问题呢？

然后写这个文档时我试了下：
先查看下当前Mysql 的 SQL_MODE的格式是什么？
公司测试库：

insert into t_2 select 4, NULL ;

竟然成了？

 

 

为啥？

官文：https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

||>show VARIABLES like 'sql_mode';
sql_mode NO_ENGINE_SUBSTITUTION


好吧，因为不是一个严格的 SQL_MODE ! 我自己设置成严格级别。
（ 注意这块使用的会话级别的，千万不要用全局级别的，如果MYSQL重启会变成全局的 ）


SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
show VARIABLES like 'sql_mode';

再插入。报错了。。。
insert into t_2 select 6, NULL ;

 

 

 

第三个 SQL DQL order by limit 分页不对。

CREATE TABLE t_3 (
id INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(300) COLLATE utf8_bin DEFAULT '',
value VARCHAR(10) COLLATE utf8_bin NOT NULL COMMENT '',
age INT(10) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表'

插入一批数据。

INSERT into t_3 (name,value,age) VALUES ("张三","java",10);
INSERT into t_3 (name,value,age) VALUES ("张三","php",12);
INSERT into t_3 (name,value,age) VALUES ("李四","java",10);
INSERT into t_3 (name,value,age) VALUES ("王五","php",10);

注意这里的age没有索引。 数据是这样的 ： SELECT * FROM t_3 ORDER BY age

 

然后我做个分页

第一页
SELECT * FROM t_3 ORDER BY age limit 0, 2;

第二页
SELECT * FROM t_3 ORDER BY age limit 2, 2;

为什么ID=4 出现了两次？ 看下执行计划

EXPLAIN SELECT id ,age FROM t_311 ORDER BY age limit 0 , 2;

Using filesort ? 就是说他是内存排序的？每次排序都会重新加载到内存？所以造成的问题吗？

加索引”index_age“。

ALTER TABLE t_3 ADD INDEX index_age ( age ) ;
再次查询：
SELECT * FROM t_3 ORDER BY age limit 0, 2;
SELECT * FROM t_3 ORDER BY age limit 2, 2;
无效。还是分错页！

再次查看执行计划：

那么到底应该怎么查分页啊？ 如果我们不需要全字段，那么可以这样。

SELECT id, age FROM t_3 ORDER BY age limit 2, 2;
再次查看执行计划：

既用上了索引，有用上了索引覆盖。

需要全字段，那么可以这样。

SELECT * FROM t_3 FORCE INDEX(index_age) ORDER BY age limit 0, 2;

参考官方文档： https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

第四个 SQL DML update set and 。

update a set a = a and b=b where ?

利用上个SQL的数据。更新一个值试试。
SELECT * FROM t_3 ;

update t_3 SET age = 8 and name= 'js' WHERE id = 1;
SELECT * FROM t_3 ;

WHY ？ value= ”js“为啥没更新？

官文： https://dev.mysql.com/doc/refman/8.0/en/update.html



查看官方文档可以知道 需要用”逗号“分开多个

如果是and 就变成的判断语句 ：
update t_3 SET age = （8 and name= 'js' WHERE id = 1 ） ;

第五个 SQL 有索引页update有可能锁全表。

CREATE TABLE t_5 (
c1 int(10) unsigned NOT NULL DEFAULT '0',
c2 int(10) unsigned NOT NULL DEFAULT '0',
c3 int(10) unsigned NOT NULL DEFAULT '0',
c4 int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (c1),
KEY c2 (c2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入实验数据

INSERT into t_5 (c1,c2,c3,c4) VALUES (0,0,0,0);
INSERT into t_5 (c1,c2,c3,c4) VALUES (1,1,1,0);
INSERT into t_5 (c1,c2,c3,c4) VALUES (3,3,3,0);
INSERT into t_5 (c1,c2,c3,c4) VALUES (4,2,2,0);
INSERT into t_5 (c1,c2,c3,c4) VALUES (6,8,5,0);
INSERT into t_5 (c1,c2,c3,c4) VALUES (7,6,6,10);
INSERT into t_5 (c1,c2,c3,c4) VALUES (10,10,4,0);

注意 SESSION-2 查询的c2=1 ,SESSION-1 查询的是 c2 >= 6； 也就是加锁在 （6 , 12]

执行顺序SESSION-1SESSION-2其他描述
1BEGIN;2 
2UPDATE t_5 SET c4 = 101 WHERE c2 >= 6 and c2 <= 12 ;  
3 SELECT * from t_5 WHERE c2=1 FOR UPDATE; 【await...】SESSION-2 一直等待中
4COMMIT;  
5 SELECT do. 
6   

查询当前的事务： SELECT * FROM information_schema.INNODB_TRX

查看当前的锁等待：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

执行顺序SESSION-1SESSION-2其他描述
1BEGIN;2 
2UPDATE t_51 SET c4 = 101 WHERE c2 >= 8 and c2 <= 12 ;  
3 SELECT * from t_5 WHERE c2=1 FOR UPDATE;可以查询没阻塞。
4   
5   
6COMMIT;  


就是说如果索引的区分度很低时，一个查询覆盖
https://dev.mysql.com/doc/refman/8.0/en/where-optimization.html

第六个 SQL

SELECT * FROM t_1 AS of

这个SQL的坑刚刚是在公司MYSQL升级8.0时候遇到的，之前有个很老项目一直跑的好好的，升级8.0之后竟然出现了问题。
复用上个SQL的数据：

在我自己的测试库中运行：

SELECT VERSION();
SELECT * from t_31 as of ;

公司的测试库中执行

SELECT VERSION();
SELECT * from t_31 as of ;

WHY 查询官方文档

https://dev.mysql.com/doc/refman/8.0/en/keywords.html

 

这个 ”of“ 在之前都不是一个关键词，所以如果在写SQL的时候遇到别名时要想着是否是个别名，以及这个名词是不是未来可能会成为别名或者是关键词， 比如”of“这个别名在项目里写SQL的时候完全可以这么这 ”o_f“ 或者 ”of_“ 至少这样在mysql 以后升级少了很多不必要的麻烦。













 

未完，待续。

 

参考：
https://imysql.com/2020/07/13/why-the-not-nullable-column-with-is-null-condition-can-find-rows.shtml
https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html
https://time.geekbang.org/column/article/82865

DavinQi
关注
专栏目录
mysql document china_手把手教你看MySQL官方文档
weixin_39637256的博客
01-21 531
前言：在学习和使用MySQL的过程中，难免会遇到各种问题。不知道当你遇到相关问题时会怎么做，我在工作或写文章的过程中，遇到不懂或需要求证的问题时通常会去查阅官方文档。慢慢的，阅读文档也有了一些经验，本篇文章将手把手教你阅读MySQL官方文档，希望可以给到各位一点小收获。1.初识官方文档如果你还没有仔细的看过MySQL官方文档，可以跟着我一步步体验下哦。首先打开参考文档首页，这里以5.7版本为例，首...
gitlab-CI 使用mysql service详细解读配置文件
雪碧味西瓜的博客
09-27 1292
前言 在使用gitlab中持续集成（CI）的时候常常会需要先对API网关等进行测试，而在测试的过程中会需要使用本地数据库（如MYSQL、Redis等），笔者在一次部署自动化测试中，需要使用mysql，踩过无数坑之后终于成功部署，涉及到一些隐形的坑。 首先在gitlab-ci 使用mysql的官方文档中（文档地址）介绍了两种方法，一种是使用Docker executor，另一种是使用Shell executor。其中如果是使用Shell executor，需要自己在环境中安装mysql，运行mysql脚本等，
mysql官方文档怎么看_手把手教你看MySQL官方文档
weixin_39566593的博客
02-27 121
**前言：**在学习和使用MySQL的过程中，难免会遇到各种问题。不知道当你遇到相关问题时会怎么做，我在工作或写文章的过程中，遇到不懂或需要求证的问题时通常会去查阅官方文档。慢慢的，阅读文档也有了一些经验，本篇文章将手把手教你阅读MySQL官方文档，希望可以给到各位一点小收获。#### 1.初识官方文档如果你还没有仔细的看过MySQL官方文档，可以跟着我一步步体验下哦。首先打开参考文档首页，这里以...
使用mysql注意事项_使用MySQL的注意事项
weixin_29079643的博客
01-27 198
安装MySQL时候的注意事项？1、如果您是用MySQL+Apache，使用的又是FreeBSD网路操作系统的话，安装时候你应按注意到FreeBSD的版本问题，在FreeBSD的3.0以下版本来说，MySQLSource内含的MIT-pthread运行是正常的，但在这版本以上，你必须使用nativethreads,也就是加入一个with-named-thread-libs=-lc_r的选项。2、...
mysql使用注意事项
David
11-07 480
在使用mysql的过程中，有几点需要注意： 编码格式，mysql默认编码格式不是utf-8，需要在配置文件中修改mysql的字符编码格式； sqlmode，有时候使用中文或出现插入错误，可能是sqlmode不正确，需要修改配置文件中的sqlmode值； 数据库备份，在正式环境下一定不能忘记备份数据库，可通过系统的定时任务备份数据库，或者备份Data文件夹； binlog，binlog记录了数据库...
MySql使用的注意事项
weixin_45001033的博客
06-02 226
MySql介绍 MySql数据库是完全免费的,在市面中是最为常见的，是关系型数据库. 安装注意 设置字符集为 UTF-8 MySql默认数据端口是3306 安装完成后在 我的电脑-高级系统设置-高级-环境变量-Path添加Mysql安装路径(bin文件的位置) 在Dos窗口输入mysql -uroot –p在下一行输入数据库密码，如果显示Welcome就成功了 链接MySql数据库 通过Dos窗口使用命令 mysql -uroot –p 点回车，在输入密码即可登录到Mysql的控制台，通过命令控
activiti6.0官方英文文档，需要自取 免费！免费！免费！
08-22
- **注意事项**：升级过程中需要注意数据兼容性和表结构变化。 ##### 3.9 Job Executor - **介绍**：Job Executor是负责执行异步任务的关键组件。 - **配置**：可以通过`ProcessEngineConfiguration`类中的方法进行...
MySQL UDF evalcmd漏洞利用教程与注意事项
本资源摘要信息将详细介绍标题中的“mysql_udf:MySQL的udf eval/cmd shell”这一主题，并对描述中提及的MySQL UDF eval/cmd shell操作进行详细解读。 标题中提到的“mysql_udf”指的是与MySQL相关的用户定义函数，...
网易技术部详析MySQL中文文档：从开发到优化关键技巧
此外，文档还分享了一些实用的SQL技巧，如检索特定值的行、随机行的选择、使用GROUP BY和位组函数进行统计，以及数据库命名规范和外键使用注意事项。 在优化篇，第十一章详细指导了SQL优化策略，包括识别执行频率、...
MySQL5.1_DOC_CN中文帮助文档
05-09
同时，它还涵盖了从旧版本升级到MySQL5.1的步骤和注意事项。 2. **SQL语言基础**：MySQL支持标准的SQL语句，文档中将介绍如何创建、查询、更新和删除数据库中的数据，包括SELECT、INSERT、UPDATE、DELETE等基本操作...
mysql使用注意点_mysql使用注意事项
weixin_39756336的博客
01-28 152
1、如果在(a,b,c)三个字段上建立联合索引，那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。select count(distinct left(code, 5))/count(*) from device;说明：建索引时使用上述语句看看区分度，选择合适的长度，通常区分度达到80%左右就可以建索引了。2、建联合索引的时候，区分度最高的字段在最左边。3、如果建立了(a,b)...
mysql 操作注意事项
风雨雾花丶
05-30 440
1，mysql group的时候 只能是用的原来的数据库的命名 来分类 不能用别名 2，mysql 统计 count(case when 字段 is null) as 字段 统计 3，mysql 在链接了表的时候 on 的条件后面可以加上 and 来筛选 因为where是相当于在一个大的表 再在里面进行筛选，不符合where条件的直接过滤掉，所以，不管是left还是inner链接表 加上whe...
MySQL中日常操作及注意事项
cqwei1987的博客
07-14 224
本文介绍MySQL的日常操作，包括创建用户、修改口令、创建修改及删除数据库、表等。
如何反馈 MySQL 官方在线文档错误？
最新发布
影三人
12-07 310
MySQL 官方文档出错，如何反馈？
【MySQL】MySQL日期函数官方文档及笔记
chenghan_yang的博客
01-29 1067
前言 MySQL 5.7日期函数官方文档 遇到了日期边界的一些问题，这里统一记录 DATEDIFF(expr1,expr2) DATEDIFF(expr1,expr2) DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of th
经验总结：java套接字网络编程
07-15 157
kafka面试基础[17] 1.Kafka的用途有哪些？使用场景如何？ 2.Kafka中的ISR、AR又代表什么？ISR的伸缩又指什么 3.Kafka中的HW、LEO、LSO、LW等分别代表什么？ 4.Kafka中是怎么体现消息顺序性的？ 5.Kafka中的分区器、序列化器、拦截器是否了解？它们之间的处理顺序是什么？ 6.Kafka生产者客户端的整体结构是什么样子的？ 7.Kafka生产者客户端中使用了几个线程来处理？分别是什么？ 8.Kafka的旧版Scala的消费者客户端的设计有什么缺陷？ 9.“消费组
mysql官方文档 优化概述
weixin_43981133的博客
10-17 357
8.1优化概述 数据库性能取决于数据库级别的几个因素，例如表，查询和配置设置。这些软件结构导致在硬件级别执行CPU和I / O操作，您必须将这些操作最小化并使其尽可能高效。在研究数据库性能时，首先要学习软件方面的高级规则和准则，并使用时钟时间来衡量性能。成为专家后，您将了解有关内部情况的更多信息，并开始测量诸如CPU周期和I / O操作之类的东西。 典型的用户旨在从其现有的软件和硬件配置中获得最佳的数据库性能。高级用户会寻找机会改进MySQL软件本身，或者开发自己的存储引擎和硬件设备来扩展MySQL生态系统
MYSQL ERROR CODE
weixin_34235105的博客
03-29 679
mysql error code(备忘) 转1005：创建表失败 1006：创建数据库失败 1007：数据库已存在，创建数据库失败 1008：数据库不存在，删除数据库失败 1009：不能删除数据库文件导致删除数据库失败 1010：不能删除数据目录导致删除数据库失败 1011：删除数据库文件失败 1012：不能读取系统表中的记录 1020：记录已被其他用户修改...
从官方文档中探索MySQL分页的几种方式及分页优化
kanlon的博客
02-02 365
参考： https://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html https://dev.mysql.com/doc/refman/5.6/en/select.html https://dev.mysql.com/doc/refman/5.6/en/select.html https://www.liaoxuefeng.com/wiki/1177760294764384/1217864791925600 https://segmentfa
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值