MySQL中的数据类型 [数值型、字符串型、时间日期型]

MySQL中的数据类型 [数值型、字符串型、时间日期型]

MySQL中各数据类型

1. 数值类型(整型)

类型	数据大小	类型 （无符号：unsigned）	数据大小	存储空间
tinyint	-128 到 127	tinyint unsigned	0 到 255	1
smallint	-32768 到 32767	smallint unsigned	0 到 65535	2
mediumint	-8388608 到 8388607	mediumint  unsigned	0 到 16777215	3
int 或 integer	-2147483648 到 2147483647	int unsigned 或 integer unsigned	0 到 4294967295	4
bigint	-9223372036854775808 到 9223372036854775807	bigint unsigned	0 到 18446744073709551615	8

2. 时间和日期类型

类型	“ 零 ” 值	存储空间
time	'00:00:00'	3
date	'0000-00-00'	3
datetime	'0000-00-00 00:00:00'	8
year	0000	1
timestamp	0000000000000000	4

3. 字符串类型

char与varchar的区别：

char：固定长度字符串。长度可以指定为从0到255的任何值。当保存char值时，在它们的右边填充空格以达到指定的长度。当检索到char值时，尾部的空格被删除掉。

varchar：可变长度字符串。长度可以指定为0到65,535之间的值。另外varchar需要用额外的1-2个字节存储字符串长度 。当检索到varchar值时，尾部的空格仍然保留。因此，VARCHAR列的有效最大长度为65,532字符。

• varchar(M)：1. 当字符串长度M小于255时，用额外的1个字节来记录长度，存储空间L+1。[L为值的长度]

                              2. 当字符串长度M大于255时，用额外的2个字节来记录长度，存储空间L+2。[L为值的长度]

       char(M)：存储空间 = 指定的长度 (0 <= M <= 255)

• 比如：char(255)和varchar(255)，在存储字符串"hello world"时：

       char会用255个字节的空间放那个11个字符；
       varchar就不会用255个，它先计算字符串长度为11，然后再加上一个记录字符串长度的字节，一共用12个字节存储，这样varchar在存储不确定长度的字符串时会大大减少存储空间。

• 从效率上考虑，固定长度，存储空间一次性分配，CHAR更好(长度较短的字段:门牌号等、固定长度的字段:性别、身份证号、手机号等)。
• 从空间上考虑，可变长度，根据长度动态调整存储空间，VARCHAR更好。
• 下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：
  

值	char(4)	存储空间 btyes	varchar(4)	存储空间 btyes
''	'    '	4	''	1
'ab'	'ab  '	4	'ab '	3
'abcd'	'abcd'	4	'abcd'	5
'abcdefgh'	'abcd'	4	'abcd'	5

另外：

1. varchar(n)：为n个字节的可变长度，且非Unicode的字符数据。n的值必须介于1与8,000之间的数值。存储大小是输入数据的字节的实际长度

2. nvarchar(n)：为n个字符的可变长度，且Unicode字符数据。n的值必须介于1与4,000之间的数值。存储大小是所输入字符个数的两倍。所输入的数据字符长度可以为零。

4. 数值类型的存储空间

类型	存储空间
tinyint	1
smallint	2
mediumint	3
int，integer	4
bigint	8
float(p)	如果0 <= p <= 24为4个字节, 如果25 <= p <= 53为8个字节
double	8
decimal(M,D),numeric(M,D)	变长
bit(M)	大约(M+7)/8个字节

decimal和numeric类型在MySQL中视为相同的类型。它们用于保存必须为确切精度的值，并且要指定标度和精度，例如货币数据。

• 实际上小数数值只有两种数据类型：float 和 decimal，分别是近似数值和精确数值。
• 其他小数类型，都可以使用float和decimal来替代，例如，双精度（double precision）数据类型等价于 float(53)，real等价于float(24)。
• numeric是 decimal的同义词，应该避免在程序中直接使用 double precision、real和numeric，而是用 float(24) 、float(53)和decimal 代替。

5. 字符串类型的存储空间 [L为值的长度]

类型	存储空间 (字节)
char(M)	M，0 <= M <= 255
varchar(M)	L+1 或 L+2，其中L <= M 且0 <= M <= 65532
binary(M)	M，0 <= M <= 255
varbinary(M)	L+1个字节，其中L <= M 且0 <= M <= 255
tinyblob tinytext	L+1个字节，其中L < 28
blob，text	L+2个字节，其中L < 216
mediumblob mediumtext	L+3个字节，其中L < 224
longblob longtext	L+4个字节，其中L < 232
enum('value1','value2',...)	1或2个字节，取决于枚举值的个数(最多65,535个值)
set('value1','value2',...)	1、2、3、4或者8个字节，取决于set成员的数目(最多64个成员)

VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度(用前面的表中的L表示)。

varbinary、binary、char、varchar异同：

1.

binary 与 varbinary 类型和char与varchar类型是相似的，只是他们存储的是二进制数据，也就是说他们是包含字节流而不是字符流，他们有二进制字符的集合和顺序，他们的对比，排序是基于字节的数值进行的。

binary与varbinary的最大长度和char与varchar是一样的，只不过他们是定义字节长度，而char和varchar对应的是字符长度。

2.

存储和取出时对尾部空格的处理：

　　char(N)用来存储非二进制字符串，插入时，对于少于N个字符的会自动在尾部加空格，查询时，尾部的空格就会被丢弃掉

　　vachar(N) 用来存储非二进制字符串，插入时，对于少于N个字符的不填补空格，查询时，尾部的空格不会被丢弃掉

　　binary(N)存储二进制字符串，插入进，少于N个字节的会自动在尾部加0x00，取出时，所有的字节都保留，返回定义长度的字节长度，在比较的时候，所有的字节都是有效的，并且0x00<space (space对应的是0x20)

　　varbinary在插入不会去填补0x00字节，查询的时候也不会丢弃任何字节，在比较的时候，所有的字节都是有效的，并且0x00<space (space对应的是0x20)

3.

大小比较时：

　　char与varchar的字符比较中，是忽略大小写与最后的空格的。

       binary及varbinary的字节比较中，所有的信息都不会被忽略。

 

参考文档：

MySql中char、varchar和nvarchar的区别：https://www.cnblogs.com/zeroingToOne/p/9568687.html

MySql数据类型的取值范围：https://www.csdn.net/gather_25/MtTaIgzsMDg3MC1ibG9n.html

MySql中bigint、int、mediumint、smallint 和 tinyint的取值范围：https://blog.csdn.net/iteye_7932/article/details/82399758

数据库中char, nchar,varchar, nvarchar的差异：https://jingyan.baidu.com/article/c85b7a645639ac003bac959a.html

SQL Server 小数类型（float 和 decimal）：https://www.cnblogs.com/ljhdo/p/4910699.html

mysql中varbinary、binary、char、varchar异同：https://www.cnblogs.com/zejin2008/p/6606120.html

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<wiz_tmp_tag id="wiz-table-range-border" contenteditable="false" style="display: none;">