深入解析：MySQL(50)如何使用UNSIGNED属性？

在 MySQL 中，UNSIGNED 属性用于数值数据类型（如 TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT），表示该列只能存储非负整数。使用 UNSIGNED 属性可以有效地扩展列的正整数范围，因为它不需要为负数保留空间。

1. 定义与用途

• 定义：UNSIGNED 属性用于指定数值列只能存储非负整数。
• 用途：用于只需要存储正数的场景，如计数器、ID、库存数量等。

2. 范围

不同数据类型的 UNSIGNED 和未使用 UNSIGNED 时的取值范围如下：

• TINYINT：
  • 有符号（默认）：-128 到 127
  • 无符号：0 到 255
• SMALLINT：
  • 有符号（默认）：-32,768 到 32,767
  • 无符号：0 到 65,535
• MEDIUMINT：
  • 有符号（默认）：-8,388,608 到 8,388,607
  • 无符号：0 到 16,777,215
• INT：
  • 有符号（默认）：-2,147,483,648 到 2,147,483,647
  • 无符号：0 到 4,294,967,295
• BIGINT：
  • 有符号（默认）：-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
  • 无符号：0 到 18,446,744,073,709,551,615

3. 示例代码

以下是一些示例代码，展示了如何使用 UNSIGNED 属性创建表，并插入和检索数据。

创建表

创建一个包含 UNSIGNED 属性的表：

CREATE
DATABASE test_unsigned_db;
USE test_unsigned_db;
CREATE
TABLE inventory (
item_id INT
UNSIGNED
AUTO_INCREMENT
PRIMARY
KEY
,
item_name VARCHAR(50
) NOT NULL
,
quantity INT
UNSIGNED
DEFAULT 0 -- 无符号整数，默认值为0
)
;

插入数据

插入一些数据：

-- 插入数据
INSERT
INTO inventory (item_name, quantity)
VALUES
('Laptop'
, 50
)
,
('Smartphone'
, 100
)
,
('Tablet'
, 30
)
;

检索数据

检索数据：

SELECT item_id, item_name, quantity FROM inventory;

检索结果：

+---------+-------------+----------+
| item_id | item_name   | quantity |
+---------+-------------+----------+
|       1 | Laptop      |       50 |
|       2 | Smartphone  |      100 |
|       3 | Tablet      |       30 |
+---------+-------------+----------+

插入负数数据（将失败）

尝试插入负数数据：

-- 尝试插入负数数据，将失败
INSERT
INTO inventory (item_name, quantity)
VALUES
('Camera'
, -10
)
;

执行结果：

ERROR 1264 (22003): Out of range value for column 'quantity' at row 1

4. 更复杂的示例：用户积分系统

以下是一个更复杂的示例，展示了如何在用户积分系统中使用 UNSIGNED 属性。

创建用户积分表

CREATE
TABLE user_points (
user_id INT
UNSIGNED
AUTO_INCREMENT
PRIMARY
KEY
,
username VARCHAR(50
) NOT NULL
,
points BIGINT
UNSIGNED
DEFAULT 0 -- 无符号大整数，默认值为0
)
;

插入用户数据

插入一些用户数据：

-- 插入用户数据
INSERT
INTO user_points (username, points)
VALUES
('john_doe'
, 1000
)
,
('jane_smith'
, 1500
)
,
('alice_jones'
, 2000
)
;

检索用户数据

检索用户数据：

SELECT user_id, username, points FROM user_points;

检索结果：

+---------+-------------+--------+
| user_id | username    | points |
+---------+-------------+--------+
|       1 | john_doe    |   1000 |
|       2 | jane_smith  |   1500 |
|       3 | alice_jones |   2000 |
+---------+-------------+--------+

尝试插入负数积分（将失败）

尝试插入负数积分：

-- 尝试插入负数积分，将失败
INSERT
INTO user_points (username, points)
VALUES
('tom_hanks'
, -500
)
;

执行结果：

ERROR 1264 (22003): Out of range value for column 'points' at row 1

5. 注意事项

• 范围限制：由于 UNSIGNED 属性只允许非负数，试图插入负数会导致错误。
• 自动递增：对于自动递增（AUTO_INCREMENT）的列，使用 UNSIGNED 属性可以有效地扩展 ID 的正整数范围。
• 兼容性：确保业务逻辑不需要存储负数，否则使用 UNSIGNED 会导致数据插入失败。

小结

• UNSIGNED 属性：用于指定数值列只能存储非负整数，适用于计数器、ID、库存数量等场景。
• 扩展正整数范围：使用 UNSIGNED 可以有效地扩展列的正整数范围。
• 实际应用：通过指定 UNSIGNED 属性，可以确保数据的正整数范围符合业务需求，提高数据完整性和一致性。

通过理解 UNSIGNED 属性的用途和特点，可以更好地设计和优化数据库表，确保数据插入操作符合业务需求。上述示例展示了如何在实际应用中使用 UNSIGNED 属性设计数据库表，并插入和检索数据。