mysql----选择合适的数据类型

1, 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   |
| ab + | ab+  |
| ab   | ab   |
+------+------+
3 rows in set (0.00 sec)

mysql> select concat(vc, '+'), concat(c, '+') from vc;
+-----------------+----------------+
| concat(vc, '+') | concat(c, '+') |
+-----------------+----------------+
| ab  +           | ab+            |
| ab ++           | ab++           |
| ab  +           | ab+            |
+-----------------+----------------+
3 rows in set (0.01 sec)

char 固定长度，存储速度快，小数据适合
varchar 长度不固定，
和mysql存储引擎影响
Myisam 建议使用固定长度的数据列代替可变长度的数据列
memory 目前都适用固定长度的数据航存储，无论char还是varchar两者都做为char处理
innodb 建议使用varchar 内部的行存储格式没有区分固定长度和可变长度列（所有数据行都指向数据列值的头指针）
2， text blob
相比于char varchar 他们可以存储更多的数据，
但要防止空洞，注意及时进行optimize table
查询为了性能，一般采用散列值进行合成索引查询，只能用于精确匹配，同时尾部散列值尾部不能有空格

mysql> desc indexWithBlob;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | varchar(100) | YES  |     | NULL    |       |
| context    | blob         | YES  |     | NULL    |       |
| hash_value | varchar(40)  | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into indexWithBlob values(1, repeat('beijing', 2), md5(context));
Query OK, 1 row affected (0.01 sec)

mysql> insert into indexWithBlob values(2, repeat('beijing', 2), md5(context));
Query OK, 1 row affected (0.00 sec)

mysql> insert into indexWithBlob values(3, repeat('beijing', 2), md5(context));
Query OK, 1 row affected (0.00 sec)

mysql> select * from indexWithBlob;
+------+----------------+----------------------------------+
| id   | context        | hash_value                       |
+------+----------------+----------------------------------+
| 1    | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 |
| 3    | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 |
+------+----------------+----------------------------------+
3 rows in set (0.00 sec)

mysql> select * from indexWithBlob where hash_value= md5(repeat('beijing', 2008));
Empty set (0.00 sec)

mysql> select * from indexWithBlob where hash_value=md5(repeat('beijing', 2));
+------+----------------+----------------------------------+
| id   | context        | hash_value                       |
+------+----------------+----------------------------------+
| 1    | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 |
| 3    | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 |
+------+----------------+----------------------------------+
3 rows in set (0.00 sec)

mysql> select * from indexWithBlob where context like 'beijing%';
+------+----------------+----------------------------------+
| id   | context        | hash_value                       |
+------+----------------+----------------------------------+
| 1    | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 |
| 3    | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 |
+------+----------------+----------------------------------+
3 rows in set (0.00 sec)

前缀索引查询，可以模糊匹配查询多条
3，浮点数 定点数

mysql> create table fdemo(f float(8, 1));
Query OK, 0 rows affected (0.02 sec)

mysql> desc fdemo;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| f     | float(8,1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into fdemo values(1.23456);
Query OK, 1 row affected (0.00 sec)

mysql> select * from fdemo;
+------+
| f    |
+------+
|  1.2 |
+------+
1 row in set (0.00 sec)

mysql> insert into fdemo values(1.256778);
Query OK, 1 row affected (0.00 sec)

mysql> select * from fdemo;
+------+
| f    |
+------+
|  1.2 |
|  1.3 |
+------+
2 rows in set (0.00 sec)

浮点数会自动四舍五入

mysql> create table test(c1 float(10, 2), c2 decimal(10, 2));
Query OK, 0 rows affected (0.02 sec)

mysql> desc test;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| c1    | float(10,2)   | YES  |     | NULL    |       |
| c2    | decimal(10,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into test values(131072.32, 131072.32);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)

浮点数存在误差问题