mysql---存储数据

float double（浮点数） decimal（定点数）
（10， 5）第一位表示浮点数的位数，第二位表示小数点后的位数，
浮点数不加的话 默认数据不丢失，定点数不加的话默认为（10， 0）

mysql> select * from t2;+--------------------+------+------+| id1                | id2  | id3  |+--------------------+------+------+| 1.2300000190734863 | 1.23 |    1 || 1.2300000190734863 | 1.23 |    1 || 1.2300000190734863 | 1.23 |    1 || 1.2300000190734863 | 1.23 |    1 || 1.2300000190734863 | 1.23 |    1 |+--------------------+------+------+5 rows in set (0.00 sec)mysql> insert into t2 values(1.234, 1.234, 1.234);Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t2;+--------------------+-------+------+| id1                | id2   | id3  |+--------------------+-------+------+| 1.2300000190734863 |  1.23 |    1 || 1.2300000190734863 |  1.23 |    1 || 1.2300000190734863 |  1.23 |    1 || 1.2300000190734863 |  1.23 |    1 || 1.2300000190734863 |  1.23 |    1 ||              1.234 | 1.234 |    1 |+--------------------+-------+------+6 rows in set (0.00 sec)mysql> insert into t2 values(1.345, 2.1345, 1.345);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> show warnings;+-------+------+------------------------------------------+| Level | Code | Message                                  |+-------+------+------------------------------------------+| Note  | 1265 | Data truncated for column 'id3' at row 1 |+-------+------+------------------------------------------+1 row in set (0.00 sec)

位数据类型

mysql> create table bitDemo(id bit);Query OK, 0 rows affected (0.02 sec)mysql> desc bitDemo;+-------+--------+------+-----+---------+-------+| Field | Type   | Null | Key | Default | Extra |+-------+--------+------+-----+---------+-------+| id    | bit(1) | YES  |     | NULL    |       |+-------+--------+------+-----+---------+-------+1 row in set (0.00 sec)mysql> insert into table bitDemo values(2);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table bitDemo values(2)' at line 1mysql> insert into bitDemo values(2);ERROR 1406 (22001): Data too long for column 'id' at row 1mysql> insert into bitDemo values(1);Query OK, 1 row affected (0.00 sec)mysql> select * from bitDemo;+------+| id   |+------+|     |+------+1 row in set (0.00 sec)mysql> select bin(id), hex(id) from bitDemo;+---------+---------+| bin(id) | hex(id) |+---------+---------+| 1       | 1       |+---------+---------+1 row in set (0.00 sec)

date

mysql> create table dateDemo(d date, t time, dt datetime, tms timestamp);Query OK, 0 rows affected (0.02 sec)mysql> desc dateDemo;+-------+-----------+------+-----+-------------------+-----------------------------+| Field | Type      | Null | Key | Default           | Extra                       |+-------+-----------+------+-----+-------------------+-----------------------------+| d     | date      | YES  |     | NULL              |                             || t     | time      | YES  |     | NULL              |                             || dt    | datetime  | YES  |     | NULL              |                             || tms   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+-------+-----------+------+-----+-------------------+-----------------------------+4 rows in set (0.00 sec)mysql> insert into dateDemo values(now(), now(), now(), now());Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from dateDemo;+------------+----------+---------------------+---------------------+| d          | t        | dt                  | tms                 |+------------+----------+---------------------+---------------------+| 2016-11-16 | 11:08:11 | 2016-11-16 11:08:11 | 2016-11-16 11:08:11 |+------------+----------+---------------------+---------------------+1 row in set (0.00 sec)mysql> insert into dateDemo values(now(), now(), now(), 0);ERROR 1292 (22007): Incorrect datetime value: '0' for column 'tms' at row 1mysql> insert into dateDemo values(now(), now(), now(), null);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from dateDemo;+------------+----------+---------------------+---------------------+| d          | t        | dt                  | tms                 |+------------+----------+---------------------+---------------------+| 2016-11-16 | 11:08:11 | 2016-11-16 11:08:11 | 2016-11-16 11:08:11 || 2016-11-16 | 11:09:21 | 2016-11-16 11:09:21 | 2016-11-16 11:09:21 |+------------+----------+---------------------+---------------------+2 rows in set (0.00 sec)mysql> alter table dateDemo add tms2 timestamp;ERROR 1067 (42000): Invalid default value for 'tms2'mysql> alter table dateDemo add tms2 timestamp;ERROR 1067 (42000): Invalid default value for 'tms2'mysql> alter table dateDemo add timestamp2 timestamp;ERROR 1067 (42000): Invalid default value for 'timestamp2'

char 自动删除末尾空格 varchar 不删除末尾空格

mysql> insert into vc values('ab  ', 'ab');Query OK, 1 row affected (0.00 sec)mysql> select * from vc;+------+------+| vc   | c    |+------+------+| ab   | ab   |+------+------+1 row in set (0.00 sec)mysql> select length(vc), length(c) from vc;+------------+-----------+| length(vc) | length(c) |+------------+-----------+|          4 |         2 |+------------+-----------+1 row in set (0.01 sec)mysql> insert into vc values('ab +', 'ab+');Query OK, 1 row affected (0.00 sec)mysql> select * from vc;+------+------+| vc   | c    |+------+------+| ab   | ab   || ab + | ab+  |+------+------+2 rows in set (0.00 sec)