MySQL 中的锁机制主要包括行锁和表锁。这两种锁的作用范围不同,对并发访问的控制方式也不同。
行锁(Row Lock)
行锁是针对表中的行进行的锁定操作。当一个事务需要修改某行的数据时,会获取该行的行锁,以确保在事务完成之前其他事务不能修改同一行的数据。
行锁的特点:
-
锁定粒度小: 行锁只锁定表中满足条件的特定行,允许其他事务并发地访问不涉及的行。
-
并发性高: 不同的事务可以同时锁定表中的不同行,允许并发的读写操作。
-
死锁风险: 当多个事务试图锁定对方已经锁定的行时,可能导致死锁的风险。
使用行锁的例子:
START TRANSACTION;
SELECT * FROM your_table WHERE your_condition FOR UPDATE; -
- 在事务中对锁定的行进行修改
COMMIT;
表锁(Table Lock)
表锁是对整个表进行的锁定操作。当一个事务需要修改表的数据时,会获取整个表的表锁,阻塞其他事务对该表的访问。
表锁的特点:
-
锁定粒度大: 表锁会锁定整个表,不同事务需要修改表的不同部分时,可能会引起并发性的降低。
-
并发性低: 当一个事务持有表锁时,其他事务无法访问相同表,可能导致并发性的降低。
-
减少死锁风险: 表锁虽然降低了并发性,但也减少了死锁的风险,因为表锁不会像行锁那样存在对方已经锁定的行的情况。
使用表锁的例子:
LOCK TABLES your_table WRITE; -- 表锁,阻塞其他事务对表的写访问和读访问
-- 在事务中对表进行修改
UNLOCK TABLES; -- 解锁表
注意: 在实际应用中,通常更倾向于使用行锁而不是表锁,因为行锁允许更高的并发性。表锁通常用于一些特殊情况,比如对整个表进行维护性操作时,为了避免其他事务的干扰。
1.在mysql技能练习题中有一个这个样的题
show databases;
列出所有数据库show tables;
列出所有表
create table points(
id int primary key auto_increment,
x int,
y int
);
计算查询为:
select id, (x^2 + y^2)/2 as result from points;
得到的结果集中,result 列的类型应该是:DECIMAL
我第一反应是 x平方 y平方 除2
我用查询语句试了一下
竟然是这样 为什么2的那个运算会是 0
chat查了一下是 这样
在 SQL 中,^
并不是表示指数运算(类似于数学中的幂运算)。在 SQL 中,^
表示按位异或运算。
1^2
表示 1 按位异或 2,这是二进制的按位异或操作,得到的结果是 3。2^2
表示 2 按位异或 2,结果是 0。(1^2 + 2^2)/2
表示 (3 + 0)/2,结果是 1.5。
如果您想进行正常的数学平方运算,您应该使用 POWER
函数或直接写成 1 * 1
和 2 * 2
:
SELECT POWER(1, 2), POWER(2, 2), (POWER(1, 2) + POWER(2, 2))/2 AS result FROM users;
或者:
SELECT 1 * 1, 2 * 2, (1 * 1 + 2 * 2)/2 AS result FROM users;
2.在mysql技能练习题中有一个这个样的题
Joe 在设计订单表,他已经完成了下列内容:
create table orders (
id int primary key auto_increment,
item_id int,
amount int,
unit_price decimal(12, 4),
price decimal(12, 4),
ts timestamp default now()
);
现在他需要给订单表加入一个 description 字段,这个字段需要保存订单的文字说明,这些文本不会超过两千字节, Joe需要加一个description 字段 这个字段的类型应该设置成什么类型的 为什么 不能是其他类型
对于保存文本说明的字段,常见的选择是使用 VARCHAR 或 TEXT 类型。在这种情况下,由于说明文本不会超过两千字节,您可以选择 VARCHAR。
ALTER TABLE orders ADD COLUMN description VARCHAR(2000);
我们先来了解一下适合这个字段的数据类型可以是什么
-
CHAR: (CHAR 适用于存储长度固定的字符串)
- 固定长度,占用整个指定长度,无论实际内容长度如何。(CHAR 是一种固定长度的字符串类型,需要在定义时指定长度。不足指定长度的部分会使用空格进行填充。)
- CHAR 类型可以设置默认值。
- CHAR 列可以作为索引。
- CHAR 的最大长度是 255 个字符。
- 不适合description字段的原因:存储效率可能较低,因为总是占用完整长度。CHAR 是固定长度的,所以无论实际内容有多少,都会占用 2000 个字符的存储空间。
-
VARCHAR: (VARCHAR 适用于存储长度可变的字符串)
- VARCHAR 是一种可变长度的字符串类型,同样需要在定义时指定最大长度,但存储时实际占用的空间取决于存储的内容长度。可变长度,占用实际内容所需的存储空间。
- VARCHAR 类型可以设置默认值。
- VARCHAR 列可以作为索引。
- VARCHAR 的最大长度是 65535 个字符。
-
TEXT: (TEXT 适用于存储大段文本)
- TEXT 是一种用于存储大量文本数据的可变长度字符串类型,存储时实际占用的空间取决于存储的内容长度。
- TEXT 类型不能设置默认值。
- TEXT 列不能作为索引。
- TEXT 的最大长度是 4294967295 个字符。
- 不适合description字段的原因 如果不指定 固定长度的话 text更适合
- 不需要事先指定固定长度,灵活性极高。
- 存储效率取决于实际内容长度。
总体而言,CHAR 适用于存储长度固定的字符串,VARCHAR 适用于存储长度可变的字符串,而 TEXT 适用于存储大段文本。选择使用哪种类型取决于您的数据特性和需求。
如果字符串长度是固定的,而且是一个常见的值,可以考虑使用 CHAR。
如果长度可变,但不是非常大,可以使用 VARCHAR。
如果需要存储大段文本,使用 TEXT。
为什么选择 VARCHAR(2000):
理论上可以使用 CHAR 或 TEXT 数据类型存储不超过两千字节的文本说明。
VARCHAR 是更为常见和合适的选择,原因如下:
-
Fixed vs. Variable Length:
- CHAR 是固定长度的字符串,无论存储的实际内容有多长,都会占用指定的空间。这意味着如果存储的文本不足指定长度,CHAR 字段会用空格填充。
- VARCHAR 是可变长度的字符串,只存储实际使用的字节数,因此在存储短文本时比固定长度的 CHAR 更节省空间。
-
Efficient Storage:
- VARCHAR 适用于相对较小的字符串,而 TEXT 通常用于更大的文本块。由于说明文本长度有限。使用 VARCHAR 更为高效,因为它不会浪费空间来存储填充字符。
-
Common Practice:
- 在实践中,VARCHAR 通常更为常见,因为它更适用于存储可变长度的文本数据。
-
Compatibility with Indexing:
- 如果以后您计划对该字段创建索引,VARCHAR 更适合创建索引,而 CHAR 由于固定长度可能导致一些效率问题。
- 如果您确切知道文本长度不会超过2000字符,并且希望有一些存储效率上的优势,VARCHAR(2000)是不错的选择。
- 如果文本长度可能变化较大,或者需要存储大段文本,TEXT是更为灵活的选择,不需要指定固定长度。
综上所述,为了存储不超过两千字节的文本说明,选择 VARCHAR(2000) 是一种合适的设计选择。
现在 Joe 的订单表已经有了如下形态:
create table orders (
id int primary key auto_increment,
item_id int,
amount int,
unit_price decimal(12, 4),
price decimal(12, 4),
ts timestamp default now(),
description varchar(2000)
);
他需要添加一个字段,用来保存订单的相关图片,由于特殊的业务需要,这些图片必须保存在数据库中,图片的大小不超过100K。那么他应该将建表语句修改为:
在这种情况下,由于要存储图片数据,通常会选择使用 BLOB(Binary Large Object)类型,而不是 VARCHAR。
BLOB 类型专门用于存储二进制数据,例如图片、音频或视频文件。由于图片大小不超过100K,可以选择使用 MEDIUMBLOB 类型,该类型支持存储最大长度为 16 MB 的二进制数据。
以下是修改后的建表语句:
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
item_id INT,
amount INT,
unit_price DECIMAL(12, 4),
price DECIMAL(12, 4),
description VARCHAR(2000),
images MEDIUMBLOB, -- 新增字段用于存储图片数据
ts TIMESTAMP DEFAULT NOW()
);
在上述语句中,添加了一个名为 images
的字段,其类型为 MEDIUMBLOB
,用于存储订单相关的图片数据。 MEDIUMBLOB
可以容纳比 VARCHAR
或 TEXT
更大的二进制数据,适合存储图像文件。
请注意,将图片数据存储在数据库中可能会增加数据库的大小,而有些开发者更倾向于将图片存储在文件系统中,而只在数据库中存储图片的路径或引用。这取决于具体业务需求和性能考虑。
如果用 longtext存储是怎样的
使用 LONGTEXT
类型是另一种存储文本或二进制数据的方式,它支持更大的存储空间,最大长度为 4 GB。在您的场景中,如果图片大小不超过100K,LONGTEXT
也是一个可行的选择。以下是相应的修改建表语句:
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
item_id INT,
amount INT,
unit_price DECIMAL(12, 4),
price DECIMAL(12, 4),
description VARCHAR(2000),
images LONGTEXT, -- 新增字段用于存储图片数据
ts TIMESTAMP DEFAULT NOW()
);
在这里,将 images
字段的类型更改为 LONGTEXT
,允许存储更大的文本或二进制数据。在实际使用时,要确保数据库和应用程序的配置允许处理较大的 LONGTEXT
数据。
需要注意的是,将图片数据存储在数据库中有一些潜在的考虑因素,包括数据库性能、备份和恢复的复杂性。有些开发者更倾向于将大的二进制数据(如图片)存储在文件系统中,而在数据库中仅存储路径或引用。这样可以减小数据库的大小,提高性能,并更好地利用数据库和文件系统各自的优势。选择存储方式时,需要根据具体的业务需求和性能考虑做出权衡。