mysql5.6常见数据类型

最新推荐文章于 2022-04-29 14:52:16 发布

久违的太阳

最新推荐文章于 2022-04-29 14:52:16 发布

阅读量1.7k

点赞数

分类专栏： mysql基础文章标签： mysql datatype

本文链接：https://blog.csdn.net/su377486/article/details/49935425

版权

mysql基础专栏收录该内容

10 篇文章 0 订阅

订阅专栏

以下所述mysql版本为5.6.27，不同版本直接可能存在差异

1.整数类型

Data Type	Storage Required
`TINYINT`	1 byte
`SMALLINT`	2 bytes
`MEDIUMINT`	3 bytes
`INT`, `INTEGER`	4 bytes
`BIGINT`	8 bytes
`FLOAT(p)`	4 bytes if 0 <= `p` <= 24, 8 bytes if 25 <= `p` <= 53
`FLOAT`	4 bytes
`DOUBLE [PRECISION]`, `REAL`	8 bytes
`DECIMAL(M,D)`, `NUMERIC(M,D)`	Varies; see following discussion
`BIT(M)`	approximately (`M`+7)/8 bytes

DECIMAL 对DECIMAL(M,D) ，M表示float的总共长度，D表示小数点后面的长度，例如decimal(7,4) 表示-999.9999，如果不写，默认是decimal(10,0)表示

mysql> create table t4 (id int(5),x int(2));

mysql中的int(5)指定长度并不能现在int的使用长度，例如：

mysql> insert into t4 values(111111,111);

bit型表示位型，当插入数字的时候，会将数字转换为二进制，如果转换为的二进制数大于bit所指定的大小，那么查询的时候将显示空，默认bit为bin(1),例如下面的例子：

   
   
    
    
     
     mysql> create table t5 (id bit(10));
    
    
    
    
     
     mysql> insert into t5 values(10);
    
    
    
    
     
     mysql> select bin(id),hex(id) from t5;
    
    
    
    
     
     +---------+---------+
    
    
    
    
     
     | bin(id) | hex(id) |
    
    
    
    
     
     +---------+---------+
    
    
    
    
     
     | 1010    | A       |.
    
    
    
    
     
     +---------+---------+

2.时间类型

如果用来表示年月日，一般用date

如果用来表示年月日时分秒，一般用datetime

如果只用来表示分秒，通常用time

如果需要经常插入当前的系统时间，一般使用timestamp

Data Type	Storage Required Before MySQL 5.6.4	Storage Required as of MySQL 5.6.4
`YEAR`	1 byte	1 byte
`DATE`	3 bytes	3 bytes
`TIME`	3 bytes	3 bytes + fractional seconds storage
`DATETIME`	8 bytes	5 bytes + fractional seconds storage
`TIMESTAMP`	4 bytes	4 bytes + fractional seconds storage

下例子：

   
   
    
    
     
     mysql> create table ttime (a date,b datetime,c timestamp,d time,e year);
    
    
    
    
     
     mysql> insert into ttime values(current_timestamp,current_timestamp,current_timestamp,current_timestamp,current_timestamp);
    
    
    
    
     
     Query OK, 1 row affected, 1 warning (0.01 sec)
    
    
    
    
     
     mysql> select * from ttime;
    
    
    
    
     
     mysql> select * from ttime;
    
    
    
    
     
     +------------+---------------------+---------------------+----------+------+
    
    
    
    
     
     | a          | b                   | c                   | d        | e    |
    
    
    
    
     
     +------------+---------------------+---------------------+----------+------+
    
    
    
    
     
     | 2015-11-19 | 2015-11-19 19:30:30 | 2015-11-19 19:30:30 | 19:30:30 | 2015 |
    
    
    
    
     
     +------------+---------------------+---------------------+----------+------+

如果对timestamp不指定输入或者输入空值，timestamp会取当前操作系统时间，因此timestamp适合用在经常去操作系统时间的场景，注意只有第一个timestamp会出现这种情况，如果还有第二个timestamp类型，在不指定默认值为current_timestamp的情况下，其默认值为0。

timestamp一个重要特点是和时区相关，如果不同的时区查看的值是不同的，而且timestamp只支持到2038年的某一天

