mysql 数据类型

最新推荐文章于 2023-06-26 22:32:53 发布

Miles_sys

最新推荐文章于 2023-06-26 22:32:53 发布

阅读量401

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/Maikilee_sys/article/details/52589674

版权

mysql 专栏收录该内容

56 篇文章 0 订阅

订阅专栏

查看一下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)