当前位置 : 首页 » 文章分类 :  开发  »  MySQL-SQL语句

MySQL-SQL语句

MySQL SQL 语句

Chapter 13 SQL Statements
https://dev.mysql.com/doc/refman/5.7/en/sql-statements.html


DQL

select [ all | distinct ]  <目标列表达式 | 聚集函数>[别名]...
from <表名或视图名>[别名]...
[ where <条件表达式> ]
[ group by <列名> [having <条件表达式>]  ]
[ order by <列名> [ASC | DESC] ]

SQL优化


count(*)慢优化

对于没有过滤条件的 count(*)
MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count() 的时候会直接返回这个数,效率很高;
而 InnoDB 引擎就麻烦了,它执行 count(
) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。

id分段count后求和

sql1: select count(*) from user where score>90;
优化为按主键id分段count后相加
sql2:

select sum(cnt) from (
select count(*) cnt from table_a where id<1000000 and age is not null
union all
select count(*) cnt from table_a where id>=1000000 and id<2000000 and age is not null
) t;

2亿8千万数据,符合条件的约3000万
sql1 执行了6个小时还没结束

非精确count-information_schema.tables

从 information_schema.tables 查询 TABLE_ROWS
对于 InnoDB 存储引擎来说是个近似值。
select TABLE_ROWS from information_schema.tables where table_schema=’db_name’ and table_name=’table_a’;

非精确-show table status like

本质和从 information_schema.tables 查询 TABLE_ROWS 一样,对于 InnoDB 存储引擎来说是个近似值。

mysql> show table status like 'my_app_table_11111';
+--------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name               | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+--------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| my_app_table_11111 | InnoDB |      10 | Dynamic    | 145394 |           4536 |   659570688 |               0 |     28540928 |   7340032 |         340429 | 2021-06-27 21:49:33 | 2021-07-14 17:02:52 | NULL       | utf8_general_ci |     NULL |                |         |
+--------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+

非精确count-解析explain扫描行数

解析 explain 输出的 rows 字段

mysql> explain select * from vehicle_four_wheel;
+----+-------------+--------------------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | table_my_app       | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 140755 |   100.00 | NULL  |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+--------+----------+-------+

count(distinct)优化

sql1: select count(distinct person_no) from user where score>90;
优化为先查出distinct编号,再count
sql2: select count(*) from (select distinct(person_no) from user where score>90) t;

2亿8千万数据,符合条件的有大约1千万数据
sql1 执行了6个小时还没结束
sql2半小时执行完了。


int和varchar类型索引

当MySQL中字段为 int 类型时,搜索条件 where num=’123’ 与 where num=123 都可以使用该字段的索引
当MySQL中字段为 varchar 类型时,搜索条件 where num=’123’ 可以使用索引,where num=123 不可以使用索引

所以:字段类型不同会有隐式类型转换,可能导致索引失效

这一点也是MySQL int和varchar类型选择的一个依据:
1、除了 int 效率较高之外。
2、int类型字段建索引后可以匹配int和varchar条件,但varchar类型字段建索引后只能匹配varchar条件
如果某个字段存储的是int,那就在MySQL中定义为int,而不要使用varchar。


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

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

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

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

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

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

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


offset分页改写为where id条件

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

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

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

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

比如对外提供一个按 create_time 时间范围分页查询的接口
queryByCreateTimePageable(String t1, String t2, long offset, int count);
本来sql如下

SELECT *
FROM table t
WHERE create_time >= $t1
  AND create_time <= $t2
ORDER BY id ASC
LIMIT $offset, $count;

可以内部对其进行优化,将offset分页改写为 where id 条件

SELECT *
FROM table t
WHERE create_time >= t1
  AND create_time <= t2
  AND id >= (SELECT ID
             FROM table st force index (primary)
             WHERE create_time >= t1
               AND create_time <= t2
             ORDER BY id ASC
             LIMIT $offset,1)
ORDER BY id ASC
LIMIT $count;

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

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

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

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

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

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

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


干预索引

use index(a,b) 限制索引范围

有时我们在数据表里建立了很多索引,当MySQL对索引进行选择时,这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引,而不是全部的索引,这就需要用到USE INDEX对查询语句进行设置。
SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2) …
无论在TABLE1中已经建立了多少个索引,MySQL在选择索引时,只考虑在FIELD1和FIELD2上建立的索引。

ignore index(a,b) 忽略索引