可能在以前的版本中只能有一个timestamp的默认值指定为current_timestamp，我测试的版本为5.6.27，可以有多个timestamp类型的默认值设置为current_timestamp

   
   
    
    
     
     mysql> insert into ttime(a) values(current_timestamp);
    
    
    
    
     
     Query OK, 1 row affected, 1 warning (0.00 sec)
    
    
    
    
     
      
    
    
    
    
     
     mysql> select * from ttime;
    
    
    
    
     
     +------------+---------------------+---------------------+----------+------+
    
    
    
    
     
     | a          | b                   | c                   | d        | e    |
    
    
    
    
     
     +------------+---------------------+---------------------+----------+------+
    
    
    
    
     
     | 2015-11-19 | 2015-11-19 19:30:30 | 2015-11-19 19:30:30 | 19:30:30 | 2015 |
    
    
    
    
     
     | 2015-11-19 | NULL                | 2015-11-19 19:51:01 | NULL     | NULL |
    
    
    
    
     
     +------------+---------------------+---------------------+----------+------+
    
    
    
    
     
     2 rows in set (0.00 sec)

   
   
    
    
     
     mysql> select version();
    
    
    
    
     
     +------------+
    
    
    
    
     
     | version()  |
    
    
    
    
     
     +------------+
    
    
    
    
     
     | 5.6.27-log |
    
    
    
    
     
     +------------+
    
    
    
    
     
     1 row in set (0.00 sec)
    
    
    
    
     
      
    
    
    
    
     
     mysql> show create table tstamp\G
    
    
    
    
     
     *************************** 1. row ***************************
    
    
    
    
     
            Table: tstamp
    
    
    
    
     
     Create Table: CREATE TABLE `tstamp` (
    
    
    
    
     
       `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    
    
    
     
       `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    
    
    
     
       `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    
    
    
     
       `d` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
    
    
    
    
     
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    
    
    
     
     1 row in set (0.00 sec)
    
    
    
    
     
     mysql> select * from tstamp;
    
    
    
    
     
     +---------------------+---------------------+---------------------+---------------------+
    
    
    
    
     
     | a                   | b                   | c                   | d                   |
    
    
    
    
     
     +---------------------+---------------------+---------------------+---------------------+
    
    
    
    
     
     | 2015-11-19 20:06:49 | 2015-11-19 20:06:49 | 2015-11-19 20:07:56 | 0000-00-00 00:00:00 |
    
    
    
    
     
     | 2015-11-19 20:08:27 | 2015-11-19 20:08:27 | 2015-11-19 20:08:27 | 0000-00-00 00:00:00 |
    
    
    
    
     
     +---------------------+---------------------+---------------------+---------------------+

3.字符类型

支持的字符类型：

Data Type	Storage Required
`CHAR(M)`	`M` × `w` bytes, 0 `<= M <=` 255, where `w` is the number of bytes required for the maximum-length character in the character set. See Section 14.2.6.7, “Physical Row Structure” for information about `CHAR` data type storage requirements for `InnoDB` tables.
`BINARY(M)`	`M` bytes, 0 `<= M <=` 255
`VARCHAR(M)`, `VARBINARY(M)`	`L` + 1 bytes if column values require 0 − 255 bytes, `L` + 2 bytes if values may require more than 255 bytes
`TINYBLOB`, `TINYTEXT`	`L` + 1 bytes, where `L` < 2⁸
`BLOB`, `TEXT`	`L` + 2 bytes, where `L` < 2¹⁶
`MEDIUMBLOB`, `MEDIUMTEXT`	`L` + 3 bytes, where `L` < 2²⁴
`LONGBLOB`, `LONGTEXT`	`L` + 4 bytes, where `L` < 2³²
`ENUM('value1','value2',...)`	1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
`SET('value1','value2',...)`	1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) 来源： <http://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html>

由上表可以看出char是0-255 字符，不是字节

varchar支持0-65535个字节

char和varchar的区别是一个是定长，一个是变长

char会删除字符串末尾的空格，varchar则不会

对于相同长度的char的效率会比varchar高，但是varchar的存储空间会比char小。

BLOB和TEXT的区别是BLOB是二进制字符串，一般保存图片什么的，text类似于oracle中的clob。

binary和varbinary类似于char和varchar。但是他们保存的二进制类型.

ENUM 是枚举行，创建表的时候需要通过枚举的方式指定允许的值，1-255个成员需要一个字节存储，对于255-65535个成员需要2个字节存储

枚举是不区分大小写的，如果对表中插入一条不在枚举范围内的数据会报错ERROR 1265 (01000)

也可以通过插入1，2，3...表示插入的为枚举中的第1,2,3个值：

   
   
    
    
     
     mysql> create table t8 (a enum('a','b','c','d','e'));
    
    
    
    
     
     Query OK, 0 rows affected (0.01 sec)
    
    
    
    
     
      
    
    
    
    
     
     mysql> insert into t8 values(2);
    
    
    
    
     
     Query OK, 1 row affected (0.00 sec)
    
    
    
    
     
      
    
    
    
    
     
     mysql> insert into t8 values(5);
    
    
    
    
     
     Query OK, 1 row affected (0.00 sec)
    
    
    
    
     
      
    
    
    
    
     
     mysql> insert into t8 values('c');
    
    
    
    
     
     Query OK, 1 row affected (0.00 sec)
    
    
    
    
     
      
    
    
    
    
     
     mysql> select * from t8;
    
    
    
    
     
     +------+
    
    
    
    
     
     | a    |
    
    
    
    
     
     +------+
    
    
    
    
     
     | b    |
    
    
    
    
     
     | e    |
    
    
    
    
     
     | c    |
    
    
    
    
     
     +------+
    
    
    
    
     
     3 rows in set (0.00 sec)
    
    
    
    
     
      
    
    
    
    
     
     mysql> insert into t8 values('g');
    
    
    
    
     
     ERROR 1265 (01000): Data truncated for column 'a' at row 1

set类型和enum类型很像，但是set类型可以一次选取多个成员，而enum只能是一个：

   
   
    
    
     
     mysql> create table t9(a set('a','b','c','d','e'));
    
    
    
    
     
     Query OK, 0 rows affected (0.00 sec)
    
    
    
    
     
     mysql> insert into t9 values('a','b');
    
    
    
    
     
     ERROR 1136 (21S01): Column count doesn't match value count at row 1
    
    
    
    
     
     mysql> insert into t9 values('ab');
    
    
    
    
     
     ERROR 1265 (01000): Data truncated for column 'a' at row 1
    
    
    
    
     
     mysql> insert into t9 values('a,b');
    
    
    
    
     
     Query OK, 1 row affected (0.00 sec)
    
    
    
    
     
      
    
    
    
    
     
     mysql> insert into t9 values('c,d,e');
    
    
    
    
     
     Query OK, 1 row affected (0.00 sec)
    
    
    
    
     
      
    
    
    
    
     
     mysql> select * from t9;
    
    
    
    
     
     +-------+
    
    
    
    
     
     | a     |
    
    
    
    
     
     +-------+
    
    
    
    
     
     | a,b   |
    
    
    
    
     
     | c,d,e |
    
    
    
    
     
     +-------+
    
    
    
    
     
     2 rows in set (0.00 sec)