当前位置 : 首页 » 文章分类 :  开发  »  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状态码。


工具

Yearning MYSQL SQL语句审核平台

https://guide.yearning.io/


数据类型

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


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


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 '%变量名%' 查看某个具体变量值

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/


工具

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;

然后在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;

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


函数和操作符

函数和操作符总索引
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


信息函数

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

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

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;

基础sql

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 才行。

手动提交事务

使用 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


select

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的最大的,还是会选出多条,所以就依赖于数据必须创建时间不同。

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

exists 重复数据取最后插入的

表结构同上,根据 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)

重复数据取最大/最小

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 查及格和不及格人数

用一个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;

不等号

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


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;

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


索引

查看索引

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 '';

前缀索引

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表空间)以一个类似目录的结构存储。索引要分类的话,分为前缀索引、全文本索引等;因此,索引只是索引,它不会去约束索引的字段的行为。


查看创建sql

查看创建表的sql

show create table table_name;

查看创建数据库的sql

show create database database_name;

查看表有哪些列

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 在旋转有序数组中搜索

阅读
评论
20,532
阅读预计89分钟
创建日期 2018-03-08
修改日期 2020-01-20
类别
目录
  1. 设计
    1. 存储树形结构
    2. 用大间隔int来表示状态码
  2. 工具
    1. Yearning MYSQL SQL语句审核平台
  3. 数据类型
    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 ‘’
    6. AUTO_INCREMENT 自增值设置与修改
    7. TIMESTAMP的默认值和自动更新
    8. enum 枚举类型
      1. 创建带有enum类型字段的表:
      2. 修改enum字段的枚举值:
  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. foreign_key_checks 外键检查
      3. max_connections 查看my.cnf中配置的最大连接数
      4. wait_timeout 非交互连接超时时间
      5. interactive_timeout
      6. max_allowed_packet(4MB)
        1. PacketTooBigException
      7. innodb_lock_wait_timeout(50秒)
      8. mysql时区
        1. time_zone 当前时区
        2. system_time_zone 系统时区
        3. 设置mysql系统时区
        4. select now()查看mysql系统时间
      9. datadir 物理文件存放位置
      10. 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. mysqldump数据库(表)导入导出
      1. 导出数据库/表
      2. 导出为多条insert语句
      3. 导入数据库/表
      4. windows上导出文件无法导入
      5. mysqldump命令详解
    2. 执行sql脚本
    3. 拷贝创建新表
      1. 创建新表同时拷贝数据(丢失约束等)
      2. 只拷贝表结构(丢失约束等)
      3. 只拷贝表结构(保留约束等)
      4. 创建新表并复制数据(保留约束等)
  7. 函数和操作符
    1. 日期和时间函数
      1. now()
      2. now()和sysdate()区别
      3. FROM_UNIXTIME 时间戳转日期
      4. UNIX_TIMESTAMP 日期转时间戳
    2. 信息函数
      1. LAST_INSERT_ID()
    3. 比较操作符
      1. coalesce()返回第一个非null值
    4. 字符串函数
      1. length()字符串字节长度
      2. substring_index()字符串分隔
      3. concat()字符串串接/int转字符串
      4. lpad(str,len,padstr)字符串左填充
    5. 聚集函数
      1. group_concat()查询转为逗号分隔列
    6. 类型转换函数
      1. cast()字符串转int
      2. convert()类型转换
    7. 用户自定义变量
      1. 设置自定义变量
      2. 使用自定义变量
  8. 基础sql
    1. sql优化
      1. 去掉表字段上的unix_timestamp时间函数
      2. create_time排序改为id排序避免回表
    2. 手动提交事务
      1. mysql start transaction 和 set autocommit = 0 的区别
    3. select
      1. like binary 区分大小写
      2. order by field按指定顺序排序
      3. exists 重复数据取最后更新的
      4. exists 重复数据取最后插入的
      5. 重复数据取最大/最小
      6. distinct多列
      7. case when 查及格和不及格人数
      8. 不等号
      9. OR条件没加括号直接和其他条件AND
    4. 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全连接
    5. insert
      1. on duplicate key update
      2. insert多条记录
      3. insert select 表间数据复制
        1. 同一数据库内insert select
        2. 不同数据库间insert select
        3. 表结构不相同insert select
    6. delete
      1. 清空表truncate和delete
      2. 外键导致删除失败
      3. delete语句使用别名
      4. 删除重复行
        1. 删除所有重复行
        2. 重复行只保留一条
      5. delete join
    7. update
      1. update多列
      2. update select join根据条件更新
      3. specified twice both target and source
      4. Every derived table must have its own alias
      5. update返回值与useAffectedRows
    8. 索引
      1. 查看索引
      2. 创建表时添加索引
      3. create index在现有表上添加索引
      4. alter table在现有表上添加索引
      5. 删除索引
      6. 前缀索引
      7. key和index区别
    9. 查看创建sql
      1. 查看创建表的sql
      2. 查看创建数据库的sql
      3. 查看表有哪些列
    10. 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 修改列为大小写敏感的
    11. 外键约束
      1. 定义表时添加外键约束
      2. 在现有表上添加外键约束
      3. 修改约束
      4. 删除外键约束
    12. 唯一约束
      1. 创建表时添加唯一约束
      2. 在已有表上添加唯一约束
      3. 查看唯一约束
      4. 修改唯一约束
      5. 删除唯一约束

页面信息

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

评论