MySQL系列(二)：字段类型

主要分为数值类型、字符串类型和时间日期类型。

一、数值型

一）、整数型

• tinyint：迷你整型，使用一个字节存储，表示的状态最多为256种(常用)
• smallint：小整型,使用2个字节存储,表示的状态最多为65536种
• mediumint：中整型, 使用3个字节存储
• int：标准整型, 使用4个字节存储(常用)
• bigint：大整型,使用8个字节存储

• 整数型只能插入范围内的整型，SQL中的数值类型全部都是默认有符号的，会分正负
• 有时候需要使用无符号数据：需要给数据类型限定： int unsigned;        -- 无符号，从0开始
• 显示宽度：没有特别的含义，只是默认的告诉用户可以显示的形式，不会改变数据本身的大小
• 显示宽度的意义：当数据不够显示宽度的时候，会自动让数据变成对应的显示宽度。通常需要搭配一个前导0来增加宽度，不改变值大小
• zerofill(零填充)，零填充会导致数值自动变成无符号
• 零填充的意义(显示宽度)：保证数据格式

-- 创建整型表
create table my_int(
     int_1 tinyint,
     int_2 smallint,
     int_3 int,
     int_4 bigint
)charset utf8;


insert into my_int values(100,100,100,100);             -- 有效数据
insert into my_int values('a','b','199','f');           -- 无效数据: 类型限定
insert into my_int values(255,10000,100000,1000000);    -- 错误: 超出范围


alter table my_int add int_5 tinyint unsigned;         -- 无符号类型


insert into my_int values(127,1000,10000,1000000,255);
alter table my_int add int_6 tinyint(1) unsigned;      -- 指定显示宽度为1;
 

insert into my_int values(127,0,0,0,255,255);
alter table my_int add int_7 tinyint(2) zerofill;      -- 显示宽度为2,0填充


insert into my_int values(1,1,1,1,1,1,1);
insert into my_int values(100,100,100,100,100,100,100);

二）、小数型

带有小数点或者范围超出整型的数值类型。

1.浮点型

小数点浮动，精度有限，会丢失精度。超出指定范围之后，会自动四舍五入，丢失精度。

• float：单精度，占用4个字节存储数据，精度范围大概为7位左右。
• double： 双精度，占用8个字节存储数据， 精度方位大概为15位左右。

• 使用float(总长度,小数部分长度)建立字段的浮点型，整数部分长度为总长度-小数部分长度
• 可以插入小数或者科学计数法，整型部分是不能超出长度，小数部分可以超出长度(自动四舍五入)。如果是因为系统进位导致整数部分超出指定的长度，那么系统也允许成立。

-- 浮点数表
create table my_float(
     f1 float,
     f2 float(10,2),    -- 10位在精度范围之外
     f3 float(6,2)      -- 6位在精度范围之内
)charset utf8;

 
insert into my_float values(1000.10,1000.10,1000.10);            -- 符合条件
insert into my_float values(1234567890,12345678.90,1234.56);     -- 符合条件
insert into my_float values(3e38,3.01e7,1234.56);                -- 符合条件
insert into my_float values(9999999999,99999999.99,9999.99);     -- 最大值
 

--超出长度插入数据
insert into my_float values(123456,1234.123456768,123.9876543);  -- 小数部分OK
insert into my_float values(123456,1234.12,12345.56);            -- 整数部分超出

2.定点型

小数点固定， 精度固定， 不会丢失精度。

保证整数部分不会被四舍五入(不会丢失精度)，小数部分有可能(理论小数部分也不会丢失精度)。

• decimal(M,D)：

• 定点数的整数部分一定不能超出长度(不可以进位)，小数部分长度可以随意超出(自动四舍五入)
• 浮点数如果进位导致长度溢出没有问题，但是定点数不行

-- 创建定点数表
create table my_decimal(
     f1 float(10,2),
     d1 decimal(10,2)
)charset utf8;


-- 插入数据
insert into my_decimal values(12345678.90,12345678.90);  -- 有效数据
insert into my_decimal values(1234.123456,1234.1234356); -- 小数部分超出,ok


insert into my_decimal values(99999999.99,99999999.99);  -- 没有问题
insert into my_decimal values(99999999.99,99999999.999); -- 进位超出范围

二、日期时间型

• datetime：时间日期，YYYY-mm-dd HH:ii:ss，范围从1000到9999年。0值: 0000-00-00 00:00:00。
• date：日期，datetime中的date部分。
• time：时间(段)，指定的某个区间之间，-时间到+时间。
• timestamp：时间戳，从1970年开始。格式与datetime一致。
• year：年份，两种形式：year(2)和year(4)，1901-2156。

• time：可以是很大的负数，year可以使用2位数插入，也可以使用4位数。
• Timestamp：只要当前所在的记录被更新，该字段一定会自动更新成当前时间。

-- 创建时间日期表
create table my_date(
     d1 datetime,
     d2 date,
     d3 time,
     d4 timestamp,
     d5 year
)charset utf8;



-- 插入数据
insert into my_date values('2015-9-28 11:50:36','2015-9-28','11:50:54','2015-9-28 11:51:08',2015);
 

-- 时间使用负数
insert into my_date values('2015-9-28 11:50:36','2015-9-28','-11:50:54','2015-9-28 11:51:08',2015);
insert into my_date values('2015-9-28 11:50:36','2015-9-28','-211:50:54','2015-9-28 11:51:08',2015);
insert into my_date values('2015-9-28 11:50:36','2015-9-28','-2 11:50:54','2015-9-28 11:51:08',2015); -- -2过去2天:48
 

-- year可以使用2位或者4位
insert into my_date values('2015-9-28 11:50:36','2015-9-28','11:50:54','2015-9-28 11:51:08',69);
insert into my_date values('2015-9-28 11:50:36','2015-9-28','11:50:54','2015-9-28 11:51:08',70);
 


