当前位置 : 首页 » 文章分类 :  开发  »  MySQL-使用笔记

MySQL-使用笔记

mysql 日常使用笔记与备忘

MySQL 5.6 Reference Manual (打开右上角可切换文档版本)
https://dev.mysql.com/doc/refman/5.6/en/


设计与实践

存储树形结构

一般比较普遍的就是四种方法:(具体见 SQL Anti-patterns这本书)

  • Adjacency List:邻接表,每一条记录存parent_id
  • Path Enumerations:路径枚举,每一条记录存整个tree path经过的node枚举
  • Nested Sets:每一条记录存 nleft 和 nright
  • Closure Table:闭包表,维护一个表,所有的tree path作为记录进行保存。

怎样在 MySQL 表中存储树形结构数据? - 卢钧轶的回答 - 知乎
https://www.zhihu.com/question/20417447/answer/15078011

树状结构的数据表如何设计?
https://segmentfault.com/q/1010000000126370

干货:在关系型数据库中优雅地存储树形结构
https://www.jianshu.com/p/951b742fd137

用大间隔int来表示状态码

比如一个实体类表5种有序的状态,可以用status表示

`status` TINYINT NOT NULL DEFAULT 0
COMMENT '结点状态,0-未知,1-阶段1,2-阶段2,3-阶段3,4-阶段4,5-阶段5',

假如这是一个订单的状态的话,这样设计有一个潜在的风险,就是如果之后需求发生变化,需要再插入中间状态,就需要刷数据了,因为间隔为1的int中间再无法再插入值了。
所以建议设计为

`status` TINYINT NOT NULL DEFAULT 0
COMMENT '结点状态,0-未知,10-阶段1,20-阶段2,30-阶段3,40-阶段4,50-阶段5',

用间隔为10的int来表示状态码,哪天需求变了要加中间状态就很方便,比如可以在10和20直接加一个15状态码。


给一个很大的线上表加字段并刷数据

给一个很大的线上表加字段,并且加字段后要设置初始化数据,要考虑哪些因素?怎么做?

考虑:
1、加字段是否会锁表?
2、加字段后设置初始值是否需要更新表的update_time?
比如 加字段 a,根据某些条件 set a 的初始值后,表中所有行的update_time都会更新为sql脚本执行时间,如果业务逻辑中有根据 update_time 做判断的,需要仔细考虑:
(1)比如我们系统中有个根据 update_time 拉取最近 24 小时内有更新的数据的接口,如果刷数据后全量数据的 update_time 都更新为当前时间,会导致这个接口能遍历出全量数据,影响性能。
(2)比如有的表的查询逻辑需要按 update_time 排序后取最新的一个,如果刷数据后所有记录 update_time 都变为一样的,就会影响业务逻辑。
为了避免这种情况,刷数据时可以保留原有的 update_time ,如下:

update table t
set t.a = xx, t.update_time = t.update_time
where xxx

一种方案:
1、先拷贝出镜像表
2、服务中双写
3、改名替换

如果用这个方案,需要注意什么?
1、双写必须在一个事务中
2、两个表里的自增id是无法对齐的,需要处理


工具

Yearning MYSQL SQL语句审核平台

https://guide.yearning.io/


MySQL数据类型

Mysql字符集和字符序

再见乱码:5分钟读懂MySQL字符集设置
https://www.cnblogs.com/chyingp/p/mysql-character-set-collation.html

在数据的存储上,MySQL提供了不同的字符集支持。而在数据的对比操作上,则提供了不同的字符序支持,字符序也就是校验规则。
字符集 (character set) 定义了字符以及字符的编码
字符序/校对 (collation) 定义了字符的比较规则

MySQL支持多种字符集 与 字符序。
1、一个字符集对应至少一种字符序(一般是1对多)。
2、两个不同的字符集不能有相同的字符序。
3、每个字符集都有默认的字符序。例如,utf8 默认的字符序为 utf8_general_ci, utf8mb4 默认的字符序为 utf8mb4_general_ci,都是大小写不敏感的。
4、字符序命名约定:它们以其相关的字符集名开始,通常包括一个语言名,并且以 _ci(大小写不敏感)、_cs(大小写敏感)或_bin(二进制)结束。
*_bin: binary case sensitive collation,也就是说是区分大小写的
*_cs: case sensitive collation,区分大小写
*_ci: case insensitive collation,不区分大小写

mysql大小写敏感

默认情况下 mysql 字符类型 varchar/char/text 是不区分大小写的,无论编码格式是 utf8 还是 utf8mb4 默认都不区分大小写,也就是默认都是大小写不敏感的。
因为默认情况下, utf8 的字符序是 utf8_general_ci, utf8mb4 的字符序是 utf8mb4_general_ci,最后的 ci 表示 case insensitive 即 大小写不敏感。

方法一,select 查询时加 binary,不改动表结构

select * from table where binary str = 'aaa';
select * from table where BINARY str = 'aAA';

方法二,建表时加以标识
1、指定字段为 binary

create table some_table(
  str char(20) binary
);

2、指定某个字段的字符序为大小写敏感的

CREATE TABLE `T` (
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB;

3、指定整个表的字符序

CREATE TABLE `T2` (
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

方法三,对于已建好的表,修改表字段属性为大小写敏感

ALTER TABLE table_name MODIFY COLUMN code varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin not null comment '大小写敏感的code';

utf8和utf8mb4

MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就够了。

那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢?
原来mysql支持的 utf8 编码最大字符长度为 3 字节,如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff,也就是 Unicode 中的基本多文种平面(BMP)。也就是说,任何不在基本多文本平面的 Unicode字符,都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码,常见于 ios 和 android 手机上),和很多不常用的汉字,以及任何新增的 Unicode 字符等等。

查看mysql支持的字符集和字符序

show character set 查看可用字符集
13.7.5.4 SHOW CHARACTER SET Syntax
https://dev.mysql.com/doc/refman/5.6/en/show-character-set.html

语法:
SHOW CHARACTER SET [LIKE 'pattern' | WHERE expr]

例如:

MariaDB > show character set like '%utf%';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf8    | UTF-8 Unicode    | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_general_ci |      4 |
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
+---------+------------------+--------------------+--------+

或者从 information_schema.CHARACTER_SETS 表中查

mysql> use information_schema;
mysql> select * from CHARACTER_SETS;

show collation 查看可用字符序
字符序的命名,以其对应的字符集作为前缀,如下所示。比如字符序 utf8_general_ci,标明它是字符集utf8的字符序。

13.7.5.5 SHOW COLLATION Statement
https://dev.mysql.com/doc/refman/5.6/en/show-collation.html
语法
SHOW COLLATION [LIKE 'pattern' | WHERE expr]

MariaDB > show COLLATION LIKE '%utf8mb4%';
+------------------------------+---------+-----+---------+----------+---------+
| Collation                    | Charset | Id  | Default | Compiled | Sortlen |
+------------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci           | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin                  | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci           | utf8mb4 | 224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci         | utf8mb4 | 225 |         | Yes      |       8 |
| utf8mb4_latvian_ci           | utf8mb4 | 226 |         | Yes      |       8 |
| utf8mb4_romanian_ci          | utf8mb4 | 227 |         | Yes      |       8 |
...

或者从 information_schema.COLLATIONS 表中查。

mysql> USE information_schema;
mysql> SELECT * FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8";

variables character_set_ 当前字符集

SHOW VARIABLES LIKE 'character_set_%';
没设置的情况下,默认字符集如下:

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

character_set_client:客户端请求数据的字符集
character_set_connection:客户机/服务器连接的字符集
character_set_database:默认数据库的字符集,无论默认数据库如何改变,都是这个字符集;如果没有默认数据库,那就使用 character_set_server指定的字符集,这个变量建议由系统自己管理,不要人为定义。
character_set_filesystem:把os上文件名转化成此字符集,即把 character_set_client转换character_set_filesystem, 默认binary是不做任何转换的
character_set_results:结果集,返回给客户端的字符集
character_set_server:数据库服务器的默认字符集
character_set_system:系统字符集,这个值总是utf8,不需要设置。这个字符集用于数据库对象(如表和列)的名字,也用于存储在目录表中的函数的名字。

variables collation_ 当前字符序

show variables like '%collation%';
没设置时默认字符序为

MariaDB > show variables like '%collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.016 sec)

MySQL中的字符集转换过程

  1. MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;
  2. 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下:
  • 使用每个数据字段的CHARACTER SET设定值;
  • 若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准);
  • 若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;
  • 若上述值不存在,则使用character_set_server设定值。
  1. 将操作结果从内部操作字符集转换为character_set_results。

创建表时指定字符集

CREATE TABLE `article` (
`id`           BIGINT(20)      NOT NULL AUTO_INCREMENT  COMMENT 'id,自增主键',
`pathname`     VARCHAR(1024)   CHARACTER SET utf8    NOT NULL DEFAULT '' COMMENT '页面pathname',
`title`        VARCHAR(2048)   CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '文章title或页面title',
`create_time`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
-- mysql最大索引 768 个字节, utf8 占 3 个字节,768/3=256
UNIQUE (`pathname`(255))
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4;

指定了表的默认字符集为 utf8mb4, 同时也指定了 pathname 字段使用 utf8 字符集,不冲突
其中 CHARSETCHARACTER SET 的同义词


char/varchar/text

1、char(n) 和 varchar(n) 中括号中 n代表字符的个数,并不代表字节个数,所以当使用了中文的时候(UTF8)意味着可以插入m个中文,但是实际会占用m*3个字节。
2、同时 char 和 varchar 最大的区别就在于 char 不管实际 value 都会占用 n 个字符的空间,而 varchar 只会占用实际字符应该占用的空间+1,并且实际空间+1<=n。
3、超过 char 和 varchar 的 n 设置后,字符串会被截断。
4、char 的上限为 255 字节,varchar 的上限 65535 字节,text 的上限为65535。
5、char 在存储的时候会截断尾部的空格,varchar 和 text 不会。
6、varchar 会使用 1-3 个字节来存储长度,text 不会。

总体来说:
1、char,存定长,速度快,存在空间浪费的可能,会处理尾部空格,上限 255。
2、varchar,存变长,速度慢,不存在空间浪费,不处理尾部空格,上限 65535,但是有存储长度实际65532最大可用。
3、text,存变长大数据,速度慢,不存在空间浪费,不处理尾部空格,上限 65535,会用额外空间存放数据长度,顾可以全部使用 65535。

当 varchar(n) 后面的 n 非常大的时候我们是使用 varchar 好,还是text好呢?
从官方文档中我们可以得知当 varchar 大于某些数值的时候,其会自动转换为 text,大概规则如下:
大于varchar(255)变为 tinytext
大于varchar(500)变为 text
大于varchar(20000)变为 mediumtext
所以对于过大的内容使用varchar和text没有太多区别。


TEXT不支持默认值

mysql text类型没有默认值,如果该字段没有值,则该字段是空,即is null

根据 mysql5.0以上版本 strict mode (STRICT_TRANS_TABLES) 的限制:
不支持对not null字段插入null值
不支持对自增长字段插入’’值,可插入null值
不支持 text 字段有默认值


int(5)括号内的长度是显示长度

int(M), M指示最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关

“整型”的长度实际上可以理解为”显示长度”, 如果该字段开启 “Zerofill/补零”就能很明显地知道它的作用.

这个M=5我们可以简单的理解成为, 我们建立这个长度是为了告诉MYSQL数据库我们这个字段的存储的数据的宽度为5位数, 当然如果你不是5位数(只要在该类型的存储范围之内)MYSQL也能正常存储

MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如,INT(4))。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。
显示宽度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示。
也就是说,int后面括号内的长度并不影响数据的存储精度,长度只和显示有关

“高性能MySQL” 书中在”4.1 选择优化的数据类型”中提到:
MySQL 可以为整数类型指定宽度, 例如 INT(11), 对大多数应用这是没有意义的: 它不会限制值的合法范围, 只是规定了 MySQL 的一些交互工具(例如 MySQL 命令行客户端)用来显示字符的个数. 对于存储和计算来说, INT(1) 和 INT(20) 是相同的

MySQL 5.7 手册 “12.2.5 Numeric Type Attributes”:
MySQL 支持用括号包含的数字限定整型的显示长度. 比如 INT(4) 限定了整型的显示长度为 4 个字符, 对于小于 4 个字符的数字, 有些数据库软件会用”空格”来补齐小于 4 个位数的数字.

这个显示长度并不会限制该字段的数字存储范围, 同时, 也不会阻止大于指定显示长度的数字写入该字段. 比如, SMALLINT(3) 的字段和 SMALLINT 的数字存储范围都是 -32768 to 32767, 如果存储的数字超过 3 个位数仍然是允许被存入 SMALLINT(3) 字段, 而且以其本来的位数显示.

如果配合 ZEROFILL 属性, 将用 0 来补齐. 比如 INT(4) ZEROFILL 字段, 数字 5 会被存储为 0005.

MySQL 整型长度的含义
https://www.jianshu.com/p/61293b416335

tinyint/int/bigint等数据长度

Type Storage (Bytes) Minimum Value Signed Minimum Value Unsigned Maximum Value Signed Maximum Value Unsigned
TINYINT 1 -128 0 127 255
SMALLINT 2 -32768 0 32767 65535
MEDIUMINT 3 -8388608 0 8388607 16777215
INT 4 -2147483648 0 2147483647 4294967295
BIGINT 8 $-2^{63}$ 0 $2^{63}-1$ $2^{64}-1$

11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
https://dev.mysql.com/doc/refman/8.0/en/integer-types.html


default null与default ‘’

《高性能mysql》中是这么说的:

尽量避免NULL

通常情况下最好指定列为 NOT NULL,除非真的需要存储 NULL 值;mysql表定义时如果没有指定列为NOT NULL,默认都是允许NULL的;

如果查询中包含可为NULL的列,对mysql来说更难优化。因为可为NULL的列,使得索引、索引统计、值比较,都更复杂;

可为NULL的列会使用更多的存储空间,在MYSQL里也需要特殊处理。

当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引;

通常,把可为NULL的列改为NOT NULL带来的性能提升比较小,所以调优时没有必要首先修改这种情况,除非确定这会导致问题;

但是如果计划在列上建索引,就应该尽量避免设计成可为NULL的列。当然也有例外,比如InnoDB使用单独的bit存储NULL的值,对稀疏数据有很好的空间效率。这一点不适用于MyISAM。
(稀疏数据:是指很多值都是NULL,少数值是非NULL)

1:空值(‘’)是不占用空间的
2: MySQL中的NULL其实是占用空间的。官方文档说明:

NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.