如果我们要考虑的索引很多,而不被使用的索引又很少时,可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引,而使用IGNORE INDEX是选择不被考虑的索引。
SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
在上面的SQL语句中,TABLE1表中只有FIELD1和FIELD2上的索引不被使用。

force index(a) 强制使用索引

有时我们希望MySQL必须要使用某一个索引(由于MySQL在查询时只能使用一个索引,因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。
SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
以上的SQL语句只使用建立在FIELD1上的索引,而不使用其它字段上的索引。


like

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,不区分大小写

like escape 转义通配符

like 子句中 % 匹配多个字符, _ 匹配单个字符。
如果要匹配的内容本身包含 %_ 怎么办呢?

可以使用 escape 指定转义字符,转义字符后面的 % 或 _ 就不作为通配符了,注意前面没有转义字符的%和_仍然起通配符作用

select * from user where name like ‘%li/_%’ escape ‘/‘;
select * from user where name like ‘%li/%%’ escape ‘/‘;


regexp 正则匹配

^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性,^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了 RegExp 对象的 Multiline 属性,$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
. 匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符,请使用像 ‘[.\n]’ 的模式。
[...] 字符集合。匹配所包含的任意一个字符。例如, ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^...] 负值字符集合。匹配未包含的任意字符。例如, ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如,’z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。
* 匹配前面的子表达式零次或多次。例如,zo* 能匹配 “z” 以及 “zoo”。* 等价于 {0,}
+ 匹配前面的子表达式一次或多次。例如,’zo+’ 能匹配 “zo” 以及 “zoo”,但不能匹配 “z”。+ 等价于 {1,}
{n} n 是一个非负整数。匹配确定的 n 次。例如,’o{2}’ 不能匹配 “Bob” 中的 ‘o’,但是能匹配 “food” 中的两个 o。
{n,m} m 和 n 均为非负整数,其中n <= m。最少匹配 n 次且最多匹配 m 次。

例:

1、查找 long 型字符串 create_time 以 000 结尾的记录

select * from user where create_time regexp '000$';

2、查找 name 以 ‘张’ 开头的记录

select * from user where name regexp '^张';

3、查找 name 包含 ‘小’ 的记录

select * from user where name regexp '小';

12.8.2 Regular Expressions
https://dev.mysql.com/doc/refman/5.7/en/regexp.html


order by field按指定顺序排序

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

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

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

按指定的姓名顺序排序

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

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


exists

重复数据取最后更新的

表结构定义:

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

存储的是 user_id 的 role 值历史。

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

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

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

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


重复数据取最后插入的

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

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

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

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

重复数据取最大/最小

ID相同取最小TYPE

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

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

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

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


case when 条件表达式

case when 查及格和不及格人数

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

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

case根据列in分组

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

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

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

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

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

update中使用case when赋值

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

sql 如下:

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

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


不等号

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


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

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

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

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

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

join

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

joininner join是完全相同的

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

连接中的on和where

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

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

where逗号分隔等价于joininner join

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

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

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

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

但更推荐使用join语法

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

用left join代替not in

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

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

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

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

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

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

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

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

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

select * from A cross join B;

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

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

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

和使用 inner joinjoin 完全相同。

mysql中没有full join全连接

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

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

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

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


union

MySQL UNION 用于把来自多个 SELECT 语句的结果组合到一个结果集合中。语法为:

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
...

在多个 SELECT 语句中,对应的列应该具有相同的字段属性,且第一个 SELECT 语句中被使用的字段名称也被用于结果的字段名称。

UNION 常用于数据类似的两张或多张表查询,如分库分表中的数据分表,或者是按时间划分的数据历史表等。

注意:
1、union 会去除结果中的重复记录,这里的重复指的是所有字段完全相同,有任意字段不同也算作不同记录。
2、第一个 SELECT 语句中被使用的字段名称也被用于结果的字段名称
3、各 SELECT 语句字段名称可以不同,但字段属性必须一致

union与union all区别

当使用 UNION 时,MySQL 会把结果集中重复的记录删掉,而使用 UNION ALL ,MySQL 会把所有的记录返回,且效率高于 UNION。

使用 UNION ALL 的时候,只是单纯的把各个查询组合到一起而不会去判断数据是否重复。因此,当确定查询结果中不会有重复数据或者不需要去掉重复数据的时候,应当使用 UNION ALL 以提高查询效率。

为什么用 union all 效率更高?
因为他不需要去重

MySQL UNION 与 UNION ALL 语法与用法
https://www.cnblogs.com/zhangminghui/p/4408546.html

