mysql5.6常见数据类型

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

1.整数类型

Data TypeStorage RequiredTINYINT1 byteSMALLINT2 bytesMEDIUMINT3 bytesINT, INTEGER4 bytesBIGINT8 bytesFLOAT(p)4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53FLOAT4 bytesDOUBLE [PRECISION], REAL8 bytesDECIMAL(M,D), NUMERIC(M,D)Varies; see following discussionBIT(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 TypeStorage Required Before MySQL 5.6.4Storage Required as of MySQL 5.6.4YEAR1 byte1 byteDATE3 bytes3 bytesTIME3 bytes3 bytes + fractional seconds storageDATETIME8 bytes5 bytes + fractional seconds storageTIMESTAMP4 bytes4 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 TypeStorage RequiredCHAR(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 <= 255VARCHAR(M), VARBINARY(M)L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytesTINYBLOB, TINYTEXTL + 1 bytes, where L < 2⁸BLOB, TEXTL + 2 bytes, where L < 2¹⁶MEDIUMBLOB, MEDIUMTEXTL + 3 bytes, where L < 2²⁴LONGBLOB, LONGTEXTL + 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)