ป้องกันไม่ให้ใส่ตัวเลขลบใน MySQL

ใช้ INT UNSIGNED เพื่อป้องกันค่าติดลบ ตัวอย่างสร้างตาราง:

mysql> create table preventNegativeNumberDemo ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserName varchar(20), UserGameScores int ); Query OK, 0 rows affected (1.74 sec)

แก้ไขคอลัมน์เป็น UNSIGNED:

mysql> alter table preventNegativeNumberDemo modify column UserGameScores INT UNSIGNED NOT NULL; Query OK, 0 rows affected (3.32 sec) Records: 0 Duplicates: 0 Warnings: 0

ตรวจสอบโครงสร้าง:

mysql> desc preventNegativeNumberDemo;

ผลลัพธ์:

+----------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+------------------+------+-----+---------+----------------+ | UserId | int(11) | NO | PRI | NULL | auto_increment | | UserName | varchar(20) | YES | | NULL | | | UserGameScores | int(10) unsigned | NO | | NULL | | +----------------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

ทดสอบแทรกค่าลบ (error):

mysql> insert into preventNegativeNumberDemo(UserName,UserGameScores) values('Larry',0); Query OK, 1 row affected (1.20 sec) mysql> insert into preventNegativeNumberDemo(UserName,UserGameScores) values('Mike',-1); ERROR 1264 (22003): Out of range value for column 'UserGameScores' at row 1 mysql> insert into preventNegativeNumberDemo(UserName,UserGameScores) values('Sam',-100); ERROR 1264 (22003): Out of range value for column 'UserGameScores' at row 1 mysql> insert into preventNegativeNumberDemo(UserName,UserGameScores) values('John',100); Query OK, 1 row affected (0.84 sec) mysql> insert into preventNegativeNumberDemo(UserName,UserGameScores) values('Bob',200); Query OK, 1 row affected (0.48 sec)

แสดงข้อมูล (เฉพาะค่าบวกและศูนย์):

mysql> select * from preventNegativeNumberDemo;

ผลลัพธ์:

+--------+----------+----------------+ | UserId | UserName | UserGameScores | +--------+----------+----------------+ | 1 | Larry | 0 | | 2 | John | 100 | | 3 | Bob | 200 | +--------+----------+----------------+ 3 rows in set (0.00 sec)