union all 多表count求和

select count(*) from t_pic_record_0 where groupid='collection_test_3'
union all
select count(*) from t_pic_record_1 where groupid='collection_test_3'
union all
select count(*) from t_pic_record_2 where groupid='collection_test_3';

结果为

+----------+
| count(*) |
+----------+
|        6 |
|        5 |
|        5 |
+----------+

要求和的话在外层再加一次 sum() 或 count() 即可

select sum(cnt)
from (
    select count(*) cnt from t_pic_record_0 where groupid='collection_test_3'
    union all
    select count(*) cnt from t_pic_record_1 where groupid='collection_test_3'
    union all
    select count(*) cnt from t_pic_record_2 where groupid='collection_test_3'
) t_cnt;

结果

+----------+
| sum(cnt) |
+----------+
|       16 |
+----------+

distinct多列

select distinct a,b,c from tableA where c > const;
等价于
select a,b,c from tableA where c > const group by a,b,c;

8.2.1.16 DISTINCT Optimization
https://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html

注意此时是将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


group by

group by 将查询结果按某一列或多列的值分组,值相等的为一组

where 子句中不能使用聚集函数作为条件表达式

join 后 group by 聚合为拼接串

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

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

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


select字段必须是分组字段或聚集函数

select 中指定的字段要么就要包含在 group by 语句的后面,作为分组的依据;要么就要被包含在聚合函数中
含有 group by 子句的 select 中指定的字段必须是“分组依据字段”,其他字段若想出现在 select 中则必须包含在聚合函数中

order字段必须是分组字段或聚集函数

group by后的order by子句中的字段必须是“分组依据字段”,其他字段若想出现在select中则必须包含在聚合函数中
例如

SELECT errortype, isschchg, count(*)
FROM "ais"."re_ais_dcm_changeresult"
WHERE "airlinecode" = 'HU'
group by errortype, isschchg
ORDER BY savedate

执行时报错:ERROR: column “re_ais_dcm_changeresult.savedate” must appear in the GROUP BY clause or be used in an aggregate function
因为savedate字段不是group by分组字段,也不在聚集函数中,若将order by子句改为:ORDER BY count(savedate),即将savedate放入聚集函数则没问题。

ERROR 1055 (42000) sql_mode=only_full_group_by

select *
from user
group by name;

报错
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘x.x’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因是
SQL 标准中不允许 SELECT 列表,HAVING 条件语句,或 ORDER BY 语句中出现分组字段和聚集函数之外的字段。而 MySQL 中有一个状态 ONLY_FULL_GROUP_BY 来标识是否遵从这一标准,默认为开启状态。

解决
1、关闭 ONLY_FULL_GROUP_BY
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));
要想永久生效,需要修改 my.cnf 配置并重启mysql,

2、通过 ANY_VALUE() 来改造查询语句以避免报错
使用 ANY_VALUE() 包裹的值不会被检查,跳过该错误。
SELECT gender, ANY_VALUE(last_name)
FROM employees
GROUP BY gender

having子句中不能使用列别名

group by 或 having 子句中不能使用列的别名
例:

SELECT a as c, sum(b)
FROM test
GROUP BY c

会提示错误,group by c 改成 group by a 就行。

我在开发中遇到的错误:

[ERROR] 2016-07-05 11:01:04,694 method:com.masikkk.myservice.integratedAnalyse(AgentBookWs.java:83)
integerated analyse query failed!
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: column "ras_agent_psgbook.bookingoffice" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 8
### The error may exist in mybatis/PsgBookMapper.xml
### The error may involve com.masikkk.dao.PsgBookMapper.selectIntegratedAnalyseByOffice-Inline
### The error occurred while setting parameters
### SQL: select bookingoffice as operatingOffice, oc as oc,count(1) as addNum    from ras_agent_psgbook    where    oc=? and depdate between ? and ? and bookingdate between ? and ?                  and (depdate > ? or (depdate=? and deptime > ?))                  group by operatingoffice,oc      order by addNum desc
### Cause: org.postgresql.util.PSQLException: ERROR: column "ras_agent_psgbook.bookingoffice" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 8
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "ras_agent_psgbook.bookingoffice" must appear in the GROUP BY clause or be used in an aggregate function

select中给bookingoffice设定别名为operatingOffice,group by中使用别名operatingoffice导致SQL报错。


\G 列变行

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


DCL

手动提交事务

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

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

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

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

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

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

mysql start transaction 和 set autocommit = 0 的区别

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

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

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

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

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