所以 mysql设计表时 建议不要用default NULL
string类型的可以default ‘’ int类型的可以default 0

数据库表某字段设置default为Null好,还是“”好,或者是Empty String好呢?手机端我只想返回空
https://segmentfault.com/q/1010000006758650/a-1020000006759600

值得收藏:一份非常完整的MySQL规范
https://mp.weixin.qq.com/s/QAzb6yCS9NqOY066ZBcuSg

为什么建议MySQL列属性尽量用 NOT NULL

1、NOT IN!= 等负向条件查询在有 NULL 值的情况下返回非空行的结果集。
比如 t.name 列可以是 null
id, name
1 张三
2 null
3 李四
select * from t where name !='李四' 只会返回 id=1 的数据,也就是 name 列不是 null 的

2、使用 concat 函数拼接时,首先要对各个字段进行非 NULL 判断,否则只要任何一个字段为空都会造成拼接的结果为 NULL

3、当用count函数进行统计时,NULL 列不会计入统计

5、NULL 列需要更多的存储空间,一般需要一个额外的字节作为判断是否为 NULL 的标志位。
同一类型,同样长度的key,如果其中一个是可为null的,则会比 not null 列的key长度多1,因为
key_len 的长度一般跟这三个因素有关,分别是数据类型,字符编码,是否为 NULL。

面试官问,为什么建议MySQL列属性尽量用 NOT NULL ?
https://mp.weixin.qq.com/s/PIKUol_7AR1CU4FehJAJLw


AUTO_INCREMENT 自增值设置与修改

创建表时设置自增主键:

CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL auto_increment,
  `order_date` datetime NOT NULL,
  `cust_id` int(11) NOT NULL,
  PRIMARY KEY  (`order_num`),
  KEY `fk_orders_customers` (`cust_id`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;

创建表格后添加:

alter table users AUTO_INCREMENT=10000;

而且该语句也适用于修改现有表的id上, 比如大批量删除数据后,想id从654321退回123456开始

alter table users AUTO_INCREMENT=123456;

TIMESTAMP的默认值和自动更新

timestamp字段有三个属性:
a) 是否允许NULL。默认为not null。
b) 默认值。
可以设定为default CURRENT_TIMESTAMP 或default 某个常量。若定义时缺省,对于第一个出现的timestamp字段,默认为CURRENT_TIMESTAMP,对于其他timestamp字段,默认为 ‘0000-00-00 00:00:00’。 Default可以指定为null,前提是本字段允许null。比如声明 field2 timestamp null default null。
c) On update CURRENT_TIMESTAMP。
是否在更新其他字段时自动将该timestamp字段修改为当前时间戳。对于第一个出现的timestamp字段,默认为On update CURRENT_TIMESTAMP,对于其他timestamp字段,没有此行为。

1、 如果定义时 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句都有,列值为默认使用当前的时间戳,并且自动更新。
2、 如果不使用DEFAULT或ON UPDATE子句,那么它等同于DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。
3、 如果只有DEFAULT CURRENT_TIMESTAMP子句,而没有ON UPDATE子句,列值默认为当前时间戳但不自动更新。
4、 如果没用DEFAULT子句,但有ON UPDATE CURRENT_TIMESTAMP子句,列默认为0并自动更新。
5、 如果有一个常量值DEFAULT,该列会有一个默认值,而且不会自动初始化为当前时间戳。如果该列还有一个ON UPDATE CURRENT_TIMESTAMP子句,这个时间戳会自动更新,否则该列有一个默认的常量但不会自动更新。
换句话说,你可以使用当前的时间戳去初始化值和自动更新,或者是其中之一,也可以都不是。(比如,你在定义的时候可以指定自动更新,但并不初始化。)下面的字段定义说明了这些情况:

自动初始化和更新:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

只自动初始化:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

只自动更新
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

只是给一个常量(注:0000-00-00 00:00:00)
ts TIMESTAMP DEFAULT 0

MySQL的timestamp类型自动更新问题
http://www.yayu.org/look.php?id=144

11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME
https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html


enum 枚举类型

枚举类型对应的数字从1开始

ENUM的字段值不区分大小写

创建带有enum类型字段的表

CREATE TABLE `enum_tests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` enum('pending','success','closed') COLLATE utf8mb4_unicode_ci NOT NULL default 'success',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

修改enum字段的枚举值

ALTER TABLE inviter_relationship_transaction
CHANGE mode mode ENUM('auto', 'manual', 'task') default 'auto' null;

关于 MySQL enum 类型的一些测试
https://laravel-china.org/articles/6927/some-tests-on-the-mysql-enum-type


DECIMAL 小数

定义数据类型为DECIMAL的列
column_name DECIMAL(P,D);
P 是表示有效数字数的精度。 P范围为1〜65。
D 是表示小数点后的位数。 D的范围是0~30。
MySQL要求 D 小于或等于(<=)P。
DECIMAL(P,D) 表示列可以存储 D 位小数的 P 位数。十进制列的实际范围取决于精度和刻度。

column_name DECIMAL(P); 相当于 column_name DECIMAL(P,0); 不包含小数部分或小数点
column_name DECIMAL; 如果 P 也忽略,相当于 column_name DECIMAL(10); P 的默认值为 10

示例
amount DECIMAL(6,2); amount 列最多可以存储 6 位数字,小数位数为 2 位; 因此,amount 列的范围是从 -9999.99 到 9999.99。


MySQL系统变量

查看mysql版本号

未连接到MySQL服务器mysql -v

没有连接到MySQL服务器,就想查看MySQL的版本。打开cmd,切换至mysql的bin目录,运行下面的命令即可:

e:\mysql\bin>mysql -V
mysql  Ver 14.14 Distrib 5.6.32, for Win32 (AMD64)

版本为 5.6.32

或者:

e:\mysql\bin>mysql -v

这个命令可以查看到更为详细的信息,因为它会用账号 ODBC,连接上MySQL服务器,默认连接到localhost上的3306端口。

select version();

已连接到mysql服务器后

MariaDB [uds]> select version();
+-----------------+
| version()       |
+-----------------+
| 10.1.34-MariaDB |
+-----------------+
1 row in set (0.023 sec)

status;\s

mysql 命令行执行命令 status;\s

MariaDB [uds]> status;
--------------
mysql  Ver 15.1 Distrib 10.3.7-MariaDB, for osx10.13 (x86_64) using readline 5.1

Connection id:        4487583
Current database:    uds
Current user:        root@10.111.159.88
SSL:            Not in use
Current pager:        less
Using outfile:        ''
Using delimiter:    ;
Server:            MariaDB
Server version:        10.1.34-MariaDB MariaDB Server
Protocol version:    10
Connection:        t-awsbj-uds-01.clap5vvkrarj.rds.cn-north-1.amazonaws.com.cn via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        3306
Uptime:            30 days 15 hours 35 min 44 sec

Threads: 797  Questions: 934282188  Slow queries: 12453  Opens: 743201  Flush tables: 25  Open tables: 200  Queries per second avg: 352.806
--------------

sql_mode

5.1.10 Server SQL Modes
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

查看当前sql_mode

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
SELECT @@sql_mode;

设置sql_mode

SET GLOBAL sql_mode = ‘modes’;
SET SESSION sql_mode = ‘modes’;

my.cnf中配置sql-mode

[mysqld]
#set the SQL mode to strict
#sql-mode="modes..."
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

常用sql_mode

ONLY_FULL_GROUP_BY

对于GROUP BY聚合操作,如果在SELECT中的列、HAVING或者ORDER BY子句的列,没有在GROUP BY中出现,那么这个SQL是不合法的。是可以理解的,因为不在 group by 的列查出来展示会有矛盾。
在5.7中默认启用,所以在实施5.6升级到5.7的过程需要注意

ANSI_QUOTES

