mysql使用02

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 的设置