show 语句

show status 查看服务端状态变量

13.7.5.35 SHOW STATUS Statement
https://dev.mysql.com/doc/refman/5.7/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 服务器工作了多少秒。


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

DML

insert

on duplicate key update

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

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

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

insert多条记录

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

insert select 表间数据复制

同一数据库内insert select

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

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

不同数据库间insert select

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

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

表结构不相同insert select

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

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

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


delete

清空表truncate和delete

truncate table wp_comments;
delete from wp_comments;

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

外键导致删除失败

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

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

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

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

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

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

delete语句使用别名

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

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

或者不使用别名:

delete from table where column = value;

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


删除重复行

删除所有重复行

用in

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

或者用exists

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

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

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

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

重复行只保留一条

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

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

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

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

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

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

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

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

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

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

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

delete join

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

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

update

update多列

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

update join 跨表关联更新

在 MySQL 中, 我们也可以在 UPDATE 语句中使用 JOIN 子句执行跨表更新

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2,
    T2.C3 = expr
WHERE condition

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

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

解决思路 2:

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

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

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

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

例如:

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

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

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

update case when 条件表达式更新

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

sql 如下:

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

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


specified twice both target and source

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

例如:

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

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

改为:

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

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

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

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

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


Every derived table must have its own alias

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

例如

SELECT id
FROM (
    SELECT id
    FROM user
);

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

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

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

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

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

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

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


update返回值与useAffectedRows

比如如下mybatis sql语句:

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

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

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

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

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

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

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

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


DDL

字段名避免使用mysql关键字

创建一个字典表, 键 使用了 key, 加反引号执行 DDL 的时候没问题

CREATE TABLE `dict`(
    `id`                 bigint(20)   NOT NULL AUTO_INCREMENT,
    `key`                varchar(255) NOT NULL,
    `value`              text         NOT NULL,
    `description`        text         DEFAULT NULL,
    `enabled`            BOOLEAN      DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

但 jpa auto ddl 时生成的 sql 字段名是不带反引号的,执行sql就会报错, key varchar(255) 处会报错,和 mysql 创建索引的关键字 KEY 冲突了。

create table dict
(
    id                 bigint not null auto_increment,
    description        varchar(255),
    key varchar(255),
    status             varchar(255) default 'enabled',
    value              varchar(255),
    primary key (id)
) engine = InnoDB

DDL与事务

MySQL不支持事务型DDL

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

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

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

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

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

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

MySQL事务中混合DDL会怎样?

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

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

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

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


MySQL的三种DDL处理方式

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

Copy Table(可读不可写)

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

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

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

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

Inplace(可读不可写)

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

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

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


MySQL5.6 中的 InnoDB Online DDL

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

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

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

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

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

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

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

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

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

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

Online DDL 选项

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

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

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

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

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

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


Online DDL 索引操作

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

创建和增加二级索引

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

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

删除索引

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

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

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

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

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


Online DDL 主键操作

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

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

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

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

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


Online DDL 字段操作

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

增加字段

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

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

删除字段

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

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

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

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


第三方Schema迁移工具

pt-online-schema-change

pt-online-schema-change
https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

gh-ost

github / gh-ost
https://github.com/github/gh-ost

不需要触发器支持的 MySQL 在线更改表结构的工具

所有在线表结构修改工具的操作方式都类似:
创建与原表结构一致的临时表,该临时表已经是按要求修改后的表结构了,缓慢增量的从原表中复制数据,同时记录原表的更改(所有的 INSERT, DELETE, UPDATE 操作) 并应用到临时表。当工具确认表数据已经同步完成,它会进行替换工作,将临时表更名为原表。


索引

show index from table查看索引

show index from table_name;

show keys from table_name;

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

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

创建表时添加索引

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

create index在现有表上添加索引

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

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

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

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

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

key和index区别

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

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

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

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


create table

查看建表语句

show create table 查看建表语句

show create table table_name;

show create database 查看建库语句

show create database database_name;

desc 查看列

desc table_name;

AUTO_INCREMENT 自增值设置与修改

创建表时设置自增主键:,默认起始值是 1

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;

13.1.17 CREATE TABLE Statement - AUTO_INCREMENT
https://dev.mysql.com/doc/refman/5.6/en/create-table.html


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

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 index/key 添加索引

使用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/key 删除索引

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 中没有 drop constraint

注意:mysql 没有 DROP CONSTRAINT 语法,必须使用 drop index/key

>alter table user drop constraint UK_name;
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint UK_name' at line 1

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 修改列名

change column 可以修改列名、列数据类型,或者调整顺序

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

modify column 调整列顺序

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

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

只修改表默认字符集

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;

rename table 表重命名

RENAME TABLE old_table TO new_table;
等价于
ALTER TABLE old_table RENAME new_table;

13.1.33 RENAME TABLE Statement
https://dev.mysql.com/doc/refman/5.7/en/rename-table.html


唯一约束

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

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

创建表时添加唯一约束

在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


外键约束

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

Can’t write; duplicate key in table xxx

错误:
使用 Jpa 的 SpringBoot 服务启动时报错:

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Unable to execute schema management to JDBC target [alter table common_user_role add constraint FKjui11rhjxr1u2hd6un2fdggkp foreign key (role_id) references common_role (id)]
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Can't write; duplicate key in table '#sql-1_42613'

由于设置了 hibernate.hbm2ddl.auto=update 启动时执行了 alter 语句,添加一个外键约束时失败了。

原因:
数据库中已有一个同名约束,即约束的名字 FKjui11rhjxr1u2hd6un2fdggkp 重复了。
MySQL 约束名在整个数据库中必须是唯一的,只在一个表上是唯一的还不行。
也就是说,假如同数据库的表 tableA 上有名字是 constraintA 的约束,那么 tableB 上再创建名为 constraintA 的约束就会失败。

在 information_schema 库的 KEY_COLUMN_USAGE 表中可以查看某个约束名在数据库上是否已经存在:

SELECT TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME = 'FKjui11rhjxr1u2hd6un2fdggkp';

解决:
果然同一个库上有个测试表上已有名字是 FKjui11rhjxr1u2hd6un2fdggkp 的约束,手动 drop 表后解决。


mysql外键

在MySQL 3.23版本后,InnoDB引擎类型的表支持了外键约束,外键主要用于引用和参照完整性的约束检查。外键的好处:可以使得两张表关联,保证数据的一致性和实现一些级联操作。但是外键的使用是有一些必要条件的:

  1. 两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持);

  2. 外键列必须建立了索引,MySQL 4.1.2以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显式建立;

  3. 外键关系的两个表的列必须是数据类型相似,也就是可以相互转换类型的列,比如int和tinyint可以,而int和char则不可以;