启用 ANSI_QUOTES 后,不能用双引号来引用字符串,因为它被解释为识别符,作用与 ` 一样。
设置它以后,update t set f1=”” …,会报 Unknown column ‘’ in ‘field list 这样的语法错误。

STRICT_TRANS_TABLES

设置它,表示启用严格模式。
注意 STRICT_TRANS_TABLES 不是几种策略的组合,单独指 INSERT、UPDATE出现少值或无效值该如何处理:
1.前面提到的把 ‘’ 传给int,严格模式下非法,若启用非严格模式则变成0,产生一个warning
2.Out Of Range,变成插入最大边界值
3.A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition

NO_AUTO_CREATE_USER

字面意思不自动创建用户。在给MySQL用户授权时,我们习惯使用 GRANT … ON … TO dbuser 顺道一起创建用户。设置该选项后就与oracle操作类似,授权之前必须先建立用户。5.7.7开始也默认了。

MySQL sql_mode 说明(及处理一起 sql_mode 引发的问题)
http://seanlook.com/2016/04/22/mysql-sql-mode-troubleshooting/


事务隔离级别

查看当前会话隔离级别

select @@tx_isolation;

查看系统当前隔离级别

select @@global.tx_isolation;

设置当前会话隔离级别

set session transaction isolatin level repeatable read;

设置系统当前隔离级别

set global transaction isolation level repeatable read;


show status 查看所有status

13.7.5.36 SHOW STATUS Syntax
https://dev.mysql.com/doc/refman/5.6/en/show-status.html

SHOW [GLOBAL | SESSION] STATUS
    [LIKE 'pattern' | WHERE expr]

例如:
show status; 查看所有变量

show status like '%变量名%'; 查看某个具体变量值

常用变量说明:
Aborted_clients 由于客户没有正确关闭连接已经死掉,已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时,已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载)
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。
Uptime 服务器工作了多少秒。

Max_used_connections 查看最大并发连接数

即mysql运行过程中曾经达到的最大连接数
show global status like 'Max_used_connections';

https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Max_used_connections

Threads_connected 查看当前实时连接数

show global status like 'Threads%';
Threads_connected 当前的实时连接数,
Threads_created 表示创建过的线程数
Threads_running 这个数值指的是激活的连接数,这个数值一般远低于connected数值

MariaDB [uds]> show global status like 'Threads%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Threads_cached    | 0        |
| Threads_connected | 1369     |
| Threads_created   | 15899277 |
| Threads_running   | 1        |
+-------------------+----------+

https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Threads_connected


Server System Variables 服务端系统变量

5.1.7 Server System Variables
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html

show variables 查看所有variable

show variables; 查看所有变量

show variables like '%变量名%' 查看某个具体变量值

autocommit 自动提交是否打开

SHOW VARIABLES LIKE 'autocommit';

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

foreign_key_checks 外键检查

是否进行外键检查,默认是打开的,即1
作用域:Global, Session

当前Session设置:
SET FOREIGN_KEY_CHECKS=0;
全局设置:
SET GLOBAL FOREIGN_KEY_CHECKS=0;

比如想强行删除被引用的外建行的话,可以暂时关闭外键检查:

SET FOREIGN_KEY_CHECKS = 0;
delete from user where user_id=81681419;
SET FOREIGN_KEY_CHECKS = 1;

foreign_key_checks
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_foreign_key_checks

Does MySQL foreign_key_checks affect the entire database?
https://stackoverflow.com/questions/8538636/does-mysql-foreign-key-checks-affect-the-entire-database


max_connections 查看my.cnf中配置的最大连接数

show variables like 'max_connections';
可以在/etc/my.cnf里面设置数据库的最大连接数

[mysqld]
max_connections = 1000

默认值151,最小值1,最大值100000

mariadb - max_connections
https://mariadb.com/kb/en/library/server-system-variables/#max_connections

mysql - max_connections
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_connections


wait_timeout 非交互连接超时时间

参数含义:服务器关闭非交互连接之前等待活动的秒数。
在线程启动时,根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect() 的连接选项CLIENT_INTERACTIVE定义)。
参数默认值:28800秒(8小时)
show global variables like 'wait_timeout';
show variables like 'wait_timeout';
空闲连接等待时间(秒)
Mysql服务器默认的“wait_timeout”是8小时(28800 秒),也就是说一个connection空闲超过8个小时,Mysql将自动断开该connection。
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout

interactive_timeout

参数含义:服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。
参数默认值:28800秒(8小时)
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_interactive_timeout


max_allowed_packet(4MB)

server 可接收的最大数据包大小,单位字节(B, bytes),大的查询或插入sql可能因为超过这个值二报错。
默认值
Default Value (>= 5.6.6) 4194304 B 即 4MB
Default Value (<= 5.6.5) 1048576 B
允许的最大值: 1073741824 B, 即 1GB
允许的最小值: 1024 B, 即 1KB

mysql 的数据包缓冲区(packet buffer) 初始值是 net_buffer_length 字节,可按需自动增长为 max_allowed_packet 字节

查看
show VARIABLES like '%max_allowed_packet%';

PacketTooBigException

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (13631257 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.
; SQL []; Packet for query is too large (13631257 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.; nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (13631257 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_allowed_packet


innodb_lock_wait_timeout(50秒)

show VARIABLES like '%innodb_lock_wait_timeout%';
该参数控制 Innodb 行锁等待的超时时间,单位为秒,该实例该参数的默认值为 50(秒)
默认50秒, 我们生产系统设置成了 120 秒

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout


mysql时区

time_zone 当前时区

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
默认值为 SYSTEM,表示使用系统时区变量 system_time_zone 的值。

mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

system_time_zone 系统时区

服务器的系统时区,mysql服务器启动时会读取所在服务器的系统时区。
建议:在启动mysql服务器前,将系统时区设为需要的时区,比如 Asia/Shanghai,避免单独设置mysql时区,其他地方比如jvm中和mysql时区不一致出问题。

设置mysql系统时区

方法一:设置系统变量

mysql> set global time_zone = '+8:00';  ##修改mysql全局时区为北京时间,即我们所在的东8区
mysql> set time_zone = '+8:00';  ##修改当前会话时区
mysql> flush privileges;  #立即生效

我这样设置之后好像没生效

方法二:通过修改my.cnf配置文件来修改时区

# vim /etc/my.cnf  ##在[mysqld]区域中加上
default-time_zone = '+8:00'

# sudo service mysqld restart ##重启mysql使新时区生效

注意一定要在 [mysqld] 之下加 ,否则会出现 unknown variable ‘default-time-zone=+8:00’


select now()查看mysql系统时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-03-10 12:55:51 |
+---------------------+
1 row in set (0.00 sec)

或者用select curtime()只能查出时间,没有日期

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 12:58:13  |
+-----------+
1 row in set (0.00 sec)

datadir 物理文件存放位置

show global variables like "%datadir%";

bind_address 服务器绑定ip

查看mysql server绑定ip
show variables like 'bind_address';
例如:

mysql> show variables like 'bind_address';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| bind_address  | *     |
+---------------+-------+
1 row in set (0.21 sec)

bind-address是MYSQL用来监听某个单独的TCP/IP连接,只能绑定一个IP地址,被绑定的IP地址可以映射多个网络接口.
可以是IPv4,IPv6或是主机名,但需要在MYSQL启动的时候指定(主机名在服务启动的时候解析成IP地址进行绑定).
默认是*,表示接收所有的IPv4 或 IPv6 连接请求

在/etc/my.cnf中配置:

  • 接收所有的IPv4 或 IPv6 连接请求

0.0.0.0 接受所有的IPv4地址
:: 接受所有的IPv4 或 IPv6 地址
IPv4-mapped 接受所有的IPv4地址或IPv4邦定格式的地址(例 ::ffff:127.0.0.1)
IPv4(IPv6) 只接受对应的IPv4(IPv6)地址


show plugins 查看所有插件

13.7.5.26 SHOW PLUGINS Syntax
https://dev.mysql.com/doc/refman/5.6/en/show-plugins.html

SHOW PLUGINS 查看支持的插件,
或者从 INFORMATION_SCHEMA.PLUGINS 表中查看支持的插件

MariaDB [uds]> show plugins;
+-------------------------------+----------+--------------------+---------+---------+
| Name                          | Status   | Type               | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| wsrep                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MyISAM                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CLIENT_STATISTICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INDEX_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| TABLE_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| USER_STATISTICS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| SEQUENCE                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| Aria                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                       | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL    | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+-------------------------------+----------+--------------------+---------+---------+

information_schema

在MySQL 5.0以后的版本中,INFORMATION_SCHEMA中的表存储了系统相关信息。

information_schema库保存了MySQL服务器所有数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。再简单点,这台MySQL服务器上,到底有哪些数据库、各个数据库有哪些表,每张表的字段类型是什么,各个数据库要什么权限才能访问,等等信息都保存在information_schema表里面。

SCHEMATA表:提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

TABLES表:提供了关于数据库中的表的信息(包括视图)。详细表述了某个表属于哪个schema,表类型,表引擎,创建时间等信息。是show tables from schemaname的结果取之此表。

processlist

processlist表字段含义

id: 就是这个线程的唯一标识,当我们发现这个线程有问题的时候,可以通过 kill 命令,加上这个Id值将这个线程杀掉。前面我们说了show processlist 显示的信息时来自information_schema.processlist 表,所以这个Id就是这个表的主键。
user: 就是指启动这个线程的用户。
host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候,我们可以定位到是哪个客户端的哪个进程发送的请求。
db: 当前执行的命令是在哪一个数据库上。如果没有指定数据库,则该值为 NULL 。
command: 是指此刻该线程正在执行的命令。这个很复杂,下面单独解释
time: 表示该线程处于当前状态的时间。
state: 线程的状态,和 Command 对应,下面单独解释。
info: 一般记录的是线程执行的语句。默认只显示前100个字符,也就是你看到的语句可能是截断了的,要看全部信息,需要使用 show full processlist。

processlist表command字段取值

Binlog Dump: 主节点正在将二进制日志 ,同步到从节点
Change User: 正在执行一个 change-user 的操作
Close Stmt: 正在关闭一个Prepared Statement 对象
Connect: 一个从节点连上了主节点
Connect Out: 一个从节点正在连主节点
Create DB: 正在执行一个create-database 的操作
Daemon: 服务器内部线程,而不是来自客户端的链接
Debug: 线程正在生成调试信息
Delayed Insert: 该线程是一个延迟插入的处理程序
Drop DB: 正在执行一个 drop-database 的操作
Execute: 正在执行一个 Prepared Statement
Fetch: 正在从Prepared Statement 中获取执行结果
Field List: 正在获取表的列信息
Init DB: 该线程正在选取一个默认的数据库
Kill : 正在执行 kill 语句,杀死指定线程
Long Data: 正在从Prepared Statement 中检索 long data
Ping: 正在处理 server-ping 的请求
Prepare: 该线程正在准备一个 Prepared Statement
ProcessList: 该线程正在生成服务器线程相关信息
Query: 该线程正在执行一个语句
Quit: 该线程正在退出
Refresh:该线程正在刷表,日志或缓存;或者在重置状态变量,或者在复制服务器信息
Register Slave: 正在注册从节点
Reset Stmt: 正在重置 prepared statement
Set Option: 正在设置或重置客户端的 statement-execution 选项
Shutdown: 正在关闭服务器
Sleep: 正在等待客户端向它发送执行语句
Statistics: 该线程正在生成 server-status 信息
Table Dump: 正在发送表的内容到从服务器
Time: Unused

我们经常看到好多处于 sleep 状态的线程,是因为这是后台服务连接池建立的链接在等待服务给他发送sql来执行。如果按ip group分组,每个ip的count个数就是每个后台服务器的连接池大小。

mysql: show processlist 详解
https://zhuanlan.zhihu.com/p/30743094

查看各ip连接数

从 information_schema 库的 processlist 表中查

select SUBSTRING_INDEX(host,':',1) as ip , count(*)
from information_schema.processlist
group by ip;

通过直接执行mysql命令也可以实现:

mysql -u root -h127.0.0.1 -e"show processlist\G;"| egrep "Host\:" | awk -F: '{ print $2 }'| sort | uniq -c
mysql -u root -h127.0.0.1 --skip-column-names -e"show processlist;"|awk '{print $3}'|awk -F":" '{print $1}'|sort|uniq –c

show processlist 查正在执行的连接(线程)

show processlist;
如果是root帐号,你能看到所有用户的当前连接。如果是其它普通帐号,只能看到自己占用的连接。
show processlist;只列出前100条,如果想全列出请使用 show full processlist;

show processlist 显示的信息都是来自MySQL系统库 information_schema 中的 processlist 表。所以使用下面的查询语句可以获得相同的结果:
select * from information_schema.processlist;


columns

根据表名查有哪些列,及各列的数据类型,以及列是否有索引等。

select *
from information_schema.columns
where table_name='service_conf'

从columns表中查表有哪些索引列

select column_name, column_type, column_key
from information_schema.columns
where table_schema='数据库名'
and TABLE_NAME='表名';

tables

table_schema :表所属的数据库名
table_name: 表名
table_rows: 记录行数
data_length: 数据总大小,以字节为单位,除1024为K,除1048576为M
index_length: 索引总大小,以字节为单位,除1024为K,除1048576为M

create_time 表创建时间

information_schema.tables.create_time 表创建时间
在MySQL 5.0以后的版本中,也可以查询 INFORMATION_SCHEMA 库中的 TABLES 表中的 CREATE_TIME 字段来看表的创建时间,
即information_schema.tables.create_time

或者,可以使用SHOW TABLE STATUS命令显示表的相关信息。
例如,对于mysql数据库中的user表:
SHOW TABLE STATUS LIKE 'user'

注意:在innoDB引擎中,information_schema.tables.create_time 是表结构最后一次被 ALTER TABLE 语句更新的时间,比如用 ALTER TABLE 改变一个列的默认值时此字段会被更新。
但MyISAM引擎又不同,ALTER TABLE时不会更新此字段。
如果想看表的精确创建时间,应该看数据库文件系统中表的 .frm 文件的创建时间。但Linux系统中文件是不保存创建时间的,所以也没法看到。

When was my table last ALTERed?
https://stackoverflow.com/questions/9537248/when-was-my-table-last-altered

data_length 表大小

information_schema.tables.data_length 表大小,以字节为单位,除1024为K,除1048576为M

看uds数据库各个表占空间大小,倒序排列,单位MB

select table_name, table_rows, data_length, index_length, (data_length+index_length)/1024/1024 as MB
from information_schema.tables
where table_schema='uds'
order by MB desc;

index_length 索引大小

information_schema.tables.index_length 索引大小,以字节为单位,除1024为K,除1048576为M


innodb_trx

MySQL 5.5 中,information_schema 库中新增了三个关于锁的表,亦即 innodb_trx、innodb_locks 和 innodb_lock_waits。
其中 innodb_trx 表记录当前运行的所有事务,innodb_locks 表记录当前出现的锁,innodb_lock_waits 表记录锁等待的对应关系。

innodb_trx表各字段含义

trx_id:事务ID。
trx_state:事务状态,有以下几种状态:RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started:事务开始时间。
trx_requested_lock_id:事务当前正在等待锁的标识,可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
trx_wait_started:事务开始等待的时间。
trx_weight:事务的权重。
trx_mysql_thread_id:事务线程 ID,可以和 PROCESSLIST 表的id字段 JOIN。
trx_query:事务正在执行的 SQL 语句。
trx_operation_state:事务当前操作状态。
trx_tables_in_use:当前事务执行的 SQL 中使用的表的个数。
trx_tables_locked:当前执行 SQL 的行锁数量。
trx_lock_structs:事务保留的锁数量。
trx_lock_memory_bytes:事务锁住的内存大小,单位为 BYTES。
trx_rows_locked:事务锁住的记录数。包含标记为 DELETED,并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified:事务更改的行数。
trx_concurrency_tickets:事务并发票数。
trx_isolation_level:当前事务的隔离级别。
trx_unique_checks:是否打开唯一性检查的标识。
trx_foreign_key_checks:是否打开外键检查的标识。
trx_last_foreign_key_error:最后一次的外键错误信息。
trx_adaptive_hash_latched:自适应散列索引是否被当前事务锁住的标识。
trx_adaptive_hash_timeout:是否立刻放弃为自适应散列索引搜索 LATCH 的标识。

MySQL 5.5 InnoDB 锁等待
https://dbarobin.com/2015/01/27/innodb-lock-wait-under-mysql-5.5/


工具

while循环批量插入数据

创建一个存储过程,while 循环插入 100 万 person 数据,同时给每个人打3个标签插入 person_tag 关联表

DROP PROCEDURE IF EXISTS batch_insert_realinfos;
delimiter $
create procedure batch_insert_realinfos()
begin
    declare num int;
    set num=1;
    while num < 1000000 do
        -- 随机姓名 可根据需要增加/减少样本
        set @SURNAME = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';
        set @NAME = '丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';
        -- length(@surname)/3 是因为中文字符占用3个长度
        set @FULL_NAME = concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));
        set @id_no = CONCAT('1',CEILING(RAND()*9000000000+1000000000));
        set @rand_conf = convert(rand() * 100, decimal(10,2));
        set @arch_id = CEILING(RAND()*9000000000+1000000000);
        set @person_no = substring(MD5(RAND()),1,15);
        set @arch_cover = substring(MD5(RAND()),1,24);
        set @rand_tag_id = floor(rand() * 27);
        insert into person(archive_cover, archive_id, confidence, id_card, id_photo_address, last_modified_date, manual_process, person_number, real_info_modified_date, real_name)
            values(@arch_cover, @arch_id, @rand_conf, @id_no,'http://image.masikkk.com/idcard', now(),0, @person_no, now(),@FULL_NAME);
        insert into person_tag(person_id, tag_id) values
            (LAST_INSERT_ID(), @rand_tag_id), (LAST_INSERT_ID(), @rand_tag_id+1), (last_insert_id(), @rand_tag_id-1);
        set num=num+1;
    end while;
end $
call batch_insert_realinfos();

mysqldump数据库(表)导入导出

远程数据库(表)导出到本地数据库(表)文件

导出数据库/表

导出的其实是一个 .sql 脚本,直接记事本就可以打开,里面有 建库建表语句, insert 数据语句
(1)导出数据库

-- 将192.168.1.1主机上的mydb数据库导出到本地的mydb.bak文件中
mysqldump -h192.168.1.1 -uroot -p123456 --databases mydb > mydb.bak;

-- 将本地mysql服务器上的mydb1数据库导出到本地的mydb1.bak文件中
mysqldump  -uroot -p123456 --databases mydb1 > mydb1.bak;

(2)导出数据表

-- 将192.168.1.1主机上的mydb数据库的tb1数据表导出到本地的tb1.bak文件中
mysqldump -h192.168.1.1 -uroot -p123456 mydb tb1 > tb1.bak;

-- 将本地主机上的mydb1数据库的tb2数据表导出到本地的tb2.bak文件中
mysqldump  -uroot -p123456 mydb1 tb2 > tb2.bak;

导出为多条insert语句

默认 mysqldump 会将多条插入语句导出成一条多个 value 的 insert 语句
在mysqldump时加上参数 --skip-extended-insert 即可导出为多条 insert 语句
--extended-insert, -e 表示使用具有多个VALUES列的INSERT语法。这样使导出文件更小,并加速导入时的速度。默认为打开状态,使用 --skip-extended-insert 取消选项。

mysqldump -h localhost -u user -ppsswd --skip-extended-insert blog comment > blog.comment.sql

导入数据库/表

(1)导入数据库
在本地数据库中创建相对应导出的数据库mydb同名的数据库:

create database mydb;

然后退出数据库,再使用以下的 命令导入数据库文件mydb.bak到本地数据库mydb中:

mysql -uroot -p123456 mydb < /root/data/mydb.bak;

或者进入mysql中,使用sourc指令完成数据库导入,如下:

mysql> source  /root/data/mydb.bak;

/root/data/mydb.bak 是远程数据库mydb导出文件的本地存放位置

(2)导入数据表
在本地数据库中创建相对应导出的数据库mydb同名的数据库:
mysql> create database mydb;
切换到 mydb 数据库
use mydb;

然后在mysql中使用source指令来完成数据表的导入,如下:
mysql> source /root/data/tb1.bak;
/root/data/tb1.bak是远程数据表tb1导出文件的本地存放位置

MySQL数据库(表)的导入导出(备份和还原)
https://blog.csdn.net/Deutschester/article/details/6866842

windows上导出文件无法导入

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:
shell> mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see Section 10.4, “Connection Character Sets and Collations”), so the dump file will not load correctly. To work around this issue, use the –result-file option, which creates the output in ASCII format:
shell> mysqldump [options] --result-file=dump.sql

4.5.4 mysqldump — A Database Backup Program
https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

mysqldump命令详解

有3种方式来调用mysqldump:
shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] —database DB1 [DB2 DB3…]
shell> mysqldump [options] –all–database
如果没有指定任何表或使用了—database或–all–database选项,则转储整个数据库。

–host=host_name,-h host_name
从给定主机的MySQL服务器转储数据。默认主机是localhost。

–user=user_name,-u user_name
连接服务器时使用的MySQL用户名。

–password[=password],-p[password]
连接服务器时使用的密码。如果你使用短选项形式(-p),不能在选项和密码之间有一个空格。如果在命令行中,忽略了–password或-p选项后面的 密码值,将提示你输入一个。

–opt
该选项是速记;等同于指定 –add-drop-tables–add-locking –create-option –disable-keys–extended-insert –lock-tables –quick –set-charset。它可以给出很快的转储操作并产生一个可以很快装入MySQL服务器的转储文件。该选项默认开启,但可以用–skip-opt禁用。要想只禁用确信用-opt启用的选项,使用–skip形式;例如,–skip-add-drop-tables或–skip-quick。

–compress,-C
压缩在客户端和服务器之间发送的所有信息(如果二者均支持压缩)。

–lock-tables,-l
开始转储前锁定所有表。

mysqldump命令详解
http://blog.51cto.com/wangwei007/980586

MySQL mysqldump命令
https://www.jianshu.com/p/14947ba6862b


执行sql脚本

Mysql命令行执行.sql脚本有两种方式

1、在未连接数据库的情况下
mysql -h localhost -uroot -ppwd database_name < prod.sql

例如执行一个批量sql脚本并统计执行时间

time mysql -h localhost -uroot -ppwd database_name < prod.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
real    6m13.689s
user    0m30.067s
sys    0m11.806s

2、在已经连接数据库的情况下,使用 source 命令 加 脚本全路径
source /tmp/prod.sql
其中 /tmp/prod.sql 是登录mysql的机器上的sql脚本


拷贝创建新表

创建新表同时拷贝数据(丢失约束等)

复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表
这种方法会将oldtable中所有的内容都拷贝过来,当然我们可以用delete from newtable;来删除。
不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra(auto_increment)等属性。需要自己用alter table语句添加,而且容易搞错。

只拷贝表结构(丢失约束等)

只复制表结构到新表
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
其中的where只要是个恒为false的表达式即可,此方法同样会丢失约束等额外信息

只拷贝表结构(保留约束等)

CREATE TABLE 新表 LIKE 旧表
此方法会保留表的约束等所有额外信息,推荐使用

创建新表并复制数据(保留约束等)

完整复制表的方法:
先创建新表,保留约束等,在插入旧表的所有数据
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

例如

create table user_bk like user;
insert into user_bk select * from user;

MySQL复制表结构表数据
https://www.cnblogs.com/emanlee/p/5140670.html


MySQL函数和操作符

函数和操作符总索引
12.1 Function and Operator Reference
https://dev.mysql.com/doc/refman/5.6/en/func-op-summary-ref.html

日期和时间函数

12.7 Date and Time Functions
https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

now()

NOW([fsp])
返回当前日期和时间,格式为 ‘YYYY-MM-DD HH:MM:SS’ 或 YYYYMMDDHHMMSS

mysql> SELECT NOW();
        -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 20071215235026.000000

now()和sysdate()区别

mysql的官方解释:
NOW() returns a constant time that indicates the time at which the statement began to execute.
This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.

now()函数返回语句开始执行的时间;而sysdate()返回函数执行时的时间。

mysql now() sysdate() 区别
https://my.oschina.net/friendship/blog/510532


FROM_UNIXTIME 时间戳转日期

FROM_UNIXTIME(unix_timestamp[,format])
其中 unix_timestamp 表示距离 1970-01-01 00:00:00 的秒数

mysql> SELECT FROM_UNIXTIME(1447430881);
        -> '2015-11-13 10:08:01'
mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
        -> 20151113100801
mysql> SELECT FROM_UNIXTIME(1447430881, '%Y %D %M %h:%i:%s %x');
        -> '2015 13th November 10:08:01 2015'

https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_from-unixtime

UNIX_TIMESTAMP 日期转时间戳

UNIX_TIMESTAMP([date])

不带参数使用时返回当前时间的时间戳(秒)

mysql> SELECT UNIX_TIMESTAMP();
        -> 1447431666
mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
        -> 1447431619
mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
        -> 1447431619.012

https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_unix-timestamp

根据生日计算年龄

TIMESTAMPDIFF(YEAR, DATE(u.birthday), CURDATE()) as 'age'
其中 birthday 是 date 类型


信息函数

12.15 Information Functions
https://dev.mysql.com/doc/refman/5.6/en/information-functions.html

LAST_INSERT_ID()

https://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id

简单说来,就是这个函数将返回插入的那条记录在表中自增的那个字段的值,一般我们都给那个自增字段命名为ID。这样就可以返回刚插入的记录的ID值了。

这个函数是基于connection的,也就是不会被其他客户端的connection影响到,所以结果是准确的。如果使用select max(id) from table,在高密度的插入请求下,是有可能出问题的,返回错误值

Mysql中LAST_INSERT_ID()的函数使用详解
https://blog.csdn.net/hsd2012/article/details/51464409


比较操作符

12.3.2 Comparison Functions and Operators
https://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html

coalesce()返回第一个非null值

COALESCE(value,...)
返回列表中的第一个非null表达式的值
COALESCE是一个函数, coalesce(exp_1, exp_2, …,exp_n) 依次参考各参数表达式,遇到非null值即停止并返回该值。如果所有的表达式都是空值,最终将返回一个空值

select coalesce(null, 2, 3, 1);
结果是第一个非空值2

select coalesce(null, ‘’, 2, 3, 1);
结果是第一个非空值’’

COALESCE(expression1, expressionN)
与此 CASE 函数等效:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
when expression2 is not null then expression2
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END

字符串函数

12.5 String Functions
https://dev.mysql.com/doc/refman/5.6/en/string-functions.html

length()字符串字节长度

LENGTH(str)
length是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符

mysql> SELECT LENGTH('text');  -> 4
       SELECT LENGTH('吗');  -> 3

function_length
https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_length

mysql随机字符串/随机姓名

-- 随机 0-1
select floor(rand() * 2);

-- 随机布尔值
select if(floor(rand() * 2) = 1 , '是' , '否');

-- 随机2位小数
select convert(floor(rand() * 70 + 30) / 100,decimal(10,2));

-- 100内的随机两位小数浮点数
select convert(rand() * 100, decimal(10,2));

-- 随机N位字符串
select substring(MD5(RAND()),1,20);
select substr(md5(rand()),1,20);

-- 随机姓名 可根据需要增加/减少样本
set @SURNAME = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';
set @NAME = '丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';
-- length(@surname)/3 是因为中文字符占用3个长度
set @FULL_NAME = concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));
select @FULL_NAME;

substring_index()字符串分隔

SUBSTRING_INDEX(str,delim,count)

返回字符串 str 中分隔符 delim 第 count 次出现位置之前(后)的字符串。
count 为正数时,返回从左数第 count 个 delim 之前的子串。
count 为负数时,返回从右数第 count 个 delim 之后的子串。
delim 区分大小写。

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

-- 查 email 地址及 email 前缀
select email, SUBSTRING_INDEX(email,"@",1) as 'email_prefix' from user;

function_substring-index
https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_substring-index

concat()字符串串接/int转字符串

CONCAT(str1, str2, ...)
字符串串接,可用于 int 等类型转换为字符串,比如 concat(8,’0’) 得到字符串 ‘80’
其中任意参数为null,则结果为null

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

For quoted strings, concatenation can be performed by placing the strings next to each other:
mysql> SELECT 'My' 'S' 'QL';
        -> 'MySQL'

function_concat
https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_concat

lpad(str,len,padstr)字符串左填充

LPAD(str,len,padstr)
LPAD(str,len,padstr) 返回字符串 str, 其左边由字符串padstr 填补到len 字符长度。假如str 的长度大于len, 则返回值被缩短至 len 字符。

例如:

mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'
mysql> SELECT LPAD('hi',1,'??');
        -> 'h'
mysql> select LPAD('1', 8, 0);
-> '00000001'

function_lpad
https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_lpad


聚集函数

12.19.1 Aggregate (GROUP BY) Function Descriptions
https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html

group_concat()查询转为逗号分隔列

GROUP_CONCAT(expr)
select group_concat(id)
from user

超过 group_concat_max_len 长度的会被截断, 默认 1024

GROUP_CONCAT(expr)
https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat


类型转换函数

12.10 Cast Functions and Operators
https://dev.mysql.com/doc/refman/5.6/en/cast-functions.html

cast()字符串转int

CAST(expr AS type)
将 varchar 转为 int 可以用 cast(a as signed) 其中a为varchar类型的字符串

例如:

select cast('125e342.83' as signed) as clm1, cast('foo seo 589' as signed) as clm2,cast('3.35' as signed) as clm3;
+------+------+------+
| clm1 | clm2 | clm3 |
+------+------+------+
|  125 |    0 |    3 |
+------+------+------+

convert()类型转换

CONVERT(expr,type), CONVERT(expr USING transcoding_name)
varchar 转 int 也可以用 convert(a, signed),其中a是varchar类型

type可以是以下值中的一个:
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]


用户自定义变量

9.4 User-Defined Variables
https://dev.mysql.com/doc/refman/5.6/en/user-variables.html

设置自定义变量

使用 set 来定义变量:

SET @var_name = expr [, @var_name = expr] ...

或者使用 select

SELECT @var_name := expr [, @var_name = expr] ...

用户变量:以@开始,形式为@var_name,以区分用户变量及列名。它可以是任何随机的,复合的标量表达式,只要其中没有列指定。
一个变量名可以由当前字符集的数字字母字符和_$.组成。缺省字符集是ISO-8859-1 Latin1;这可以用mysqld 的–default-character-set 选项更改字符集。
对于SET,可以使用=:=来赋值,对于SELECT只能使用:=来赋值。

使用自定义变量

比如先定义一个 user_id 变量,后面的sql都引用这个变量,不用写好几次了:

set @var_user_id=147884;
delete from user where user_id=@var_user_id;
delete from user_address where user_id=@var_user_id;

DQL

SQL优化

去掉表字段上的unix_timestamp时间函数

根据更新时间查询 user 报慢查询,DBA 建议不要在 表字段 上使用函数,会导致无法使用索引。
不要在表字段上用函数,会用不到索引,在参数上用或者直接转换下参数

MariaDB [db]> explain select id from user where unix_timestamp(update_time) >= 1571673600 and unix_timestamp(update_time) < 1571760000 ;
+------+-------------+-------+-------+---------------+-------------+---------+------+--------+--------------------------+
| id   | select_type | table | type  | possible_keys | key         | key_len | ref  | rows   | Extra                    |
+------+-------------+-------+-------+---------------+-------------+---------+------+--------+--------------------------+
|    1 | SIMPLE      | user  | index | NULL          | update_time | 4       | NULL | 208320 | Using where; Using index |
+------+-------------+-------+-------+---------------+-------------+---------+------+--------+--------------------------+
1 row in set (0.012 sec)

MariaDB [db]> explain SELECT id FROM user WHERE update_time >= '2019-10-22 00:00:00.0(Timestamp)' AND update_time < '2019-10-23 00:00:00.0(Timestamp)';
+------+-------------+-------+-------+---------------------------+-------------+---------+------+--------+--------------------------+
| id   | select_type | table | type  | possible_keys             | key         | key_len | ref  | rows   | Extra                    |
+------+-------------+-------+-------+---------------------------+-------------+---------+------+--------+--------------------------+
|    1 | SIMPLE      | user  | index | update_time   | update_time | 4       | NULL | 208320 | Using where; Using index |
+------+-------------+-------+-------+---------------------------+-------------+---------+------+--------+--------------------------+
1 row in set (0.010 sec)

原因
SQL语句where中如果有functionName(colname)或者某些运算,则MYSQL无法使用基于colName的索引。使用索引需要直接查询某个字段。
索引失效的原因是索引是针对原值建的二叉树,将列值计算后,原来的二叉树就用不上了;
为了解决索引列上计算引起的索引失效问题,将计算放到索引列外的表达式上。

create_time排序改为id排序避免回表

假如 user 表使用自增 id 作为主键,且创建后 create_time 不会变,则 id 排序和 create_time 排序的结果是一样的,可使用 id 排序代替 create_time 排序。
因为 create_time 上即使有索引也是二级索引,需要回表找到主键 id 才行。

不要使用offset,limit循环分页

如果像下面这样循环分页查,随着 offset 的增加,查询会变的越来越慢,因为 mysql 是查出匹配条件的全部数据后再切分的。

select *
from user
where xx=xx
order by xx
limit @offset, 100;

正确的循环分页查询方法应该是把 where 条件作为变量,每次不断改变条件,使用 where 条件和 limit 来分页,不要使用偏移量 offset。
典型的比如使用 id,每次记录上一页最后一个id当做条件

select *
from user
where id > @last_id
order by id
limit 100;

有些条件下,可能无法转换为使用 where 条件的方式,比如要按 update_time 排序后分页,如果 update_time 有重复的,无法使用上一页的 update_time 做条件进行精确分页。

两表关联的连接表如何建索引

user 人员表(id, name, birthday)
user_tag 人员标签表(id, user_id, tag_id)一个人可以有多个标签
tag 标签表 (id,name)
根据标签id列表查询人员并按生日倒序排序特别慢

select distinct u.*
from user u join user_tag ut on u.id=ut.user_id
where ut.tag_id in (1,2,3)
order by u.birthday desc limit 10;

数据量:200万 user,300万 user_tag,这种索引该怎么建?
最终建立的索引

user_tag 表
1、在 user_id 列上创建索引,为了加速”查某人的标签”
2、在 (tag_id, user_id) 列上创建联合索引,为了查某标签下有多少人
alter table user_tag
add index idx_user_id(user_id),
add index idx_tag_user(tag_id, user_id);

网上有人做了实验,结果是:关联表分别创建 user_id 与 tag_id 的单列索引 idx_user, idx_tag 最优

MySQL两表关联的连接表该如何创建索引?
https://www.jianshu.com/p/0ec5b4dedc1a


like binary 区分大小写

MySQL的like查询是不区分大小写的
有时候,我们需要区分大小写的是,该怎么办呢?

一、一种方法是在查询时指定大小写敏感,在like的后面加个 binary 就可以了

select *
from user
where name like binary '%aaa%'

二、另一种方法是建表时可以设置表或行的collation,使其为binary或case sensitive。在MySQL中,对于Column Collate其约定的命名方法如下:
*_bin: 表示的是binary case sensitive collation,也就是说是区分大小写的
*_cs: case sensitive collation,区分大小写
*_ci: case insensitive collation,不区分大小写

order by field按指定顺序排序

order by field 可以按指定的顺序排序,最好搭配in一起使用

SELECT * FROM MyTable
where id in(5, 3, 7, 1)
ORDER BY FIELD(`id`, 5, 3, 7, 1)

好像如果是数字排序的话,不加in也可以。

按指定的姓名顺序排序

SELECT * FROM MyTable
WHERE name IN ('张三', '李四', '王五', '孙六')
ORDER BY FIELD(name, '李四', '孙六', '张三', '王五');

How does ORDER BY FIELD() in MySQL work internally
https://dba.stackexchange.com/questions/109120/how-does-order-by-field-in-mysql-work-internally


exists

重复数据取最后更新的

表结构定义:

-- 用户身份历史表
DROP TABLE IF EXISTS user_role_transaction;
CREATE TABLE `user_role_transaction` (
  `id`                   BIGINT       NOT NULL    AUTO_INCREMENT,
  `user_id`              BIGINT       NOT NULL,
  `account_id`           BIGINT       NOT NULL    DEFAULT 0,
  `user_identity`        TINYINT      NOT NULL    DEFAULT 0 COMMENT '用户身份',
  `create_time`          DATETIME     NOT NULL    DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY (`user_id`),
  KEY (`account_id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

