13、MySQL-无符号(UNSIGNED)和零填充(ZEROFILL)
零填充会自动使用无符号位零填充指的是位数固定,如果数值长度不足字段类型的长度,则使用0来填充无符号格式CREATE TABLE tbl_name( 字段名称 字段类型 UNSIGNED);零填充格式CREATE TABLE tbl_name( 字段名称 字段类型 ZEROFILL);mysql> CREATE TA
·
零填充会自动使用无符号位
零填充指的是位数固定,如果数值长度不足字段类型的长度,则使用0来填充
无符号格式
CREATE TABLE tbl_name(
字段名称 字段类型 UNSIGNED
);
零填充格式
CREATE TABLE tbl_name(
字段名称 字段类型 ZEROFILL
);
mysql> CREATE TABLE IF NOT EXISTS testNUM(
-> -- 默认为有符号
-> num1 TINYINT,
-> num2 TINYINT UNSIGNED,
-> num3 INT,
-> num4 INT ZEROFILL,
-> num5 INT UNSIGNED ZEROFILL
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT testNUM(num1,num2) VALUES(-5,-5);
ERROR 1264 (22003): Out of range value for column 'num2' at row 1
mysql> INSERT testNUM(num3,num4) VALUES(123,123);
mysql> INSERT testNUM VALUES(0,0,0,0,0);
mysql> INSERT testNUM(num3,num4,num5) VALUES(123,123,123);
mysql> SELECT * FROM testNUM;
+------+------+------+------------+------------+
| num1 | num2 | num3 | num4 | num5 |
+------+------+------+------------+------------+
| NULL | NULL | 123 | 0000000123 | NULL |
| 0 | 0 | 0 | 0000000000 | 0000000000 |
| NULL | NULL | 123 | 0000000123 | 0000000123 |
+------+------+------+------------+------------+
-> -- 默认为有符号
-> num1 TINYINT,
-> num2 TINYINT UNSIGNED,
-> num3 INT,
-> num4 INT ZEROFILL,
-> num5 INT UNSIGNED ZEROFILL
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT testNUM(num1,num2) VALUES(-5,-5);
ERROR 1264 (22003): Out of range value for column 'num2' at row 1
mysql> INSERT testNUM(num3,num4) VALUES(123,123);
mysql> INSERT testNUM VALUES(0,0,0,0,0);
mysql> INSERT testNUM(num3,num4,num5) VALUES(123,123,123);
mysql> SELECT * FROM testNUM;
+------+------+------+------------+------------+
| num1 | num2 | num3 | num4 | num5 |
+------+------+------+------------+------------+
| NULL | NULL | 123 | 0000000123 | NULL |
| 0 | 0 | 0 | 0000000000 | 0000000000 |
| NULL | NULL | 123 | 0000000123 | 0000000123 |
+------+------+------+------------+------------+
更多推荐
已为社区贡献1条内容
所有评论(0)