在InnoDB存储引擎中,对于一个外键列,如果没有显示地对这个列加索引,InnoDB存储引擎会自动对其加一个索引,因为这样可以避免表锁。这比Oracle数据库做得好,Oracle数据库不会自动添加索引,用户必须自己手动添加,这也导致了Oracle数据库中可能产生死锁。

外键的定义语法:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在CREATE TABLE和ALTER TABLE时使用,如果不指定CONSTRAINT symbol,MySQL会自动生成一个名字。对于ON DELETE、ON UPDATE表示事件触发限制。

一般来说,称被引用的表为父表,引用的表为子表。外键定义时ON DELETE和ON UPDATE表示在对父表进行DELETE或UPDATE操作时,对子表所做的操作,可定义的子表操作有:

CASCADE(跟随外键改动):表示当父表发送DELETE或UPDATE操作时,对相应的子表中的数据也进行DELETE和UPDATE操作。
SET NULL(设空值):表示当父表发送DELETE或UPDATE操作时,对相应的子表中的数据更新为NULL值,但子表对应的列必须允许为NULL。
SET DEFAULT(设默认值):表示当父表发送DELETE或UPDATE操作时,对相应的子表中的数据更新为默认值。
NO ACTION(无动作,默认的):表示当父表发送DELETE或UPDATE操作时,抛出错误,不允许这类操作发生。
RESTRICT(限制外表中的外键改动):表示当父表发送DELETE或UPDATE操作时,抛出错误,不允许这类操作发生。如果定义外键时没有指定ON DELETE或ON UPDATE,RESTRICT就是默认的外键设置。

在其他数据库中,如Oracle数据库中,有一种称为延时检查(deferred check)的外键约束,即检查在SQL语句运行完成后再进行。而目前MySQL数据库的外键约束都是即时检查,因此从上面的定义可以看出,在MySQL数据库中NO ACTION和RESTRICT的功能是相同的。

对于参照完整性约束,外键能起到一个非常好的作用。但是对于数据的导入操作时,外键往往导致在外键约束的检查上花费大量时间。因此MySQL数据库的外键是即时检查的,所以对导入的每一行都会进行外键检查。但是用户可以在导入过程中忽视外键的检查,如:

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA ....

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