存储的是 user_id 的 role 值历史。

要求对于 user_id 相同的记录,选出 create_time 最新的一条,单个查的话很简单,排下序就行,现在要求根据 user_id 批量查。
sql 如下,对于外层的每条记录,去内层查有没有 create_time 更大的,没有的话,选出这条记录:

-- 批量查询每个user_id的最新身份
select *
from user_role_transaction urt
where not exists (
  select 1
  from user_role_transaction urt2
  where urt.user_id = urt2.user_id and urt2.create_time > urt.create_time
)
and user_id in (120253,147896)

这个 sql 的问题是,如果有 user_id 和 create_time 都相同的记录且这个 create_time 是这个 user_id 的最大的,还是会选出多条,所以就依赖于数据必须创建时间不同。
如何解决?
其实这个需求应该根据 id 筛选,直接选 id 最大的一条,肯定是唯一的,就不应该根据 create_time 查。

SQL中遇到多条相同内容只取一条的最简单实现
https://blog.csdn.net/lizeyang/article/details/18420007


重复数据取最后插入的

表结构同上,根据 user_id 批量查询,对于每个 user_id,如果存在多条数据,选择 id 最大的,即最后插入的,id 为自增主键。

select *
from user_role_transaction urt
where not exists (
  select 1
  from user_role_transaction urt2
  where urt2.user_id=urt.user_id and urt2.id>urt.id
)
and user_id in (153048,153037)

