MySQL DML

Question

insert 多条记录 insert into table_name values (1),(2),(3),(4),(5);

Mysql 条件语句 in 与 or 效率对比:

• 如果 in 和 or 所在列 有索引或者主键 的话，or 和 in 没啥差别，执行计划 和执行时间都几乎一样
• 如果 in 和 or 所在列 没有索引 的话，性能差别就很大了，in 胜出

设置变量

set 命令: SET @var_name = value 或者使用 SET @var := value

查看执行计划

查看 sql 语句的执行计划使用: explain <sql-statement> ，返回字段对应的类型如下， 详情见 :

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type 从好到差的连接类型为 const、eq_reg、ref、range、indexhe 和 all
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

基本运算

• 整除: 5 div 2 = 2
• 取余: 5 mod 2 = 1
• 四舍五入: round(1.5) = 2
• 创建一个一定范围内的随机数: floor(rand() * (<max> - <min> + 1)) + <min>

相关函数

• last_insert_id() :表中含自增字段(auto_increment)，向表 insert 一条记录后，可以调用 last_insert_id() 来获得最近 insert 的那行记录的自增字段值
  • 向含 auto_increment 字段的表中正常插入一条记录后，last_insert_id() 会返回表中自增字段的当前值
  • 若在同一条 insert 语句中插入多行 insert into table_name(col_a, col_b) values('aa', 'bb'), ('aaa', 'bbb') ，则 last_insert_id() 返回的自增字段的"当前值"只在旧值的基础上加 1，这与事实不符，应该会是增加插入的条数
  • 指定自增 id 的值后 last_insert_id() 的值不变， insert into table_name(id, name) values(11, 'test3'); 如果此时 id 是 auto_increment 但是值指定，则 last_insert_id 的值不变
  • 如果 sql 语句执行出错，事务因执行出错回滚，则 last_insert_id() 的值不会恢复到事务执行前的那个值
• order by rand(): You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.
• 日期加减相应的值: date(date_add("date_time", INTERVAL -1 DAY))
• select 正则字段，mysql 8.0 之前不支持，如果 8.0 之前需要使用可以通过 length 及 location 方法合并使用 select CAST(RIGHT(url, LENGTH(url) - 3 - LOCATE('&id=', url)) from table_name

功能实现

convert str to date: str_to_date

select str_to_date('2017-10-01', '%Y-%m-%d') from dual;

监测 mysql 记录是否存在 best way to test if a row exists in a mysql table

select exists(select 1 from table_name where ...)

记录不存在 insert 存在 update

# 指定插入的值
insert into table_name (a,b,c) values (1,2,3) on duplicate key update c=c+1;
# 从表中选择插入的值
insert into table_name (a,b,c) select a,b,c from table_name_1 on duplicate key update c=c+1;

alter

------------------
-- 索引相关
------------------
-- 创建主键
alter table tablename add primary key (`column`);
-- 创建 unique 索引
alter table tablename add unique index (`key`);
-- 创建普通索引
alter table tablename add index index_name (`column`);
-- 创建全局索引
alter table tablename add fulltext (`column`);
-- 添加多列索引
alter table tablename add index index_name (`column1`, `column2`, `column3`);
-- 删除 key 键 unique 索引
alter table tablename drop index `key`;

------------------
-- 表相关
------------------
-- 修改表名
alter table old_tablename rename to new_tablename;
rename table old_table to new_table;

------------------
-- 字段相关
------------------
-- 修改字段类型 modify 只改除了字段名的别的约束
alter table tablename modify column char(10);
alter table tablename modify column bigint not null default 100;  -- 改变字段类型 not null 约束 默认值
-- 增加普通字段
alter table tablename add new_column varchar(10) not null default 'defaultValue';
-- 增加主键 增加唯一索引只需要将 primary key 改成
alter table tablename add id int unsigned not null auto_increment primary key;
-- 修改字段
alter table tablename change old_column new_column int not null;
-- 删除普通字段
alter table tablename drop column;
-- 调整字段顺序  使用 after 或者 before 关键字
alter table table_name modify column column_name varchar(100) comment 'comment' after column_name1;

update

# 更新单个字段
update table_name set col = val where col1 = 123
# 更新多个字段
update table_name set col1 = val1, col2 = val2 where col3 = 123;
# 多表联合更新
update table_name_1 t1, table_name_2 t2
set t1.col = t1.col + t2.col
where t1.col1 = '2017-09-20'
   and t2.col1='2017-09-19'
   and t1.uid = t2.uid;

update table_name_1 t1
inner join table_name_2 t2
  on t1.uid = t2.uid
set t1.col = t1.col + t2.col
where t1.col1 = '2017-09-20'
   and t2.col1='2017-09-19';

replace

一个表建立了唯一索引，当我们再向这个表中使用已经存在的键值 insert 一条记录,那将会抛出一个主键冲突的错误。如果想要用新记录的值来覆盖原来的记录值。方法一是使用 delete 语句删除原先的记录，然后再使用 insert 新的记录。方法二是使用 replace 语句。

使用 replace 插入一条记录时,如果不重复，replace 就和 insert 的功能一样,如果有重复记录，REPLACE 就使用新记录的值来替换原来的记录值.使用 REPLACE 的最大好处就是可以将 delete 和 insert 合二为一，形成一个原子操作。这样就可以不必考虑在同时使用 DELETE 和 INSERT 时添加事务等复杂操作了。

注意：在使用 REPLACE 时,表中必须有唯一索引，而且这个索引所在的字段不能允许空值，否则 REPLACE 就和 INSERT 完全一样的

# 单条记录
replace into users (id,name,age) values(123, '赵本山', 50);
# 多条记录
replace into users(id, name, age) values(123, '赵本山', 50), (134,'mary',15);