对于外键值的插入或更新,首先需要检查父表中的记录,既SELECT父表。但是对于父表的SELECT操作,不是使用一致性非锁定读的方式,因为这会发生数据不一致的问题,因此这时使用的是SELECT…LOCK IN SHARE MODE方式,即主动对父表加一个S锁。如果这时父表上已经这样加X锁,子表上的操作会被阻塞,如下:

先创建测试环境,如下:

# 创建parent表;
create table parent(
  tag_id int primary key auto_increment not null,
  tag_name varchar(20)
);

# 创建child表;
create table child(
  article_id int primary key auto_increment not null,
  article_tag int(11),
  CONSTRAINT  tag_at FOREIGN KEY (article_tag) REFERENCES parent(tag_id)
);

# 插入数据;
insert into parent(tag_name) values('mysql');
insert into parent(tag_name) values('oracle');
insert into parent(tag_name) values('mariadb');

开始测试

# Session A
mysql> begin
mysql> delete from parent where tag_id = 3;

# Session B
mysql> begin
mysql> insert into child(article_id, article_tag) values(1,3);   #阻塞

Session B 执行insert语句时被阻塞。当Session A手动执行commit提交事务后,Session B才能继续。

在上述的例子中,两个会话中的事务都没有进行COMMIT或ROLLBACK操作,而会话B的操作会被阻塞。这是因为tag_id为3的父表在会话中已经加了一个X锁,而此时在会话B中用户又需要对父表中tag_id为3的行加一个S锁,这时INSERT的操作会被阻塞。设想如果访问父表时,使用的是一致性的非锁定读,这时Session B会读到父表有tag_id=3的记录,可以进行插入操作。但是如果会话A对事务提交了,则父表中就不存在tag_id为3的记录。数据在父、子表就会存在不一致的情况。

MySQL InnoDB外键约束详解 - 运维那点儿事 质量很高的文章
http://www.ywnds.com/?p=9135


外键与锁

在mysql的多个存储引擎中,innodb支持外键,但是由于外键,也会对innodb表增加锁定机制;

所有的外键相关的操作都在数据更改时,比如检查数据完整性、增加锁定等;

假设一个表为 parent ,一个表为child,child表有外键pid REFERENCES parent(id);在一个session中set autocommit=0,执行对parent或child的操作,在另一个
session执行对child或parent的操作,从而得出以下结论:

一、对父表的操作
1、insert to parent ,新插入行的id值为XXX
child会锁外键值为XXX的行,不会锁其他行

2、update parent ,原id为XXX,现id为YYY
child会锁外键值为XXX,YYY的行,不会锁其他行

3、delete from parent ,删除行的id为XXX
child会锁外键值为XXX,不会锁其他行

二、对子表的操作
1、insert to child,插入行的外键值为XXX
parent 会锁值XXX的行,不会锁其他行

2、update child,更新行的外键值原为XXX,现为YYY
parent 会锁XXX,YYY行,但要注意:存在间隔锁,也会锁其他行(XXX,YYY之间的位置)

3、delete from child,删除行的外键值为XXX
parent 会锁XXX的行,但要注意:存在间隔锁,也会锁其他行(XXX-1的位置)

mysql 外键锁机制
https://blog.csdn.net/lan12334321234/article/details/70049370


InnoDB会自动为外键创建索引

下面是 mysql(mariadb) 中的一个建表语句,原始sql如下:

