你的位置:首页 > 数据库

[数据库]MySQL学习笔记之数据类型


  数据类型在所有的数据库使用当中,都是避免不了的部分。以前每次写SQL语句,对于定义成哪种数据类型总是迷迷糊糊,今天就来彻底弄清。以下介绍仅针对MySQL 5.5以上版本。

一、字符串类型

1、char和varchar

  以前在建表语句中,定义到字符串类型只会用varchar,但是它后面要指定一个数字,这个数字具体代表什么一知半解。char和varchar两个类型后面都要跟上一个数字,例如char(10),varchar(20)。

  看过官方文档后发现,这里的数字表示的是最大字符个数,注意是字符数,而不是字节数!在严格的SQL模式下,插入的字符串数据如果字符数超过这个值,分两种情况:

  ①如果尾部超出部分全都是空格,则截去超出部分后插入,产生一个警告;

  ②如果尾部超出部分含非空格字符,插入失败,直接报错。

  在非严格SQL模式下,两种情况都会直接截去超出部分后插入。

  

  括号里面的数字究竟能设为多大呢?官方说明是,char的可以设为0~255,varchar的可以设为0~65535。对于char没有任何问题,但对于varchar,经过实际操作发现没那么简单。

  MySQL不管对于什么样的存储引擎,有一个统一的设定,那就是一行数据的总字节数,不能超过65535。而字符有英文字符,有中文字符,还有其他语言的字符,而且各种编码下,有的字符一个占1字节,有的字符一个占2个或3个字节。而MySQL的默认编码类型是utf-8,一个中文字符占到3个字节,如果某个字段指定为varchar(65535),那么单这一个字段,最大字节数就可能达到65535*3,远远超出了一行数据的最大设定65535字节。比如执行如下语句:

create table t_planet(  name varchar(65535));

 

会提示如下错误: create table t_planet ( name varchar(65535) ) Error Code: 1074. Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead

注意这里的错误提示,它提示name字段的最大字符数只能为21845,也就是65535除以3。因为在默认的utf-8编码下,最坏情况下会出现全为中文字符的情况,根据一个中文字符占3字节,所以最大字符数就是65535/3。

如果把字符集改为所有字符只占1字节的latin1,字符数最大值就可以达到65535了。

create table t_planet(  name varchar(65535)) character set latin1;  //整张表格的字符集指定为latin1

 

   以上只是对最大字符数做的设定。具体到一个字符串究竟占用了多少字节,char和varchar的计算方法不一样。首先当然要根据实际的字符集,算出各个字符各自占用的字节数,然后求和。这里为了方便起见,假定每个字符都占一个字节。这样,对于char(M),实际占用的字节数就永远都是M,因为如果字符数不满M,后面用空格补充,空格也占用字节数。当然这是存储时的情况,select显示时是把尾部空格全部去掉的。

  而对于varchar(M),实际占用的字节数绝大多数小于M,输入几个字符就是几个字符,不会自动填充。当然,因为字符数不确定,就要额外增加1到2个字节记录字符个数,<=255个字符用1字节记录,>255&&<=65535个字符就用2字节记录。这样,一个varchar(M)的字符串,实际占用空间大小就是“实际字符数+1或+2”。

  由上面分析可以得知,varchar表面上完全可以取代char,实际未必,因为varchar有额外的空间占用。更重要的是,在以字符串创建索引、进行排序等操作时,由于char的字符数固定,拥有更高的效率。比方说有一个学号字段,有9位数也有10位数,表面上应该用可变字符串varchar,实际上用固定字符串char(10)是更好的选择。

 

  未完待续。。。