筛选 register_record 表中有 比 third_party_user 时间小的记录的,查出其 create_time 最小的一个

select u.id as user_id, case when tpu.name='懂车帝客户' then '6Cvz6a3pPW' else 'eUFUWmA6L6' end as 'code', u.create_time, rr.campaign_code as '首次code'
from third_party_user tpu join user u on tpu.phone=u.mobile
join leads_distribution ld on u.id = ld.user_id
left join register_record rr on u.id=rr.user_id
where tpu.clue_create_time > '2020-04-03 19:28:00' and tpu.clue_create_time < '2020-04-09 16:07:00'
and exists(select * from register_record rr2 where rr2.user_id = u.id and rr2.create_time < tpu.create_time)
and not exists(select * from register_record rr3 where rr3.user_id=u.id and rr3.create_time < rr.create_time)
order by tpu.id desc;

重复数据取最大/最小

ID相同取最小TYPE

-- 方法1:(推荐在数据量较大时使用)
select * from Temp A where TYPE=(select min(TYPE) from Temp where ID=A.ID)

-- 方法2:
Select * from Temp A where not exists(select 1 from Temp where ID=A.ID and TYPE<A.TYPE)

-- 方法3:
select A.* from Temp A join (select min(TYPE)TYPE,ID from Temp group by ID) B on A.ID=B.ID and A.TYPE=B.TYPE

Sql 处理重复记录(相同ID取另一字段最大值)
https://www.norbread.com/2018/01/14/sql-duplicated/


distinct多列

select distinct a,b,c from tableA;
注意此时是将a,b,c三列所有不同的组合全部列出来,而不仅仅只是distinct a

如果想 distinct 只作用于a列,同时又能查出对应的b和c列,可以使用group by a来代替,此时如果唯一的a列对应的b,c列值不同,查出的结果可能具有不确定性。

mysql实现distinct限制一列而查多列的方法
https://blog.csdn.net/liuxiao723846/article/details/79181857


case when 条件表达式

case when 查及格和不及格人数

用一个SQL语句完成不同条件的分组
例如:stu_cls表有字段name class score,用一条sql查询每个class里及格和不及格的学生人数,结果形式class num(>=60),num(<60)

select class,
sum(case when score>=60 then 1 else 0 end) '及格人数' ,
sum(case when score<60 then 1 else 0 end) '不及格人数',
count(*) '总人数'
from stu_cls
group by class;

case根据列in分组

name 是 masikkk, devgou, madaimeng 的 ,nickname 列是 myself
name 是 其他的, nickname 是 others

select mobile, name, create_time, case when name in ('masikkk','madaimeng','devgou') then 'myself' else 'others' end as 'nickname'
from user;

user_role_transaction 保存的是用户身份变更历史,每变动一次插入一条新数据
user 用户表,user 表中的数据可能在 user_role_transaction 中不存在
查询所有用户的 最新实时 身份 identity:

select u.id, case when urt.id is not null then urt.user_identity else 1 end
from  user u left join user_role_transaction urt on urt.user_id = u.id
WHERE urt.id is null or (urt.user_id = u.id AND NOT exists(
    SELECT *
    FROM user_role_transaction urt2
    WHERE urt2.user_id = urt.user_id AND urt2.id > urt.id)
)
order by u.id;

使用了 case when,对于在 user_role_transaction 表中的 user(即 urt.id is not null)结果为 urt.user_identity,否则(即不在 user_role_transaction 表中的 user)结果为固定值 1

update中使用case when赋值

需求:
user 表的 identity 字段是当前用户身份,新加的字段
user_role_transaction 保存的是用户身份变更历史,每变动一次插入一条新数据
之前只有 user_role_transaction 历史表,user 表中没有 identity 字段,现在刚加上,要用 user_role_transaction 的 user_identity 字段初始化 user 表的 identity 字段,规则是:
对于 user_id 在 user_role_transaction 表中的 user,把 user 表的 identity 字段设置为此 user_id 在 user_role_transaction 表中的最新一个值(即id最大的)
对于 user_id 不在 user_role_transaction 表中的 user,把 user 表的 identity 字段设置为固定值 1

sql 如下:

UPDATE user u left join user_role_transaction urt on urt.user_id = u.id
set u.identity = case when urt.id is not null then  urt.user_identity else 1 end
WHERE urt.id is null or (urt.user_id = u.id AND NOT exists(
    SELECT *
    FROM user_role_transaction urt2
    WHERE urt2.user_id = urt.user_id AND urt2.id > urt.id)
);

解释:
使用了 update join 根据查询条件更新,由于 user 表中的数据有可能不在 user_role_transaction 表中,使用了 left join 左外连接来保留 user 中非关联数据
筛选条件是 urt.id 为空(即左外关联后 user_role_transaction 表对应列是空的,即不在 user_role_transaction 表中的 user),或者关联后 user_role_transaction 中 id 最大的(这里用了个 not exists 筛选没有比他 id 更大的)
set 赋值语句使用了 case when,对于在 user_role_transaction 表中的 user(即 urt.id is not null)设置为选出的 urt.user_identity,否则(即不在 user_role_transaction 表中的 user)设置为 1


不等号

mysql中用 <>!= 都是可以的,但sqlserver中不识别 !=,所以建议用 <>


OR条件没加括号直接和其他条件AND

比如想查询指定user_id的没有订单号的数据,如果写成下面这样就大错特错了:

SELECT * FROM order
WHERE user_id = 2812 AND order_no IS NULL OR order_no = '' ;

正确的写法是用括号把或条件括起来:

SELECT * FROM order
WHERE user_id = 2812 AND
  ( order_no IS NULL OR order_no = '' );

join

left joinleft outer join : 左连接,返回左表中所有的记录以及右表中连接字段相等的记录。
right joinright outer join: 右连接,返回右表中所有的记录以及左表中连接字段相等的记录。
inner joinjoin : 内连接,又叫等值连接,只返回两个表中连接字段相等的行。
full joinfull outer join: 外连接,返回两个表中的行:left join + right join。
cross join : 结果是笛卡尔积,就是第一个表的行数乘以第二个表的行数。

joininner join是完全相同的

mysql 和 标准sql 中,joininner join 都是完全相同的
Difference between JOIN and INNER JOIN
https://stackoverflow.com/questions/565620/difference-between-join-and-inner-join

连接中的on和where

数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。
在使用 left jion 时,onwhere 条件的区别如下:
1、on 条件是在生成临时表时使用的条件,它不管 on 中的条件是否为真,都会返回左边表中的记录。
2、where 条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有 left join 的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

SQL JOIN 中 on 与 where 的区别
https://www.runoob.com/w3cnote/sql-join-the-different-of-on-and-where.html

where逗号分隔等价于joininner join

1、如下两个 sql 是相等的,都是 带有 on 条件的 inner join

select * from A a join B b on a.id=b.a_id;
select * from A a, B b where a.id=b.a_id;

2、如下两个 sql 也是相等的,都是不带 on 条件的 cross join,结果是两表的笛卡尔积,行数等于 A表行数 乘以 B表行数

select * from A a join B b;
select * from A a, B b;

但更推荐使用join语法

INNER JOIN ON vs WHERE clause
https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause

用left join代替not in

比如我们想查询用户地址表 user_address 中的 user_id 不在 user 表中的脏数据,这些脏数据可能是由于 user_address 表的 user_id 字段没加外键约束导致的。
可以用下面的 not in 子句进行条件筛选:

SELECT *
FROM user_address
where user_id not in (select id from user );

或者 使用 left join 左外连接 把 user_address 表中独有的数据保留下来,结果中右边user表中没数据的都是null,直接用user的某个字段是否null判断即可

select *
from user_address as ua left join user as u ON ua.user_id=u.id
where u.id is null;

可以用关联后的 user 表的任意字段是否 null 进行判断。
用 left join 显得更高端一点儿。

mysql中cross joinjoin以及inner join完全相同

mysql 中,cross joinjoin以及inner join完全相同,无任何区别
见 5.6 版本官网文档
13.2.9.2 JOIN Syntax
https://dev.mysql.com/doc/refman/5.6/en/join.html

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

当没有on连接条件时,cross joinjoin以及inner join 都是笛卡尔积。
例如

select * from A cross join B;

可以替换为 inner joinjoin ,和标准sql一样,是做笛卡尔积,结果个数是表A行数乘以表B行数

由于mysql将这三者视为等同,所以cross join也可以加on条件,而标准sql中cross join是不能加条件的。
例如

select *
from A as a cross join B as b on a.id=b.a_id;

和使用 inner joinjoin 完全相同。

mysql中没有full join全连接

mysql 中没有 full join 语法,下面的sql会报语法错误:

select *
from A as a full join B as b on a.id=b.a_id;

可使用 union 并集代替全连接。

How to do a FULL OUTER JOIN in MySQL?
https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql


group by

join 后 group by 聚合为拼接串

user_label_mapping 是 user id 和 标签 id 映射表
user_label 是 标签表
一个 user 可以有多个标签,查出同一 user 的多个标签并串接为 逗号分隔字符串

select user_id, group_concat(ul.name) 'hobbies'
from user_label_mapping ulm join user_label ul on ulm.user_label_id = ul.id
where ulm.type='user_hobby'
group by user_id

join 后先 group by 按 user_id 分组,然后把 标签名 group_concat 串接起来。

\G 列变行

\G 的作用是将查到的结构旋转 90 度变成纵向


DCL

手动提交事务

使用 START TRANSACTION, BEGIN 来显式地开启一个事务。
在显式开启事务后,在默认设置下(即参数 completion_type 等于0),MySQL会自动地执行 SET AUTOCOMMIT=0 的命令,并在 COMMITROLLBACK 结束一个事务后执行 SET AUTOCOMMIT=1

start transctionbegin:显示的开启一个事务
commitcommit work:commit work与completion_type的关系,commit work是用来控制事务结束后的行为,是chain还是release的,可以通过参数completion_type来控制,默认为0(或者NO_CHAIN),表示没有任何操作 与commit效果一样。

MariaDB [uds]> begin;
Query OK, 0 rows affected (0.010 sec)