-- 用户地址表, 常用地址和收货地址
DROP TABLE IF EXISTS user_address;
CREATE TABLE user_address (
  id               BIGINT                        NOT NULL AUTO_INCREMENT,
  app_id           INT                           NOT NULL,
  user_id          BIGINT                        NOT NULL,
  status           ENUM ('enable', 'disable')    NOT NULL DEFAULT 'enable'
  COMMENT '状态',
  province_id      BIGINT
  COMMENT '省份id',
  province         VARCHAR(64)
  COMMENT '省份',
  city_id          BIGINT
  COMMENT '城市id',
  city             VARCHAR(64)
  COMMENT '城市',
  region_id        BIGINT
  COMMENT '区县id',
  region           VARCHAR(64)
  COMMENT '区县',
  detailed_address VARCHAR(512)                  NOT NULL
  COMMENT '详细地址',
  type             ENUM ('usual', 'deliverable') NOT NULL
  COMMENT 'usual:常用联系地址,deliverable:收货地址',
  priority         INT                           NOT NULL DEFAULT 1
  COMMENT '序号,用于排序',
  is_default       BOOLEAN                       NOT NULL DEFAULT FALSE
  COMMENT '是否默认地址',
  ext_data         VARCHAR(1024)                 NOT NULL
  COMMENT '姓名、手机号地区码、手机号、别名,json字符串',
  `create_time`    DATETIME                      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`    TIMESTAMP                     NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user (id),
  FOREIGN KEY (province_id) REFERENCES china_region (id),
  FOREIGN KEY (city_id) REFERENCES china_region (id),
  FOREIGN KEY (region_id) REFERENCES china_region (id)
)
  ENGINE = InnoDB,
  DEFAULT CHARSET = utf8mb4;

创建完后在 DataGrip 中查看建表语句的话,发现多了几个外键的创建索引语句,这是mysql自动给加外键的索引

create table user_address
(
    id bigint auto_increment primary key,
    user_id bigint not null,
    province_id bigint null comment '省份id',
    province varchar(64) null comment '省份',
    city_id bigint null comment '城市id',
    city varchar(64) null comment '城市',
    region_id bigint null comment '区县id',
    region varchar(64) null comment '区县',
    detailed_address varchar(512) not null comment '详细地址',
    type enum('usual', 'deliverable') not null comment 'usual:常用联系地址,deliverable:收货地址',
    priority int default '1' not null comment '序号,用于排序',
    is_default tinyint(1) default '0' not null,
    ext_data varchar(1024) null comment '姓名、手机号地区码、手机号、别名,json字符串',
    app_id int not null,
    status enum('enable', 'disable') default 'enable' not null comment '状态',
    create_time datetime default CURRENT_TIMESTAMP not null,
    update_time timestamp default CURRENT_TIMESTAMP not null,
    constraint user_address_ibfk_1
        foreign key (user_id) references user (id),
    constraint user_address_ibfk_2
        foreign key (province_id) references china_region (id),
    constraint user_address_ibfk_3
        foreign key (city_id) references china_region (id),
    constraint user_address_ibfk_4
        foreign key (region_id) references china_region (id)
);

-- 下面4个创建索引的语句是mysql自动加的
create index user_id on user_address (user_id);
create index city_id on user_address (city_id);
create index province_id on user_address (province_id);
create index region_id on user_address (region_id);

直接在命令行用 show create table user_address 看建表sql也会看到多了几个建索引的 KEY 语句:

CREATE TABLE `user_address` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `province_id` bigint(20) DEFAULT NULL COMMENT '省份id',
  `province` varchar(64) DEFAULT NULL COMMENT '省份',
  `city_id` bigint(20) DEFAULT NULL COMMENT '城市id',
  `city` varchar(64) DEFAULT NULL COMMENT '城市',
  `region_id` bigint(20) DEFAULT NULL COMMENT '区县id',
  `region` varchar(64) DEFAULT NULL COMMENT '区县',
  `detailed_address` varchar(512) NOT NULL COMMENT '详细地址',
  `type` enum('usual','deliverable') NOT NULL COMMENT 'usual:常用联系地址,deliverable:收货地址',
  `priority` int(11) NOT NULL DEFAULT '1' COMMENT '序号,用于排序',
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  `ext_data` varchar(1024) DEFAULT NULL COMMENT '姓名、手机号地区码、手机号、别名,json字符串',
  `app_id` int(11) NOT NULL,
  `status` enum('enable','disable') NOT NULL DEFAULT 'enable' COMMENT '状态',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `province_id` (`province_id`),
  KEY `city_id` (`city_id`),
  KEY `region_id` (`region_id`),
  CONSTRAINT `user_address_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `user_address_ibfk_2` FOREIGN KEY (`province_id`) REFERENCES `china_region` (`id`),
  CONSTRAINT `user_address_ibfk_3` FOREIGN KEY (`city_id`) REFERENCES `china_region` (`id`),
  CONSTRAINT `user_address_ibfk_4` FOREIGN KEY (`region_id`) REFERENCES `china_region` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10070 DEFAULT CHARSET=utf8mb4

show index 查看索引如下:

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.016 sec)

上一篇 MySQL-Function 函数与操作符

下一篇 Linux-Service

