mysql> #首先进行unsigned的测试
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a |
| employees |
| t |
| test01 |
| timetest |
| tt |
| updatetime |
| z |
+----------------+
8 rows in set (0.00 sec)
mysql> select * from t;
+------+------+
| a | b |
+------+------+
| 0001 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> select a-b from t;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - `test`.`t`.`b`)'
mysql> #为什么会出现这种情况
mysql> #在c语言中 自己可以测试下 unsigned int a = 1, b = 2; printf("%d\n",a-b)
mysql> #此时 对于c %p 打印出a-b, 此时会出现ffffffff
mysql> #解决方法 set sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> set sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> select a-b from t;
+------+
| a-b |
+------+
| -1 |
+------+
1 row in set (0.00 sec)
mysql> show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` int(4) unsigned zerofill DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> # 看出t表的细节
mysql> select * from t;
+------+------+
| a | b |
+------+------+
| 0001 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> #我们看到a有三个0 怎么除掉呢?
mysql> alter table t change column a a int(0) unsigned zerofill;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------------+------+
| a | b |
+------------+------+
| 0000000001 | 2 |
+------------+------+
1 row in set (0.00 sec)
mysql> alter table t change column a a int(1) unsigned zerofill;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> #int后面的表示位数
mysql> #hex的使用
mysql> select a, hex(a) from t;
+------+--------+
| a | hex(a) |
+------+--------+
| 1 | 1 |
+------+--------+
1 row in set (0.00 sec)
mysql> #sql_mode 的设置
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a |
| employees |
| t |
| test01 |
| timetest |
| tt |
| updatetime |
| z |
+----------------+
8 rows in set (0.00 sec)
mysql> select * from t;
+------+------+
| a | b |
+------+------+
| 0001 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> select a-b from t;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - `test`.`t`.`b`)'
mysql> #为什么会出现这种情况
mysql> #在c语言中 自己可以测试下 unsigned int a = 1, b = 2; printf("%d\n",a-b)
mysql> #此时 对于c %p 打印出a-b, 此时会出现ffffffff
mysql> #解决方法 set sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> set sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> select a-b from t;
+------+
| a-b |
+------+
| -1 |
+------+
1 row in set (0.00 sec)
mysql> show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` int(4) unsigned zerofill DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> # 看出t表的细节
mysql> select * from t;
+------+------+
| a | b |
+------+------+
| 0001 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> #我们看到a有三个0 怎么除掉呢?
mysql> alter table t change column a a int(0) unsigned zerofill;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------------+------+
| a | b |
+------------+------+
| 0000000001 | 2 |
+------------+------+
1 row in set (0.00 sec)
mysql> alter table t change column a a int(1) unsigned zerofill;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> #int后面的表示位数
mysql> #hex的使用
mysql> select a, hex(a) from t;
+------+--------+
| a | hex(a) |
+------+--------+
| 1 | 1 |
+------+--------+
1 row in set (0.00 sec)
mysql> #sql_mode 的设置