MariaDB [uds]> update user set name='手动commit3' where id=136228;
Query OK, 1 row affected (0.015 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [uds]> commit;
Query OK, 0 rows affected (0.014 sec)

MySQL事务控制语句(学习笔记)
https://blog.csdn.net/mchdba/article/details/8690935

mysql start transaction 和 set autocommit = 0 的区别

1、mysql使用InnoDB的引擎,那么是自动开启事务的,也就是每一条sql都是一个事务(除了select)。
2、由于第一条的原因,所以我们需要autocommit为on,否则每个query都要写一个commit才能提交。
3、在mysql的配置中,默认缺省autocommit就是为on,这里要注意,不用非要去mysql配置文件中显示地配置一下。
4、最关键的来了,当我们显示地开启一个事务,也就是写了begin的时候,autocommit对此事务不构成影响。而不是网上大家说的,必须要写一个query临时设置autocommit为off,否则比如三个query只能回滚最后一个query,这是完全不对的。

1、set autocommit = 0
关闭当前会话中事务的自动提交,需要手动 commit 或者 rollback,相当于开启一个全局的事务。在 mysql 的事务中,默认 autocommit = 1,每一次 sql 操作都被认为是一个单次的事务,被隐式提交

2、start transaction
挂起 autocommit 的状态,开启一个事务上下文。首先数据库会隐式提交之前的还未被提交的操作,同时开启一个新事务。挂起 autocommit 的意思是保存 autocommit 的当前状态,然后 start transaction,直到 commit or rollback 结束本次事务,再恢复之前挂起的 autocommit 的状态。

如果 start transaction 前 autocommit = 1,则完成本次事务后 autocommit 还是 1
如果 start transaction 前 autocommit = 0,则完成本次事务后 autocommit 还是 0,接下来的操作你仍需手动 commit 才可以提交。

mysql start transaction 和 set autocommit = 0 的区别
https://my.oschina.net/sallency/blog/785476


DML

insert

on duplicate key update

ON DUPLICATE KEY UPDATE 为Mysql特有语法,作用是当insert已经存在的记录时,执行Update

如果在INSERT语句末尾指定了 ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。
例如,如果列 a 为 主键 或 拥有UNIQUE索引,并且包含值1,则以下两个语句具有相同的效果:

INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;

insert多条记录

INSERT INTO user(user_id, name_en, name_cn) VALUES
  ( 8, "llll", "小拉"),
  ( 9, "zsan", "张三"),
  (10, "lisi", "李四"),
  (27, "wwu",  "王五");

insert select 表间数据复制

同一数据库内insert select

1、表结构相同的表,且在同一数据库(如,table1,table2)

# 完全复制
insert into table1 select * from table2;
# 不复制重复纪录
insert into table1 select distinct * from table2;
# 前五条纪录
insert into table1 select top 5 * from table2;

不同数据库间insert select

2、不在同一数据库中(如,db1 table1,db2 table2)

# 完全复制
insert into db1.table1 select * from db2.table2;
# 不复制重复纪录
insert into db1.table1 select distinct * from db2.table2;
# 前五条纪录
insert into tdb1.able1 select top 5 * from db2.table2;

表结构不相同insert select

如果表tb1和tb2只有部分字段是相同的,要实现将tb1中的部分字段导入到tb2中相对应的相同字段中,则使用以下命令:

insert into user_address(user_id, province_id, city_id, province, city)
    select user_id, province_id, city_id, province, city
    from user_region
    where user_id=12345;

Mysql-两个表之间复制数据
https://blog.csdn.net/qingwuh/article/details/81350470


delete

清空表truncate和delete

truncate table wp_comments;
delete from wp_comments;

其中truncate操作中的table可以省略。这两者都是将wp_comments表中数据清空,不过也是有区别的,如下:
truncate是整体删除(速度较快), delete是逐条删除(速度较慢)。
truncate不写服务器log,delete写服务器log,也就是truncate效率比delete高的原因。
truncate不激活trigger(触发器),但是会重置Identity(标识列、自增字段),相当于自增列会被置为初始值,又重新从1开始记录,而不是接着原来的ID数。而delete删除以后,Identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。
如果只需删除表中的部分记录,只能使用DELETE语句配合where条件。 DELETE FROM wp_comments WHERE……

外键导致删除失败

比如从user表中删除数据,如果还有其他表引用此行数据会导致删除失败:

[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`uds`.`table_name`, CONSTRAINT `user_fk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))

如果忽略数据一致性,强行删除的话,可以暂时关闭外键检查:

SET FOREIGN_KEY_CHECKS = 0;
delete from user where user_id=81681419;
SET FOREIGN_KEY_CHECKS = 1;

Can’t drop table: A foreign key constraint fails
https://stackoverflow.com/questions/11100911/cant-drop-table-a-foreign-key-constraint-fails

mysql 外键引发的删除失败
http://www.cnblogs.com/FlyAway2013/p/6864466.html

delete语句使用别名

mysql delele table语句使用别名:
语法:

delete t from table t where t.column = value;

或者不使用别名:

delete from table where column = value;

但如果使用别名,delete后一定要加别名t,否则在某些严格语法检查的情况下会报错。


删除重复行

删除所有重复行

用in

delete from table1
where user_id in (
  SELECT user_id
  FROM table1
  GROUP BY user_id
  HAVING count(*) > 1);

或者用exists

delete from table1
where exists (
  SELECT user_id
  FROM table1
  GROUP BY user_id
  HAVING count(*) > 1);

mariadb中都会报错:
[HY000][1093] Table ‘table1’ is specified twice, both as a target for ‘DELETE’ and as a separate source for data
因为同时进行查询和修改。

解决方法:查询语句外面再套一个查询形成一张临时表

delete from drive_booking
where user_id in (
    select *
    from (
      SELECT user_id
      FROM drive_booking
      GROUP BY user_id
      HAVING count(*) > 1) as temp_table
);

重复行只保留一条

删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录

delete from people
where peopleId in (
  select peopleId
  from people
  group by peopleId
  having count(peopleId) > 1
)
and rowid not in (
  select min(rowid)
  from people
  group by peopleId
  having count(peopleId )>1
);

上面这条语句在mysql中执行会报错,因为同时对表进行查询和更新,mysql不支持这种方式。oracel和msserver都支持这种方式。

解决方法:查询语句外面再套一个查询形成一张临时表
删除 user_region 表中 user_id 重复的数据,只保留id最小的

delete from user_region
where user_id in (
  select user_id
  from (
    select user_id
    from user_region
    GROUP BY user_id
    HAVING count(*)>1
  ) as a
)
and id not in (
  select id
  from (
    select min(id) as id
    from user_region
    GROUP BY user_id
    having count(*)>1
  ) as b
);

问:为什么不能只用 not in min(id) 来删除呢?
答:因为这样会多删除,会把 user_id 不重复的也删掉,非常危险,使用 not in 的话必须加user_id in 条件.

当表中没有id自增主键时,有时候需要根据 更新时间排重,但不是很精确。
删除 user_employee_info 表中 user_id 重复的数据,保留 update_time 最大的

select * from user_employee_info
where user_id in (
  select user_id
  from (
    select user_id
    from user_employee_info
    group by user_id
    having count(*) > 1
  ) as a
)
and update_time not in (
  select update_time
  from (
    select max(update_time) as update_time
    from user_employee_info
    group by user_id
    having count(*)>1
  ) as b
);

这个sql对于两条user_id 相同且 update_time 相同的数据就删除不了。

例如 user_address 表中有 user_id 和地址类型 type,一个user同一类型地址只能存一个,假如表上没有唯一约束造成了重复数据,删除重复数据,保留id最大的

delete from user_address
where user_id in (
  select user_id
  from (
    select user_id
    from user_address
    GROUP BY user_id, type
    HAVING count(*)>1
  ) as a
)
and id not in (
  select id
  from (
    select max(id) as id
    from user_address
    GROUP BY user_id, type
    having count(*)>1
  ) as b
);

delete join

mysql 支持 delete 语句中 join 连表查询

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;
delete t1 from table1 t1 left join table2 t2 on t1.t2_id=t2.id
where t1.id in (1,2,3)
and (t2.status='disable' or t2.id is null);

update

update多列

update user u
set u.name = '张三', u.mobile = '13613661366'
where u.id = 23;

update select join 根据关联查询更新

当你希望更新一批值,且值是通过select条件查询出来时
解决思路 1:
使用 INNER JOIN (最简洁)

UPDATE A a INNER JOIN B b ON b.key_id = a.key_id
SET a.val = 2 WHERE b.satisfy = 1

解决思路 2:

UPDATE A a, (SELECT A.id from A
             LEFT JOIN B ON B.key_id= A.key_id
             WHERE B.satisfy = 1) b
SET a.val = 2
WHERE a.id = b.id

根据无关表 table2 中的某些信息去更新 table1 表

UPDATE `table1` AS `dest`,
    ( SELECT * FROM `table2` WHERE `id` = x ) AS `src`
SET `dest`.`col1` = `src`.`col1`
WHERE `dest`.`id` = x ;

https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query

例如:

update user_info as ui join user as u on ui.user_id = u.id
set ui.ext_info='', ui.address='beijing'
where u.name like '%ST'
and ui.user_id in (
  SELECT * FROM (
         SELECT user_id
         FROM user_info
         WHERE ext_info like '%特殊%'
       ) AS special_user
);

update app_user_relationship as aur join user_account_info as uai on aur.account1_id=uai.account_id
set aur.user1_id=uai.user_id
where aur.user1_id != uai.user_id;

-- join 两个表
UPDATE user u join user_address ua on u.id=ua.user_id join user_address ua2 on ua.pid=ua2.id
set u.city_id=cr2.id, u.province_id=cr2.pid
where ua.satisfy = 1;

update case when 条件表达式更新

需求:
user 表的 identity 字段是当前用户身份,新加的字段
user_role_transaction 保存的是用户身份变更历史,每变动一次插入一条新数据
之前只有 user_role_transaction 历史表,user 表中没有 identity 字段,现在刚加上,要用 user_role_transaction 的 user_identity 字段初始化 user 表的 identity 字段,规则是:
对于 user_id 在 user_role_transaction 表中的 user,把 user 表的 identity 字段设置为此 user_id 在 user_role_transaction 表中的最新一个值(即id最大的)
对于 user_id 不在 user_role_transaction 表中的 user,把 user 表的 identity 字段设置为固定值 1

sql 如下:

UPDATE user u left join user_role_transaction urt on urt.user_id = u.id
set u.identity = case when urt.id is not null then  urt.user_identity else 1 end
WHERE urt.id is null or (urt.user_id = u.id AND NOT exists(
    SELECT *
    FROM user_role_transaction urt2
    WHERE urt2.user_id = urt.user_id AND urt2.id > urt.id)
);

解释:
使用了 update join 根据查询条件更新,由于 user 表中的数据有可能不在 user_role_transaction 表中,使用了 left join 左外连接来保留 user 中非关联数据
筛选条件是 urt.id 为空(即左外关联后 user_role_transaction 表对应列是空的,即不在 user_role_transaction 表中的 user),或者关联后 user_role_transaction 中 id 最大的(这里用了个 not exists 筛选没有比他 id 更大的)
set 赋值语句使用了 case when,对于在 user_role_transaction 表中的 user(即 urt.id is not null)设置为选出的 urt.user_identity,否则(即不在 user_role_transaction 表中的 user)设置为 1


specified twice both target and source

当要更新的目标表同时也在查询条件中时,会报错:
Table name is specified twice, both as a target for ‘UPDATE’ and as a separate source for data in mysql
解决方法是把作为条件的目标表查询为一个中间表。

例如:

UPDATE MYTABLE
SET COL=COL+1
WHERE ID IN (
    SELECT ID
    FROM MYTABLE
    WHERE OTHERCOL=0
  );

DELETE FROM MYTABLE
WHERE ID IN (
  SELECT ID
  FROM MYTABLE
  WHERE OTHERCOL=0
);

改为:

UPDATE MYTABLE
SET COL=COL+1
WHERE ID IN (
  SELECT *
  FROM (
    SELECT ID
    FROM MYTABLE
    WHERE OTHERCOL=0
  ) AS TEMP
);

DELETE FROM MYTABLE
WHERE ID IN (
  SELECT *
  FROM (
    SELECT ID
    FROM MYTABLE
    WHERE OTHERCOL=0
  ) AS TEMP
);

例如:
FROM manager AS m2
改为
FROM (select * from manager) AS m2

Mysql: Table ‘name’ is specified twice, both as a target for ‘UPDATE’ and as a separate source for data
https://www.cnblogs.com/liusonglin/p/4387543.html

Table is specified twice, both as a target for ‘UPDATE’ and as a separate source for data in mysql
https://stackoverflow.com/questions/44970574/table-is-specified-twice-both-as-a-target-for-update-and-as-a-separate-source


Every derived table must have its own alias

意思是每个派生出来的表(或者叫子查询)必须有一个自己的别名。
一般是在多表查询或者子查询的时候会出现这个错误,因为在嵌套查询中,子查询的结果是作为一个派生表给上一级进行查询,所以子查询的结果必须有一个别名。

例如

SELECT id
FROM (
    SELECT id
    FROM user
);

会报错 Every derived table must have its own alias.
改为

SELECT id
FROM (
    SELECT id
    FROM user
) as temp;

在子查询的后面增加一句 as temp,相当于给子查询的结果集派生表取别名为temp,问题就解决了。

但是下面这条sql就不会报错

-- 删除地址表中user_id不存在的脏数据
delete from user_address
where user_id not in (select id from user);

What is the error “Every derived table must have its own alias” in MySQL?
https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql

Mysql错误:Every derived table must have its own alias
https://chenzhou123520.iteye.com/blog/2041684


update返回值与useAffectedRows

比如如下mybatis sql语句:

@Update({"UPDATE user SET name = null WHERE id = #{id}"})
void updateUserProfileById(@Param("id") long id);

默认情况下,mybatis 的 update 操作的返回值是 matched 的记录数,并不是受影响的记录数。
严格意义上来将,这并不是 mybatis 的返回值,mybatis 仅仅只是返回的数据库连接驱动(通常是 JDBC )的返回值

通过对 JDBC URL 显式的指定 useAffectedRows选项,我们将可以得到受影响的记录的条数:
jdbc:mysql://${jdbc.host}/${jdbc.db}?useAffectedRows=true

那么有没有办法让 mybatis 的 update 操作的返回值是受影响的行数呢。因为我们业务逻辑中有时会根据这个返回值做业务判断。答案当然是有的。
修改数据库链接配置为:增加了 useAffectedRows 字段信息。

JDBC默认返回的是符合的行数Rows matched, 如果想返回修改过( Changed)的行数 ,需要使用useAffectedRows参数
useAffectedRows的含义 :是否用受影响的行数替代查找到的行数来返回数据

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm?useAffectedRows=true
jdbc.username=root
jdbc.password=123456

mybatis 中的 update 返回值你真的明白吗
https://www.jianshu.com/p/80270b93082a

聊聊Mybatis Update操作返回值
https://notes.wanghao.work/2017-09-06-%E8%81%8A%E8%81%8AMybatis-Update%E6%93%8D%E4%BD%9C%E8%BF%94%E5%9B%9E%E5%80%BC.html


DDL

DDL与事务

MySQL不支持事务型DDL

MySQL DDL 不支持事务,DDL语句执行后会立即提交
所以 drop table, create table, alter table 这些 DDL 是不支持事务的。

13.3.2 Statements That Cannot Be Rolled Back
https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html

常见数据库的事务DDL支持情况

PostgreSQL 的这篇调研说明了主要数据库是否支持 事务DDL以及为什么。
Transactional DDL in PostgreSQL: A Competitive Analysis
https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis

总结如下:
PostgreSQL - yes
MySQL - no; DDL causes an implicit commit
Oracle Database 11g Release 2 and above - by default, no, but an alternative called edition-based redefinition exists
Older versions of Oracle - no; DDL causes an implicit commit
SQL Server - yes
Sybase Adaptive Server - yes
DB2 - yes
Informix - yes
Firebird (Interbase) - yes

Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?
https://stackoverflow.com/questions/4692690/is-it-possible-to-roll-back-create-table-and-alter-table-statements-in-major-sql

MySQL事务中混合DDL会怎样?

应当将DDL和DML语句以及DCL语句严格分开,避免事务被隐性“破坏”,导致误操作情况发生。

#禁用自动提交
set autocommit=off;
#创建tb1
create table tb1(id int auto_increment primary key,c1 int);
#开始事务
start transaction;
#插入数据
insert into tb1(c1) select 1;
insert into tb1(c1) select 2;
insert into tb1(c1) select 3;
#创建tb2
create table tb2(id int auto_increment primary key,c1 int);

执行完上述 sql 后,如果想回滚3条插入操作,会发现无法将这3条数据删除,因为 create table tb2 这条 DDL 执行完后会自动提交,顺带也会把之前的 DML 提交。

当执行到DDL语句时,会隐式的将当前回话的事务进行一次“COMMIT”操作,因此在MySQL中执行DDL语句时,应该严格地将DDL和DML完全分开,不能混合在一起执行


MySQL的三种DDL处理方式

MySQL 各版本,对于 DDL 的处理方式是不同的,主要有三种:

Copy Table(可读不可写)

Copy Table 方式, 这是 InnoDB 最早支持的方式。顾名思义,通过临时表拷贝的方式实现的。新建一个带有新结构的临时表,将原表数据全部拷贝到临时表,然后 rename,完成创建操作。这个方式过程中,原表是可读的,不可写。但是会消耗一倍的存储空间。

处理过程:
1、首先新建 Temp table,表结构是 ALTAR TABLE 新定义的结构
2、然后把原表中数据导入到这个 Temp table
3、删除原表
4、最后把临时表 rename 为原来的表名
为了保持数据的一致性,中间复制数据(Copy Table)全程锁表只读,如果有写请求进来将无法提供服务,连接数爆张。

在 MySQL 5.1(带InnoDB Plugin)和 5.5 中,有个新特性叫 Fast Index Creation(FIC),就是在添加或者删除二级索引的时候,可以不用复制原表
引入 FIC 之后,创建二级索引时会对原表加上一个S锁,创建过程不需要重建表(no-rebuild);删除InnoDB二级索引只需要更新内部视图,并标记这个索引的空间可用,去掉数据库元数据上该索引的定义即可。这个过程也只允许读操作,不能写入,但大大加快了修改索引的速度(不含主键索引,InnoDB 的按主键聚簇存储特性决定了修改主键依然需要 Copy Table )。

FIC 只对索引的创建删除有效,MySQL 5.6 Online DDL 把这种特性扩展到了添加列、删除列、修改列类型、列重命名、设置默认值等等,实际效果要看所使用的选项和操作类别来定。

Inplace(可读不可写)

Inplace 方式,这是原生 MySQL 5.5,以及 innodb_plugin 中提供的方式。所谓 Inplace,也就是在原表上直接进行,不会拷贝临时表。相对于 Copy Table 方式,这比较高效率。原表同样可读的,但是不可写。

Online(可读可写,5.6及以上)

Online 这是 MySQL 5.6 以上版本中提供的方式。无论是 Copy Table 方式,还是 Inplace 方式,原表只能允许读取,不可写。对应用有较大的限制,因此 MySQL 最新版本中,InnoDB 支持了所谓的 Online 方式 DDL。与以上两种方式相比,online 方式支持 DDL 时不仅可以读,还可以写,对于 dba 来说,这是一个非常棒的改进。


MySQL5.6 中的 InnoDB Online DDL

14.13 InnoDB and Online DDL
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

14.13 InnoDB 在线 DDL - 官方文档的中文翻译
https://zhuanlan.zhihu.com/p/40443907

从 MySQL 5.6 开始,InnoDB 存储引擎提供一种叫 在线 DDL(Online DDL) 的 DDL 执行方式,允许 Inplace 更改表 和 并发 DML 操作
此功能的好处包括:

  • 在繁忙的生产环境中提高响应能力和可用性,使表不可用几分钟或几小时是不切实际的。
  • 使用LOCK子句在DDL操作期间调整性能和并发性之间平衡的能力。 请参阅LOCK子句。
  • 与table-copy方法相比,使用的磁盘空间和I/O开销更少。

Online DDL 基于 MySQL 5.5 开始提供的 快速索引创建特性(fast index creation),快速索引创建特性可以在不拷贝表的情况下进行索引创建和删除。

Online DDL 是默认开启的,无需执行任何特殊操作即可启用在线DDL。 默认情况下,MySQL在允许的情况下执行操作,并尽可能少地锁定。

可以使用 ALTER TABLE 语句的 LOCKALGORITHM 子句控制 DDL 操作的各个方面。
这些子句放在语句的末尾,用逗号分隔表和列。 例如:

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

LOCK 子句可用于微调对表的并发访问程度。
ALGORITHM 子句主要用于性能比较,并且在遇到任何问题时作为旧表复制行为的后备。
例如:

  • 为避免意外地使表不可用于读取,写入或两者,请在 ALTER TABLE 语句中指定一个子句,例如 LOCK = NONE(允许读取和写入)或 LOCK = SHARED(允许读取)。 如果请求的并发级别不可用,则操作立即停止。
  • 要比较性能,请运行 ALGORITHM = INPLACEALGORITHM = COPY 语句。
  • 为避免使用复制表的 ALTER TABLE 操作来绑定服务器,请包括 ALGORITHM = INPLACE。 如果语句不能使用 in-place 机制,则该语句立即停止。

Online DDL 选项

MySQL 在线 DDL 分为 INPLACE 和 COPY 两种方式,通过在 ALTER 语句的 ALGORITHM 参数指定。

ALGORITHM=INPLACE,原地操作,可以避免重建表带来的 IO 和 CPU 消耗,保证 ddl 期间依然有良好的性能和并发。
ALGORITHM=COPY,需要拷贝原始表,所以不允许并发 DML 写操作,可读。这种 copy 方式的效率不如 inplace,因为前者需要记录 undo 和 redo log,而且因为临时占用buffer pool引起短时间内性能受影响。

上面只是 Online DDL 内部的实现方式,此外还有 LOCK 选项控制是否锁表,根据不同的DDL操作类型有不同的表现:默认mysql尽可能不去锁表,但是像修改主键这样的昂贵操作不得不选择锁表。

LOCK=NONE,即 DDL 期间允许并发读写涉及的表,比如为了保证 ALTER TABLE 时不影响用户注册或支付,可以明确指定,好处是如果不幸该 alter 语句不支持对该表的继续写入,则会提示失败,而不会直接发到库上执行。ALGORITHM=COPY 默认 LOCK 级别
LOCK=SHARED,即 DDL 期间表上的写操作会被阻塞,但不影响读取。
LOCK=DEFAULT,让 mysql 自己去判断 lock 的模式,原则是 mysql 尽可能不去锁表
LOCK=EXCLUSIVE,即 DDL 期间该表不可用,堵塞任何读写请求。如果你想 alter 操作在最短的时间内完成,或者表短时间内不可用能接受,可以手动指定。

但是有一点需要说明,无论任何模式下,online ddl 开始之前都需要一个短时间排它锁(exclusive)来准备环境,所以 alter 命令发出后,会首先等待该表上的其它操作完成,在 alter 命令之后的请求会出现等待waiting meta data lock。同样在ddl结束之前,也要等待alter期间所有的事务完成,也会堵塞一小段时间。所以尽量在 ALTER TABLE 之前确保没有大事务在执行,否则一样出现连环锁表。

mysql 5.6 在线 DDL
https://www.cnblogs.com/wyy123/p/10272496.html


Online DDL 索引操作

创建或增加二级索引,删除索引,重命名索引都支持 in-place 的方式,均支持并发 DML,但是不能重建表。其中,删除索引和重命名索引只修改元数据。

创建和增加二级索引

CREATE INDEX name ON table (col_list);
ALTER TABLE tbl_name ADD INDEX name (col_list);

在创建索引时,该表仍可用于读写操作。 CREATE INDEX 语句仅在完成访问表的所有事务完成后才结束,因此索引的初始状态反映了表的最新内容。
在线DDL支持添加二级索引意味着您通常可以通过先创建没有二级索引的表,再加载数据,最后添加二级索引,来加速创建和加载表及相关索引的整个过程。
新创建的辅助索引仅包含CREATE INDEX或ALTER TABLE语句完成执行时表中的已提交数据。 它不包含任何未提交的值,旧版本的值或标记为删除但尚未从旧索引中删除的值。
如果服务器在创建二级索引时退出,则在恢复时,MySQL会删除任何部分创建的索引。 您必须重新运行ALTER TABLE或CREATE INDEX语句。
某些因素会影响此操作的性能,空间使用和语义。

删除索引

DROP INDEX name ON table;
ALTER TABLE tbl_name DROP INDEX name;

在删除索引时,该表仍可用于读写操作。 DROP INDEX语句仅在完成访问表的所有事务完成后才结束,因此索引的初始状态反映了表的最新内容。

14.13.1 Online DDL Operations
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

MySQL给已存在的表增加索引时会锁表吗?

看版本,MySQL 5.6 及以上的话,支持 Online DDL 操作,不会锁表。
MySQL 5.6 以下版本,不支持 Online DDL 操作,会锁表


Online DDL 主键操作

增加主键(原来无显式主键),删除后再增加主键,都支持in-place,重建表,可并发DML,并不仅仅只修改元数据。
删除主键 不支持并发DML和in-place,并不仅仅只修改元数据。

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

修改主键需要重建表,因为 innodb 是按主键聚簇存储的,需要大量数据重组操作,需要复制表,因此,最好在创建表时定义主键,而不是稍后发出ALTER TABLE … ADD PRIMARY KEY
如果你创建一个没有主键的表,InnoDB会为你选择一个,它可以是在NOT NULL列上定义的第一个UNIQUE键,或者是系统生成的键。 为避免不确定性以及额外隐藏列的潜在空间要求,请将PRIMARY KEY子句指定为CREATE TABLE语句的一部分。

主键修改过程:
MySQL通过将原始表中的现有数据复制到具有所需索引结构的临时表来创建新的聚簇索引。 将数据完全复制到临时表后,把原始表重命名为一个新的临时表,然后把刚才的临时表重命名为原始表名,然后删除原始表。

14.13.1 Online DDL Operations
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html


Online DDL 字段操作

除了改变字段类型以外的字段操作,均支持并发 DML。所有的操作都支持 in-place 的方式。

增加字段

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

注意:添加自增列时不允许并发DML。
尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作。当添加列是auto-increment,不允许DML并发

删除字段

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;

删除列需要进行大量数据重组,是一项开销很大的操作。

14.13.1 Online DDL Operations
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

14.13 InnoDB 在线 DDL - 官方文档的中文翻译
https://zhuanlan.zhihu.com/p/40443907


索引

show index from table查看索引

show index from table_name;

show keys from table_name;

Non_unique 是否非唯一,0不是,1是
Key_name 索引的名称。
Seq_in_index 索引中的列序列号,从1开始。
Column_name 列名称。
Collation 列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。
Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。
Sub_part 如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。
Packed 指示关键字如何被压缩。如果没有被压缩,则为NULL。
Null 如果列含有NULL,则含有YES。如果没有,则该列含有NO。
Index_type 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

MariaDB [uds]> show index from user_address;
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_address |          0 | PRIMARY     |            1 | id          | A         |        2807 |     NULL | NULL   |      | BTREE      |         |               |
| user_address |          1 | user_id     |            1 | user_id     | A         |        2807 |     NULL | NULL   |      | BTREE      |         |               |
| user_address |          1 | province_id |            1 | province_id | A         |          56 |     NULL | NULL   | YES  | BTREE      |         |               |
| user_address |          1 | city_id     |            1 | city_id     | A         |         140 |     NULL | NULL   | YES  | BTREE      |         |               |
| user_address |          1 | region_id   |            1 | region_id   | A         |         255 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.012 sec)

创建表时添加索引

-- 车辆信息表
DROP TABLE IF EXISTS `vehicle_info`;
CREATE TABLE `vehicle_info` (
  `id`                    BIGINT                     NOT NULL AUTO_INCREMENT,
  `car_order_id`          BIGINT                     NOT NULL
  COMMENT '整车订单id',
  `car_order_no`          VARCHAR(32)                NOT NULL
  COMMENT '整车订单订单号',
  `vehicle_id`            VARCHAR(32)
  COMMENT '车辆id',
  `vin_code`              VARCHAR(40)
  COMMENT '车辆vin码',
  `create_time`           DATETIME                   NOT NULL               DEFAULT CURRENT_TIMESTAMP
  COMMENT '创建时间',
  `update_time`           TIMESTAMP                  NOT NULL
  COMMENT '修改时间',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`car_order_id`) REFERENCES `car_order` (`id`),
  UNIQUE KEY (`car_order_id`),
  KEY (`car_order_no`),
  KEY (`vehicle_id`),
  KEY (`vin_code`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

create index在现有表上添加索引

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

alter table在现有表上添加索引

或者使用Alter语法:

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE [KEY|INDEX] [index_name](column_list)
ALTER TABLE table_name ADD PRIMARY KEY [index_name](column_list)

例如:

alter table user add unique (mobile, country_code);
alter table user add unique key (mobile, country_code);
alter table user add unique index (mobile, country_code);
alter table user add unique index mobile(mobile, country_code);
alter table page_view_transaction add index `pathname` (`pathname`(255));
alter table page_view_transaction add index host (host);

删除索引

DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

同一条alter语句中删除索引,同时添加索引

ALTER TABLE `dbname`.`table_name`
  DROP INDEX `idx_tppa_userid`,
  ADD UNIQUE `idx_tppa_userid` USING BTREE (`user_id`) comment '';

前缀索引(字符串最大768字节)

MySQL 的 varchar/text 索引只支持不超过768个字节,过长时可以指定前缀
mysql最大索引 768 个字节, utf8 占 3 个字节,768/3=256
mysql最大索引 768 个字节, utf8mb4 占 4个字节,768/4=192

ALTER TABLE table_name ADD index index_name (column_name(prefix_length));
alter table page_view add index pathname (`pathname`(255));

MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)

key和index区别

KEY
key 是数据库的物理结构,它包含两层意义,一是约束(偏重于约束和规范数据库的结构完整性),二是索引(辅助查询用的)。包括primary key, unique key, foreign key 等。

  • primary key 有两个作用,一是约束作用(constraint),用来规范一个存储主键和唯一性,但同时也在此key上建立了一个index;
  • unique key 也有两个作用,一是约束作用(constraint),规范数据的唯一性,但同时也在这个key上建立了一个index;
  • foreign key也有两个作用,一是约束作用(constraint),规范数据的引用完整性,但同时也在这个key上建立了一个index;
    MySQL中的key是同时具有constraint和index的意义。

另外,在MySQL中,对于一个Primary Key的列,MySQL已经自动对其建立了Unique Index,无需重复再在上面建立索引了。

INDEX
index是数据库的物理结构,它只是辅助查询的,它创建时会在另外的表空间(mysql中的innodb表空间)以一个类似目录的结构存储。索引要分类的话,分为前缀索引、全文本索引等;因此,索引只是索引,它不会去约束索引的字段的行为。


查看建表语句

show create table 查看建表语句

show create table table_name;

show create database 查看建库语句

show create database database_name;

desc 查看列

desc table_name;

alter table

修改表默认字符集和所有字符列的字符集

只修改表默认字符集

alter table page_view default charset utf8;

修改表默认字符集 同时 把所有字符列(CHAR,VARCHAR,TEXT)改为新的字符集

alter table page_view convert to charset utf8;
-- 同时修改字符序
alter table page_view convert to charset utf8 COLLATE utf8_general_ci;

drop column 删除列

ALTER TABLE table_name
DROP COLUMN field_name;

或者不加column关键字也行

ALTER TABLE table_name
DROP field_name;

注意要删除的列不能是约束的一部分,比如group_type是多列唯一约束的其中一列,则删除时提示找不到列:
ERROR 1072 (42000): Key column ‘group_type’ doesn’t exist in table
需要先将唯一约束删除才能继续。

add column 添加列

alter table table_name
add column column_name varchar(30);

alter table table_name
add column `data_type` TINYINT NOT NULL DEFAULT 1 COMMENT '数据类型 0:unknown 1:male 2:owner';

first/after 添加列时指定字段顺序

FIRST,添加为第一列
AFTER col_name,添加到指定列名后
默认是添加为最后一列

ALTER TABLE user
ADD column_name varchar(128) null COMMENT '姓名' FIRST;

ALTER TABLE user
ADD column_name varchar(128) null COMMENT '姓名' AFTER id;

https://dev.mysql.com/doc/refman/5.6/en/alter-table.html#alter-table-add-drop-column

同时添加/删除/修改列和索引

可在同一个 ALTER TABLE 语句中同时指定多个修改子句,例如添加多列,或者添加多列、删除多列、添加索引等组合在一起,都可以:

1、同时删除多列

alter table user
drop column email,
drop column education,
drop column job,
drop column marriage,
drop column phone;

2、列和索引一起删除:

alter table user
  drop COLUMN user_uuid,
  drop COLUMN code,
  drop COLUMN name,
  drop COLUMN age,
  drop KEY user_uuid;

3、同时添加多列和索引

ALTER TABLE table_name
  ADD COLUMN `uuid` BIGINT NOT NULL AFTER `id`,
  ADD COLUMN `code` VARCHAR(10) COMMENT '编码' AFTER uuid,
  ADD COLUMN `name` VARCHAR(20) COMMENT '名字' AFTER code,
  ADD UNIQUE KEY (uuid);

4、同时添加多列、修改列、添加索引

ALTER TABLE table_name
  ADD COLUMN `uuid` BIGINT NOT NULL AFTER `id`,
  ADD COLUMN `code` VARCHAR(10) COMMENT '编码' AFTER uuid,
  ADD COLUMN `name` VARCHAR(20) COMMENT '名字' AFTER code,
  MODIFY COLUMN `enabled` BOOLEAN NOT NULL DEFAULT TRUE,
  ADD UNIQUE KEY (uuid);

change column 修改列名

修改列名

alter table table_name
CHANGE COLUMN old_name new_name BIGINT NOT NULL COMMENT '用户id'

modify column 调整列顺序

column_name 放到 column_name2 后面

alter table table_name
modify column column_name tinyint(4) NOT NULL DEFAULT '0' after column_name2;

modify column 修改列类型

无论 column_name 原来是什么类型,直接改为想要的类型

alter table table_name
modify column column_name varchar(22);

alter table user_employee_info
modify column en_title varchar(1024) comment '英文title',
modify column cn_title varchar(1024) comment '中文title';

modify column 修改列注释

alter table table_name
MODIFY column column_name tinyint default '0' not null comment '用户身份, 0未知, 1非车主, 2意向金车主, 3定金车主, 4共同车主, 5正式车主'

modify column 修改列为大小写敏感的

修改表字段属性为大小写敏感的,即把列的字符序改为大小写敏感的字符序

ALTER TABLE table_name
MODIFY COLUMN code varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin not null comment '大小写敏感的code';

外键约束

可以在创建表时规定约束(通过 CREATE TABLE 语句),或者在表创建之后也可以(通过 ALTER TABLE 语句)

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
FOREIGN KEY 约束可定义在列级,也可以定义在表级。
CONSTRAINT关键字可为约束命名。
ON DELETE CASCADE 声明级联删除
ON UPDATE CASCADE 声明级联更新

定义表时添加外键约束

下面是postgresql中的一个建表语句:

CREATE TABLE "ais"."re_ais_opencloseclass_agent" (
"agent_id" int4 NOT NULL,
"airline" varchar(2) COLLATE "default" NOT NULL,
"savetime" int8 NOT NULL,
"saveuser" text COLLATE "default" NOT NULL,
CONSTRAINT "idx_re_ais_opencloseclass_agent_pk" PRIMARY KEY ("agent_id"),
CONSTRAINT "re_ais_opencloseclass_agent_agent_id_fkey1" FOREIGN KEY ("agent_id") REFERENCES "ais"."re_ais_base_agent" ("agent_id") ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT "re_ais_opencloseclass_agent_agent_id_airline_key" UNIQUE ("agent_id", "airline")
)

在现有表上添加外键约束

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

或者:

alter table orders
add FOREIGN KEY user_id_fk(user_id) REFERENCES user(id);

修改约束

若要修改主键约束、外键约束、唯一约束、检查约束,必须先删除现有的约束,然后再用新定义重新创建该约束。

删除外键约束

ALTER TABLE table_name
DROP FOREIGN KEY foreign_key_name[1,...n]

唯一约束

唯一键约束,可以在一个列上添加约束,也可以在多个列上添加唯一约束。

在一列上添加唯一约束,主要是让该列在表中只能有唯一的一行,例如注册邮箱时的邮箱名、手机号等信息

创建表时添加唯一约束

在username列上添加唯一约束

CREATE TABLE `t_user` (
    `Id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(18) NOT NULL unique,
    `password` varchar(18) NOT NULL,
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1018 DEFAULT CHARSET=gbk;

在多列上添加唯一约束

CREATE TABLE `jw_resource` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `resource_name` VARCHAR(128) DEFAULT NULL,
  `resource_type` TINYINT(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `resource_name` (`resource_name`,`resource_type`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk;

在已有表上添加唯一约束

ALTER TABLE `t_user` ADD unique(`username`);

或者:

create unique index UserNameIndex on 't_user' ('username');

在多列上添加唯一约束:

ALTER TABLE table_name
ADD UNIQUE KEY index_name(resource_name, resource_type);

查看唯一约束

show index from table_name;

修改唯一约束

必须先删除唯一约束,再添加唯一约束

删除唯一约束

注意唯一约束用index关键字,因为唯一约束其实就是索引

ALTER TABLE table_name DROP INDEX index_name;

MySQL中添加唯一约束和联合唯一约束
https://blog.csdn.net/yumushui/article/details/38960619


上一篇 面试准备02-Java集合框架

下一篇 LeetCode.033.Search in Rotated Sorted Array 搜索旋转排序数组

阅读
评论
27,385
阅读预计116分钟
创建日期 2018-03-08
修改日期 2020-10-20
类别
目录
  1. 设计与实践
    1. 存储树形结构
    2. 用大间隔int来表示状态码
    3. 给一个很大的线上表加字段并刷数据
  2. 工具
    1. Yearning MYSQL SQL语句审核平台
  3. MySQL数据类型
    1. Mysql字符集和字符序
      1. mysql大小写敏感
      2. utf8和utf8mb4
      3. 查看mysql支持的字符集和字符序
      4. variables character_set_ 当前字符集
      5. variables collation_ 当前字符序
      6. MySQL中的字符集转换过程
      7. 创建表时指定字符集
    2. char/varchar/text
      1. TEXT不支持默认值
    3. int(5)括号内的长度是显示长度
    4. tinyint/int/bigint等数据长度
    5. default null与default ‘’
      1. 为什么建议MySQL列属性尽量用 NOT NULL
    6. AUTO_INCREMENT 自增值设置与修改
    7. TIMESTAMP的默认值和自动更新
    8. enum 枚举类型
      1. 创建带有enum类型字段的表
      2. 修改enum字段的枚举值
    9. DECIMAL 小数
  4. MySQL系统变量
    1. 查看mysql版本号
      1. 未连接到MySQL服务器mysql -v
      2. select version();
      3. status; 或 \s
    2. sql_mode
      1. 查看当前sql_mode
      2. 设置sql_mode
      3. 常用sql_mode
        1. ONLY_FULL_GROUP_BY
        2. ANSI_QUOTES
        3. STRICT_TRANS_TABLES
        4. NO_AUTO_CREATE_USER
    3. 事务隔离级别
      1. 查看当前会话隔离级别
      2. 查看系统当前隔离级别
      3. 设置当前会话隔离级别
      4. 设置系统当前隔离级别
    4. show status 查看所有status
      1. Max_used_connections 查看最大并发连接数
      2. Threads_connected 查看当前实时连接数
    5. Server System Variables 服务端系统变量
      1. show variables 查看所有variable
      2. autocommit 自动提交是否打开
      3. foreign_key_checks 外键检查
      4. max_connections 查看my.cnf中配置的最大连接数
      5. wait_timeout 非交互连接超时时间
      6. interactive_timeout
      7. max_allowed_packet(4MB)
        1. PacketTooBigException
      8. innodb_lock_wait_timeout(50秒)
      9. mysql时区
        1. time_zone 当前时区
        2. system_time_zone 系统时区
        3. 设置mysql系统时区
        4. select now()查看mysql系统时间
      10. datadir 物理文件存放位置
      11. bind_address 服务器绑定ip
    6. show plugins 查看所有插件
  5. information_schema
    1. processlist
      1. processlist表字段含义
      2. processlist表command字段取值
      3. 查看各ip连接数
      4. show processlist 查正在执行的连接(线程)
    2. columns
      1. 从columns表中查表有哪些索引列
    3. tables
      1. create_time 表创建时间
      2. data_length 表大小
      3. index_length 索引大小
    4. innodb_trx
      1. innodb_trx表各字段含义
  6. 工具
    1. while循环批量插入数据
    2. mysqldump数据库(表)导入导出
      1. 导出数据库/表
      2. 导出为多条insert语句
      3. 导入数据库/表
      4. windows上导出文件无法导入
      5. mysqldump命令详解
    3. 执行sql脚本
    4. 拷贝创建新表
      1. 创建新表同时拷贝数据(丢失约束等)
      2. 只拷贝表结构(丢失约束等)
      3. 只拷贝表结构(保留约束等)
      4. 创建新表并复制数据(保留约束等)
  7. MySQL函数和操作符
    1. 日期和时间函数
      1. now()
      2. now()和sysdate()区别
      3. FROM_UNIXTIME 时间戳转日期
      4. UNIX_TIMESTAMP 日期转时间戳
      5. 根据生日计算年龄
    2. 信息函数
      1. LAST_INSERT_ID()
    3. 比较操作符
      1. coalesce()返回第一个非null值
    4. 字符串函数
      1. length()字符串字节长度
      2. mysql随机字符串/随机姓名
      3. substring_index()字符串分隔
      4. concat()字符串串接/int转字符串
      5. lpad(str,len,padstr)字符串左填充
    5. 聚集函数
      1. group_concat()查询转为逗号分隔列
    6. 类型转换函数
      1. cast()字符串转int
      2. convert()类型转换
    7. 用户自定义变量
      1. 设置自定义变量
      2. 使用自定义变量
  8. DQL
    1. SQL优化
      1. 去掉表字段上的unix_timestamp时间函数
      2. create_time排序改为id排序避免回表
      3. 不要使用offset,limit循环分页
      4. 两表关联的连接表如何建索引
    2. like binary 区分大小写
    3. order by field按指定顺序排序
    4. exists
      1. 重复数据取最后更新的
      2. 重复数据取最后插入的
      3. 重复数据取最大/最小
    5. distinct多列
    6. case when 条件表达式
      1. case when 查及格和不及格人数
      2. case根据列in分组
      3. update中使用case when赋值
    7. 不等号
      1. OR条件没加括号直接和其他条件AND
    8. join
      1. join和inner join是完全相同的
      2. 连接中的on和where
      3. where逗号分隔等价于join或inner join
      4. 用left join代替not in
      5. mysql中cross join和join以及inner join完全相同
      6. mysql中没有full join全连接
    9. group by
      1. join 后 group by 聚合为拼接串
    10. \G 列变行
  9. DCL
    1. 手动提交事务
    2. mysql start transaction 和 set autocommit = 0 的区别
  10. DML
    1. insert
      1. on duplicate key update
      2. insert多条记录
      3. insert select 表间数据复制
        1. 同一数据库内insert select
        2. 不同数据库间insert select
        3. 表结构不相同insert select
    2. delete
      1. 清空表truncate和delete
      2. 外键导致删除失败
      3. delete语句使用别名
      4. 删除重复行
        1. 删除所有重复行
        2. 重复行只保留一条
      5. delete join
    3. update
      1. update多列
      2. update select join 根据关联查询更新
      3. update case when 条件表达式更新
      4. specified twice both target and source
      5. Every derived table must have its own alias
      6. update返回值与useAffectedRows
  11. DDL
    1. DDL与事务
      1. MySQL不支持事务型DDL
      2. 常见数据库的事务DDL支持情况
      3. MySQL事务中混合DDL会怎样?
    2. MySQL的三种DDL处理方式
      1. Copy Table(可读不可写)
      2. Inplace(可读不可写)
      3. Online(可读可写,5.6及以上)
    3. MySQL5.6 中的 InnoDB Online DDL
      1. Online DDL 选项
      2. Online DDL 索引操作
        1. MySQL给已存在的表增加索引时会锁表吗?
      3. Online DDL 主键操作
      4. Online DDL 字段操作
    4. 索引
      1. show index from table查看索引
      2. 创建表时添加索引
      3. create index在现有表上添加索引
      4. alter table在现有表上添加索引
      5. 删除索引
      6. 前缀索引(字符串最大768字节)
      7. key和index区别
    5. 查看建表语句
      1. show create table 查看建表语句
      2. show create database 查看建库语句
      3. desc 查看列
    6. alter table
      1. 修改表默认字符集和所有字符列的字符集
      2. drop column 删除列
      3. add column 添加列
      4. first/after 添加列时指定字段顺序
      5. 同时添加/删除/修改列和索引
      6. change column 修改列名
      7. modify column 调整列顺序
      8. modify column 修改列类型
      9. modify column 修改列注释
      10. modify column 修改列为大小写敏感的
    7. 外键约束
      1. 定义表时添加外键约束
      2. 在现有表上添加外键约束
      3. 修改约束
      4. 删除外键约束
    8. 唯一约束
      1. 创建表时添加唯一约束
      2. 在已有表上添加唯一约束
      3. 查看唯一约束
      4. 修改唯一约束
      5. 删除唯一约束

页面信息

location:
protocol:
host:
hostname:
origin:
pathname:
href:
document:
referrer:
navigator:
platform:
userAgent:

评论