阅读
评论
20.3k
阅读预计88分钟
创建日期 2021-07-19
修改日期 2021-07-19
类别
目录
  1. DQL
    1. SQL优化
      1. count(*)慢优化
        1. id分段count后求和
        2. 非精确count-information_schema.tables
        3. 非精确-show table status like
        4. 非精确count-解析explain扫描行数
      2. count(distinct)优化
      3. int和varchar类型索引
      4. 去掉表字段上的unix_timestamp时间函数
      5. create_time排序改为id排序避免回表
      6. offset分页改写为where id条件
      7. 两表关联的连接表如何建索引
    2. 干预索引
      1. use index(a,b) 限制索引范围
      2. ignore index(a,b) 忽略索引
      3. force index(a) 强制使用索引
    3. like
      1. like binary 区分大小写
      2. like escape 转义通配符
    4. regexp 正则匹配
    5. order by field按指定顺序排序
    6. exists
      1. 重复数据取最后更新的
      2. 重复数据取最后插入的
      3. 重复数据取最大/最小
    7. case when 条件表达式
      1. case when 查及格和不及格人数
      2. case根据列in分组
      3. update中使用case when赋值
    8. 不等号
      1. OR条件没加括号直接和其他条件AND
    9. 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全连接
    10. union
      1. union与union all区别
      2. union all 多表count求和
    11. distinct多列
    12. group by
      1. join 后 group by 聚合为拼接串
      2. select字段必须是分组字段或聚集函数
      3. order字段必须是分组字段或聚集函数
      4. ERROR 1055 (42000) sql_mode=only_full_group_by
      5. having子句中不能使用列别名
    13. \G 列变行
  2. DCL
    1. 手动提交事务
    2. mysql start transaction 和 set autocommit = 0 的区别
    3. show 语句
      1. show status 查看服务端状态变量
      2. show plugins 查看所有插件
  3. DML
    1. insert
      1. on duplicate key update
      2. insert多条记录
      3. insert select 表间数据复制
        1. 同一数据库内insert select
        2. 不同数据库间insert select
        3. 表结构不相同insert select
    2. delete
      1. 清空表truncate和delete
      2. 外键导致删除失败
      3. delete语句使用别名
      4. 删除重复行
        1. 删除所有重复行
        2. 重复行只保留一条
      5. delete join
    3. update
      1. update多列
      2. update join 跨表关联更新
      3. update case when 条件表达式更新
      4. specified twice both target and source
      5. Every derived table must have its own alias
      6. update返回值与useAffectedRows
  4. DDL
    1. 字段名避免使用mysql关键字
    2. DDL与事务
      1. MySQL不支持事务型DDL
      2. 常见数据库的事务DDL支持情况
      3. MySQL事务中混合DDL会怎样?
    3. MySQL的三种DDL处理方式
      1. Copy Table(可读不可写)
      2. Inplace(可读不可写)
      3. Online(可读可写,5.6及以上)
    4. MySQL5.6 中的 InnoDB Online DDL
      1. Online DDL 选项
      2. Online DDL 索引操作
        1. MySQL给已存在的表增加索引时会锁表吗?
      3. Online DDL 主键操作
      4. Online DDL 字段操作
    5. 第三方Schema迁移工具
      1. pt-online-schema-change
      2. gh-ost
    6. 索引
      1. show index from table查看索引
      2. 创建表时添加索引
      3. create index在现有表上添加索引
      4. 前缀索引(字符串最大768字节)
      5. key和index区别
    7. create table
      1. 查看建表语句
        1. show create table 查看建表语句
        2. show create database 查看建库语句
        3. desc 查看列
      2. AUTO_INCREMENT 自增值设置与修改
    8. alter table
      1. add column 添加列
      2. drop column 删除列
        1. 删除的列不能是约束的一部分
      3. add index/key 添加索引
      4. drop index/key 删除索引
        1. MySQL 中没有 drop constraint
      5. first/after 添加列时指定字段顺序
      6. 同时添加/删除/修改列和索引
      7. change column 修改列名
      8. modify column 调整列顺序
      9. modify column 修改列类型
      10. modify column 修改列注释
      11. modify column 修改列为大小写敏感的
      12. 修改表默认字符集和所有字符列的字符集
    9. rename table 表重命名
    10. 唯一约束
      1. 创建表时添加唯一约束
      2. 在已有表上添加唯一约束
      3. 查看唯一约束
      4. 修改唯一约束
      5. 删除唯一约束
    11. 外键约束
      1. 定义表时添加外键约束
      2. 在现有表上添加外键约束
      3. 修改约束
      4. 删除外键约束
      5. Can’t write; duplicate key in table xxx
      6. mysql外键
      7. 外键与锁
      8. InnoDB会自动为外键创建索引

页面信息

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

评论