-- timestamp: 修改记录
update my_date set d1 = '2015-9-28 11:55:45' where d5 = 2069;

使用PHP作为开发语言的时候，一般可以将时间日期字段定为int型。

三、字符串型

char、varchar、text 、 blob、enum和set。

一）、定长字符串

char，磁盘(二维表)在定义结构的时候，就已经确定了最终数据的存储长度。

char(L)：L代表length, 可以存储L长度的字符， 最大长度值可以为255。

Char(4)：在UTF8 环境下，需要4 * 3 = 12个字节。

定长的磁盘空间比较浪费，但是效率高。如果数据基本上确定长度都一样，就可以使用定长，如身份证、电话号码、手机号码等。

二）、变长字符串

varchar，在分配空间的时候，按照最大的空间分配。但是实际上最终用了多少，根据具体的数据来确定。

Varchar(L)：L为理论长度是65536个的字符，会多处1到2个字节来确定存储的实际长度。一般如果长度超过255，会使用文本字符串text。

Varchar(10)：10 * 3 + 1 = 31(bytes)，存储了3个汉字为3 * 3 + 1 = 10(bytes)。

变长的磁盘空间比较节省，但是效率低。如果数据不能确定长度, 如姓名、地址等。

三）、文本字符串

• text：存储文字(二进制数据，实际上都是存储路径)
• blob：存储二进制数据(很少使用)

四）、枚举字符串

enum, 事先将所有可能出现的结果都设计好, 存储的数据必须是规定好的数据中的一个。

• 数据只能是规定的数据中的其中一个。
• 节省存储空间(单选框)，枚举实际存储的是数值而不是字符串本身，所以可以直接插入数值。

枚举原理：枚举在进行数据规范的时候(定义的时候)，系统会自动建立一个数字与枚举元素的对应关系(关系放到日志中)；然后在进行数据插入的时候，系统自动将字符转换成对应的数字存储，然后在进行数据提取的时候，系统自动将数值转换成对应的字符串显示。

-- 创建枚举表
create table my_enum(
     gender enum('男','女','保密')
)charset utf8;


-- 插入数据
insert into my_enum values('男'),('保密');      -- 有效数据


-- 错误数据
insert into my_enum values('male');            -- 错误: 没有该元素


-- 将字段结果取出来进行+0运算
-- 将数据取出来 + 0 就可以判断出原来的数据存的到底是字符串还是数值
-- 如果是字符串最终结果永远为0, 否则就是其他值
select gender + 0, gender from my_enum;


-- 数值插入枚举元素
insert into my_enum values(1),(2);

五）、集合字符串

集合跟枚举很类似，实际存储的是数值，而不是字符串(集合是多选)。

• 可以使用多个元素字符串组合，也可以直接插入数值。
• 每一个元素都是对应一个二进制位，被选中为1，没有则为0， 最后反过来。
• 集合中元素的顺序没有关系，最终系统都会去匹配顺序。

-- 创建集合表
create table my_set(
     hobby set('篮球','足球','乒乓球','羽毛球','排球','台球','网球','棒球')
--                    足球                          台球   网球
-- 集合中: 每一个元素都是对应一个二进制位,被选中为1,没有则为0: 最后反过来
--               0     1      0        0      0      1     1     0
-- 反过来 01100010 = 98
)charset utf8;


-- 插入数据
insert into my_set values('足球,台球,网球');
insert into my_set values(3);


-- 查看集合数据
select hobby + 0, hobby from my_set;


-- 98转成二进制 = 64 + 32 + 2 = 01100010


-- 颠倒元素出现的顺序
insert into my_set values('网球,台球,足球');

四、记录长度

MySQL规定：任何一条记录最长不能超过65535个字节(varchar永远达不到理论值)。

Utf8 下varchar的实际顶配21844字符，GBK下的varchar的实际顶配32766字符。

• 想用完整个65535个字节长度，增加一个tinyint字段即可。
• Mysql记录中，如果有任何一个字段允许为空，那么系统会自动从整个记录中保留一个字节来存储NULL(若想释放NULL所占用的字节，必须保证所有的字段都不允许为空)。
• Mysql中text文本字符串，不占用记录长度，额外存储。但是text文本字符串也是属于记录的一部分，一定需要占据记录中的部分长度，10个字节(保存数据的地址以及长度)。

-- 求出varchar在utf8和GBK下的实际最大值
create table my_utf8(
     name varchar(21844) -- 21844 * 3 + 2 = 65532 + 2 = 65534
)charset utf8;


create table my_gbk(
     name varchar(32766) -- 32766 * 2 + 2 = 65532 + 2 = 65534
)charset gbk;


 
create table my_utf81(
     age tinyint,        -- 1
     name varchar(21844) -- 21844 * 3 + 2 = 65532 + 2 = 65534

)charset utf8;

create table my_gbk1(
     age tinyint,        -- 1
     name varchar(32766) -- 32766 * 2 + 2 = 65532 + 2 = 65534
)charset gbk;



-- 释放NULL
create table my_utf82(
     age tinyint not null,         -- 1
     name varchar(21844) not null  -- 21844 * 3 + 2 = 65532 + 2 = 65534
)charset utf8;

create table my_gbk2(
     age tinyint not null,         -- 1    
     name varchar(32766) not null  -- 32766 * 2 + 2 = 65532 + 2 = 65534
)charset gbk;

 

-- text占用十个字节长度
create table my_text(
     name varchar(21841) not null, -- 21841 * 3 + 2 = 65523 + 2 = 65525
     content text not null         -- 10        
)charset utf8;

 

转载于:https://my.oschina.net/programs/blog/1789521