mysql 数据类型

+-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(11) | YES | | NULL | | | in2 | int(5) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec)

插入测试数据

mysql小数表示分为：浮点数和定点数。定点数在mysql中用字符串形式存放，比浮点数精确，适用于表示货币等精度高的数据。两者都可以使用（M,D)方式表示，M：精度；D：标度。默认定点数在不指定精度时，默认会按照实际精度显示，二定点数默认整数位为10，小数位为0

创建测试表t1

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 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显示当地实际时间

+---------------------+---------------------+ | 2016-09-20 16:07:55 | 2016-09-20 15:07:55 | +---------------------+---------------------+ 1 row in set (0.00 sec)

mysql create table vc (v varchar(4), c char(4));

Query OK, 0 rows affected (0.05 sec)

插入测试数据

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

查看文件大小

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分离到单独的表中。

MySQL基础教程4——数据类型 MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
mysql之数据类型、建表以及约束 1）添加表外键： ALTER TABLE 表名 ADD FOREIGN KEY(外键名称) REFERENCES 主表名称(主键名称); 2）删除表外键： ALTER TABLE 表名 DROP FOREIGN KEY 约束名; 3）新增主键： ALTER TABLE 表名 ADD PRIMARY KEY(主键名称); 4）删除主键：主键只有一个直接删除即可 ALTER TABLE 表名 DROP PRIMARY KEY; 5）新增唯一约束： ALTER TABLE 表名 ADD UNIQUE(列名称1[,列名称2,..]); 6）删除唯一约束：其实就是