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

MySQL-使用笔记

mysql日常使用笔记与备忘


数据类型

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

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 字符等等。

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的默认值和自动更新

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类型字段的表:

CREATE TABLE `enum_tests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` enum('pending','success','closed') COLLATE utf8mb4_unicode_ci NOT NULL,
  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


sql易错

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

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

事务隔离级别

查看当前会话隔离级别

select @@tx_isolation;

查看系统当前隔离级别

select @@global.tx_isolation;

设置当前会话隔离级别

set session transaction isolatin level repeatable read;

设置系统当前隔离级别

set global transaction isolation level repeatable read;


show status 查看所有status

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


datadir 物理文件存放位置

show global variables like "%datadir%";

character_set_ 查看mysql字符集

SHOW VARIABLES LIKE 'character_set_%';
没设置的情况下,默认就是utf-8

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)

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)地址

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数据库(表)导入导出

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

导出数据库/表

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

导入数据库/表

(1)导入数据库
在本地数据库中创建相对应导出的数据库mydb同名的数据库:
mysql> 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


mysql单服务器内表间数据复制

同一数据库内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 db2table2;
# 前五条纪录
insert into tdb1.able1 select top 5 * from db2table2;

表结构不相同insert select

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

insert into db2.tb2(字段1,字段2,字段3……)
select  字段1‘,字段2’,字段3‘,……  from db1.tb1;

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


拷贝创建新表

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

复制表结构及数据到新表
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


清空表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……


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


LAST_INSERT_ID()

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

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

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

coalesce()返回第一个非null值

返回列表中的第一个非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

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

select group_concat(id)
from user


基础sql

手动提交事务

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

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;

delete

外键导致删除失败

比如从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

update

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;

update多列

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

update条件列

FROM manager AS m2
改为
FROM (select * from manager) AS m2

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


update select根据查询更新

当你希望更新一批值,且值是通过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

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


不等号

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

索引

查看索引

show index from table;

创建表时添加索引

-- 车辆信息表
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 idx_id    on mydb.user(id);

或者使用Alter语法

ALTER TABLE user ADD INDEX(id);
ALTER TABLE user ADD INDEX idx_id (id);

删除索引

DROP INDEX index_name ON talbe_name;
DROP INDEX idx_vin_code ON uds.vehicle_info;

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 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
需要先将唯一约束删除才能继续。

同时删除多列

alter table user
drop column email,
drop column social_id,
drop column passport_id,
drop column education,
drop column job,
drop column marriage,
drop column phone,
drop column region,

添加列

alter table 表名 add column 列名 varchar(30);
alter table 表名 add column `data_type` TINYINT NOT NULL DEFAULT 1 COMMENT '数据类型 0:unknown 1:male 2:owner';

添加列时指定字段顺序

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 table_name CHANGE COLUMN old_name new_name BIGINT NOT NULL COMMENT '用户id'

调整列顺序

column_name 放到 column_name2 后面

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

修改列类型

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

alter table table_name modify column column_name varchar(22);

修改列注释

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

外键约束

可以在创建表时规定约束(通过 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


删除重复行

删除所有重复行

用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) 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都支持这种方式。

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

delete from php_user where
username in (select username from ( select username from php_user group by username having count(username)>1) a)
and id not in ( select min(id) from (select min(id) as id from php_user group by username having count(username)>1 ) b)

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

下一篇 LeetCode.033.Search in Rotated Sorted Array

阅读
11,584
阅读预计49分钟
创建日期 2018-03-08
修改日期 2019-01-10
类别
目录
  1. 数据类型
    1. int(5)括号内的长度的含义
    2. tinyint/int/bigint等数据长度
    3. utf8和utf8mb4
    4. default null与default ‘’
    5. AUTO_INCREMENT 自增值设置与修改
    6. TIMESTAMP的默认值和自动更新
    7. enum 枚举类型
      1. 创建带有enum类型字段的表:
      2. 修改enum字段的枚举值:
    8. sql易错
      1. OR条件没加括号直接和其他条件AND
  2. mysql系统变量
    1. 查看mysql版本号
      1. 未连接到MySQL服务器mysql -v
      2. select version();
      3. status; 或 \s
    2. 事务隔离级别
      1. 查看当前会话隔离级别
      2. 查看系统当前隔离级别
      3. 设置当前会话隔离级别
      4. 设置系统当前隔离级别
    3. show status 查看所有status
      1. Max_used_connections 查看最大并发连接数
      2. Threads_connected 查看当前实时连接数
    4. Server System Variables 服务端系统变量
      1. show variables 查看所有variable
      2. foreign_key_checks 外键检查
      3. max_connections 查看my.cnf中配置的最大连接数
      4. datadir 物理文件存放位置
      5. character_set_ 查看mysql字符集
      6. bind_address 服务器绑定ip
  3. 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表各字段含义
  4. 工具/函数
    1. mysqldump数据库(表)导入导出
      1. 导出数据库/表
      2. 导入数据库/表
      3. windows上导出文件无法导入
      4. mysqldump命令详解
    2. mysql单服务器内表间数据复制
      1. 同一数据库内insert select
      2. 不同数据库间insert select
      3. 表结构不相同insert select
    3. 拷贝创建新表
      1. 创建新表同时拷贝数据(丢失约束等)
      2. 只拷贝表结构(丢失约束等)
      3. 只拷贝表结构(保留约束等)
      4. 创建新表并复制数据(保留约束等)
    4. 清空表truncate和delete
    5. now()和sysdate()区别
    6. LAST_INSERT_ID()
    7. coalesce()返回第一个非null值
    8. group_concat()查询转为逗号分隔列
  5. 基础sql
    1. 手动提交事务
      1. mysql start transaction 和 set autocommit = 0 的区别
    2. select
      1. exists 重复数据取最后更新的
      2. exists 重复数据取最后插入的
      3. 重复数据取最大/最小
      4. distinct多列
      5. case when 查及格和不及格人数
    3. delete
      1. 外键导致删除失败
    4. update
      1. ON DUPLICATE KEY UPDATE
      2. update多列
      3. update条件列
      4. update select根据查询更新
      5. update返回值与useAffectedRows
    5. 不等号
    6. 索引
      1. 查看索引
      2. 创建表时添加索引
      3. 在现有表上添加索引
      4. 删除索引
      5. key和index区别
    7. 查看创建sql
      1. 查看创建表的sql
      2. 查看创建数据库的sql
      3. 查看表有哪些列
    8. 增加删除列
      1. 删除列
      2. 同时删除多列
      3. 添加列
      4. 添加列时指定字段顺序
      5. 修改列名
      6. 调整列顺序
      7. 修改列类型
      8. 修改列注释
    9. 外键约束
      1. 定义表时添加外键约束
      2. 在现有表上添加外键约束
      3. 修改约束
      4. 删除外键约束
    10. 唯一约束
      1. 创建表时添加唯一约束
      2. 在已有表上添加唯一约束
      3. 查看唯一约束
      4. 修改唯一约束
      5. 删除唯一约束
    11. 删除重复行
      1. 删除所有重复行
      2. 重复行只保留一条
百度推荐