MySQL 死锁产生的原因和“超卖”现象

死锁其实很好理解,就是两个会话分别在等待对方占有锁的释放。这个概念不仅仅是MySQL数据库中的,计算机中一些资源的占有和释放,都可能会产生死锁。

在MySQL数据库中,举个例子,由于代码设计不当,比如两个事务会话中, 都使用读锁去占有一条数据,但是两个会话却都想更新这条数据,如果是并发请求,则会产生死锁,看以下SQL执行顺序:

begin;
select * from goods_sku where id = 1 in share mode;
update goods_sku set stock = stock - 1 where id = 1 and stock > 0;
commit; 

Continue reading "MySQL 死锁产生的原因和“超卖”现象"

MySQL四种事务隔离级别

事务是一系列数据操作的过程。事务具有四个特征,分别足原子性(Atomicity )、一致性(Consistency )、隔离性(Isolation) 和持久性(Durability),简称为事务的ACID特性。

先了解几种场景的名词定义:

脏读(Drity Read):A事务更新了一份数据,但是未提交(commit),B事务此时读到了这份未提交的更新数据,称之为脏读。如果事务A不回滚,基本上也没什么问题,如果事务A回滚了,问题可能就会非常大。

不可重复读(Non-repeatable read):A事务在执行过程中,B事务对数据进行了修改或删除(已commit),导致A两次读取的数据不一致;重点在于update和delete(锁行即可解决)。

幻读(Phantom Read):A事务在执行过程中,B事务新增了符合A事务要查询的数据,导致A两次读取的数据不一致;重点在于insert(需要锁表解决)。

Continue reading "MySQL四种事务隔离级别"

MySQL终端显示当前用户名、服务器、和数据库

MySQL终端默认是这样子的,比较简陋:

➔ mysql
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 150
Server version: 5.6.47-log Homebrew

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

觉得mysql>太简单了,我想让他显示更多的信息,就像平时我会用oh-my-zsh修改本地的shell那样,比如,这是我终端的提示符:

jake in Macintosh at Code/foo-poject on jake-v2.13 [!?$]
➔

可以显示当前用户名,目录,git版本等等信息。

方法很简单,设置一个环境变量即可:

export MYSQL_PS1="\u@\h [\d]> "

再次尝试:

➔ mysql
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 151
Server version: 5.6.47-log Homebrew

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> use performancenet;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost [performancenet]>

现在提示符部分变得丰富了?,如果想永久生效,需要将export MYSQL_PS1="\u@\h [\d]> "这行命令加入到你的~/.bashrc(或者~/.zshrc)文件中。

SQL中的distinct关键字的正确用法

distinct 并不是一个函数,它只是一个修饰词,它的意思是过滤所有的重复返回行

因此以下语句:

select distinct(user.cellphone), user.points from user;

可能会返回重复的手机号的行,因为distinct 并不是跟(user.cellphone)结合的,它实际上是跟select结合的,将其修正为正确的写法:

select distinct user.cellphone, user.points from user;

这条语句的含义是返回user.cellphoneuser.points结合的唯一行,因此依然会有重复的手机号返回,以下语句则不会有重复的手机号返回,因为返回行总共只有一列:

select distinct user.cellphone from user;

如果想在返回唯一的手机号时,顺便携带points,则应当使用group by:

select user.cellphone, user.points from user group by user.cellphone;

MySQL中的时间函数

UNIX_TIMESTAMP() 返回当前时间戳

mysql> select UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1593576560 |
+------------------+
1 row in set (0.00 sec)

UNIX_TIMESTAMP(datetime) 返回指定日期的时间戳

这个函数和上一个函数一样,只是增加了参数

mysql> select UNIX_TIMESTAMP('2020-07-01 12:11:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-07-01 12:11:00') |
+---------------------------------------+
|                            1593576660 |
+---------------------------------------+
1 row in set (0.01 sec)

FROM_UNIXTIME 将时间戳转换成datetime格式

mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP());
+---------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP()) |
+---------------------------------+
| 2020-07-01 12:11:00             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1593576660);
+---------------------------+
| FROM_UNIXTIME(1593576660) |
+---------------------------+
| 2020-07-01 12:11:00       |
+---------------------------+
1 row in set (0.01 sec)

mysql> select FROM_UNIXTIME(createtime), FROM_UNIXTIME(starttime) from project limit 1 \G
*************************** 1. row ***************************
FROM_UNIXTIME(createtime): 2016-01-20 16:59:25
 FROM_UNIXTIME(starttime): 2016-01-20 00:30:00
1 row in set (0.00 sec)

DATE_FORMAT格式化一个datetime时间

mysql> select DATE_FORMAT(FROM_UNIXTIME(1593576444), '%Y/%m/%d %H/%i/%s %M %p');
+-------------------------------------------------------------------+
| DATE_FORMAT(FROM_UNIXTIME(1593576444), '%Y/%m/%d %H/%i/%s %M %p') |
+-------------------------------------------------------------------+
| 2020/07/01 12/07/24 July PM                                       |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL连接查询on和where的区别和顺序

原文连接:https://www.cnblogs.com/jessy/p/3525419.html

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

关键字: on

数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。

在使用left jion时,on和where条件的区别如下:

1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。

2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

假设有两张表:

表1:tab1

id size
1 10
2 20
3 30

表2:tab2

size name
10 AAA
20 BBB
20 CCC

两条SQL:

1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')
第一条SQL的过程:

1、中间表
on条件:
tab1.size = tab2.size
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 CCC
3 30 (null) (null)
2、再对中间表过滤
where 条件:
tab2.name=’AAA’
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
第二条SQL的过程:

1、中间表
on条件:
tab1.size = tab2.size and tab2.name=’AAA’
(条件不为真也会返回左表中的记录)
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)

其实以上结果的关键原因就是left join,right join,full join的特殊性,不管on上的条件是否为真都会返回left或right表中的记录,full则具有left和right的特性的并集。 而inner jion没这个特殊性,则条件放在on中和where中,返回的结果集是相同的。