查看一下mysql版本
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.27-log |
+------------+
1 row in set (0.01 sec)
数值类型
整数类型
创建测试表t1
mysql> create table t1 (id1 int, in2 int(5));
Query OK, 0 rows affected (0.02 sec)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| in2 | int(5) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
插入测试数据
mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.02 sec)
mysql> select * from t1;
+------+------+
| id1 | in2 |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.01 sec)修改字段类型，加入zerofill,可以看到数值前面用字符0填充了剩余的宽度
mysql> alter table t1 modify id1 int zerofill;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify in2 int(5) zerofill;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------------+-------+
| id1 | in2 |
+------------+-------+
| 0000000001 | 00001 |
+------------+-------+
1 row in set (0.01 sec)
测试插入大于宽度限制的值，可见宽度限制并不影响数据的正常保存
mysql> insert into t1 values(1,1111111);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------------+---------+
| id1 | in2 |
+------------+---------+
| 0000000001 | 00001 |
| 0000000001 | 1111111 |
+------------+---------+
2 rows in set (0.00 sec)
小数类型
mysql小数表示分为：浮点数和定点数。定点数在mysql中用字符串形式存放，比浮点数精确，适用于表示货币等精度高的数据。两者都可以使用（M,D)方式表示，M：精度；D：标度。默认定点数在不指定精度时，默认会按照实际精度显示，二定点数默认整数位为10，小数位为0
创建测试表t1
mysql> create table t1 (
-> id1 float(5,2) default null,
-> id2 double(5,2) default null,
-> id3 decimal(5,2) default null);
Query OK, 0 rows affected (0.03 sec)插入数据1.23，数据都正常显示
mysql> insert into t1 values (1.23,1.23,1.23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)插入数据1.234，id1、id2犹豫标度限制，舍去最后一位；id3显示被截断
mysql> insert into t1 values (1.234,1.234,1.234);
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> select * from t1;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.00 sec)
将字段的精度及标度都去掉，插入数据1.23。可以看到id1、id2正常，id3截断。
mysql> alter table t1 modify id1 float;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify id2 double;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify id3 decimal;
Query OK, 2 rows affected, 2 warnings (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column 'id3' at row 1 |
| Note | 1265 | Data truncated for column 'id3' at row 2 |
+-------+------+------------------------------------------+
2 rows in set (0.00 sec)
mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id1 | float | YES | | NULL | |
| id2 | double | YES | | NULL | |
| id3 | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t1 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t1;
+-------+-------+------+
| id1 | id2 | id3 |
+-------+-------+------+
| 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1 |
| 1.234 | 1.234 | 1 |
+-------+-------+------+
3 rows in set (0.00 sec)
通过上面的例子，可以看到浮点数如果没有精度和标度，会安装实际精度显示，如果有精度和标度，会四舍五入。定点数如果不写精度和标度，会按照默认值decimal(10,0)来进行操作，如果数据超越了精度和标度值，系统会报错。
日期时间类型
- 根据实际需要选择最小存储的日期类型。如果只需要记录年份，南无year类型即可。
- 如果需要记录年月日时分秒，并且记录年份比较久远，那么最好选择datetime，因为datetime比timestamp日期范围长
- 如果日期需要让不同时区的用户使用，那么最好使用timestamp
通过测试可知，datetime为date和time的组合。
mysql> create table t (
-> d date,
-> t time,
-> dt datetime);
Query OK, 0 rows affected (0.02 sec)
mysql> desc t;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t values (now(),now(),now());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2016-09-20 | 14:51:08 | 2016-09-20 14:51:08 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
timestamp类型也可用来表示日期
mysql> create table t (id1 timestamp,id2 datetime);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(now(),now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+---------------------+---------------------+
| id1 | id2 |
+---------------------+---------------------+
| 2016-09-20 15:07:55 | 2016-09-20 15:07:55 |
+---------------------+---------------------+
1 row in set (0.00 sec)
修改时区，可见timestamp显示当地实际时间
#当前为系统时区（东八区）
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)
#修改时区
mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+---------------------+---------------------+
| id1 | id2 |
+---------------------+---------------------+
| 2016-09-20 16:07:55 | 2016-09-20 15:07:55 |
+---------------------+---------------------+
1 row in set (0.00 sec)
字符串类型
varchar char
创建测试表vc
mysql> create table vc (v varchar(4), c char(4));
Query OK, 0 rows affected (0.05 sec)
插入测试数据
mysql> insert into vc values ('ab ','ab ');
Query OK, 1 row affected (0.01 sec)
显示查询结果
mysql> select length(v), length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
| 4 | 2 |
+-----------+-----------+
1 row in set (0.01 sec)
mysql> select concat(v,'+'),concat(c,'+') from vc;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab + | ab+ |
+---------------+---------------+
1 row in set (0.06 sec)
可以看到char类型自动去除尾部的空格
text blob
- 主要区别
- text只能存字符数据，如日记
- blob用来存二进制数据，如照片
blob和text会引起一些性能问题，特别是在执行大量删除操作时。删除操作会造成空洞，建议使用OPTIMIZE TABLE进行碎片整理。
mysql> create table t (id varchar(100),context text);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values (1,repeat('haha',100));
Query OK, 1 row affected (0.00 sec)
mysql> insert into t values (2,repeat('haha',100));
Query OK, 1 row affected (0.00 sec)
mysql> insert into t values (3,repeat('haha',100));
Query OK, 1 row affected (0.00 sec)
mysql> insert into t select * from t;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
......
mysql> insert into t select * from t;
Query OK, 393216 rows affected (4.05 sec)
Records: 393216 Duplicates: 0 Warnings: 0查看文件大小
# du -sh t.*
12K t.frm
365M t.ibd删除部分数据；查看文件大小，没变化
mysql> delete from t where id=1;
Query OK, 262144 rows affected (1.29 sec)
# du -sh t.*
12K t.frm
365M t.ibd
对表进行OPTIMIZE
mysql> optimize table t;
+--------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status | OK |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (8.34 sec)
[root@db3 test]# du -sh t.*
12K t.frm
237M t.ibd
可见空洞被回收
对于blob和text字段的查询性能问题。可以使用合成索引和前缀索引进行优化。
合成索引示例
mysql> create table t (
-> id varchar(100),
-> context blob,
-> hash_value varchar(40));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values (1,repeat('beijing',2),md5(context));
Query OK, 1 row affected (0.01 sec)
mysql> insert into t values (2,repeat('beijing',2),md5(context));
Query OK, 1 row affected (0.00 sec)
mysql> insert into t values (3,repeat('beijing 2008',2),md5(context));
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+--------------------------+----------------------------------+
| id | context | hash_value |
+------+--------------------------+----------------------------------+
| 1 | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 |
| 2 | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 |
| 3 | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
3 rows in set (0.00 sec)
mysql> select * from t where hash_value=md5(repeat('beijing 2008',2));
+------+--------------------------+----------------------------------+
| id | context | hash_value |
+------+--------------------------+----------------------------------+
| 3 | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
1 row in set (0.00 sec)
合成索引只能用于精确匹配。
使用前缀索引实现模糊查询
mysql> create index idx_blob on t(context(100));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc select * from t where context like 'beijing%'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: idx_blob
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.00 sec)
避免对有blob和text字段的表进行全扫描，应尽量使用where子句并取所需字段的信息，避免造成大量的网络传输。
某些情况下，可以考虑将blob和text分离到单独的表中。
