mysql数据类型-读书笔记

1.正确选择数据类型至关重要

对数据类型的选择将影响与数据库交互的应用程序的性能。通常来说，如果一个页可以存放尽可能多的行，那么数据库的性能就越好；另一方面，如果数据库中创建表时选择错误的数据类型，那么后期维护成本可能非常大。因此要认识到数据类型是一个既基础又非常重要的角色。

2.类型属性unsigned、zerofill

2.1.unsigned

unsigned属性就是将数字类型无符号化。例如，int的类型范围是-2 147 483 648 ~ 2 147 483 647，int unsigned的类型范围就是0 ~ 4 294 967 295。

看上去仅仅是数据的范围有所区别，但是实际使用中，unsigned可能会带来一些负面影响。

示例：

create table t (
    a int unsigned,
    b int unsigned
)engine = innodb;


insert into t select 1, 2;

select * from t;

这段代码很简单，就是创建一个表，两个字段都是unsigned的int类型，往里面插入（1, 2）这行数据。

接着执行如下代码：

select a - b from t;

 这段代码的执行结果是不确定的，在我本机Windows系统中会报如下错误。

此处用计组的知识来解释一下：在计算机中减法实际上也是通过加法实现的，所以1 - 2，实际上是1  +（-2）。

1在计算机中存储的反码就是其原码：0000 0000 0000 0000 0000 0000 0000 0001

-2在计算机中存储的反码：1111 1111 1111 1111 1111 1111 1111 1110

所以1 - 2 结果为：1111 1111 1111 1111 1111 1111 1111 1111 -> 0xffffffff

0xffffffff可以代表两种值：对于无符号的整数值，最大值为4 294 967 295；对于无符号的整数值来说，第一位代表符号位，如果是1，表示负数，这时需要取反加1得到负数值，即-1。

在mysql中，对于unsigned数的操作，其返回值都是unsigned的。所以此处的结果会出现不确定性的情况。可以通过修改sql_mode这个参数来得到-1

set sql_mode = 'no_unsigned_subtraction';

select a - b from t;

结论：尽量不要使用unsigned，如果int存放不了了，也不要使用unsigned来提升范围，直接使用bigint类型。

2.2.zerofill

zerofill是一个显示属性，在建表时，会经常看到int(10)这样的设置，如果没有zerofill这个属性，那么10就毫无意义了。

看个例子：

alter table t change column a a int(4) unsigned zerofill;

# 将默认的int(10)修改为int(4)

select a, b from t;

这里显示0001，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里宽度是4），则自动填充0。要注意的是，这只是显示的结果，实际的存储还是1。

3.日期和时间类型

各种日期数据类型及其所占空间

类型	所占空间
datetime	8字节
date	3字节
timestamp	4字节
year	1字节
time	3字节

3.1.datetime和date

datetime表示时间日期，其可表示的范围：1000-01-01 00:00:00 到 9999-12-31 23:59:59

date表示日期，可显示的范围：1000-01-01 到 9999-12-31

mysql对日期和时间的输入格式要求相对宽松的，可以是以下形式：

2021-01-01 00:01:10

2021/01/01 00+01+10

2021010100110

21/01/01 00@01@10

虽然格式众多，最好还是养成习惯，使用第一种辨识度高的形式输入。

示例：

create table t1(
    a datetime
);

insert into t1 select '2021-01-01 00:01:10.123456';


select * from t1;

 mysql会将微秒数值截断，如果要显示微秒值，可以使用microsecond

SELECT MICROSECOND('2021-01-01 00:01:10.123456');

 3.2.timestamp

timestamp和datetime显示的结果是一样的，都是固定"YYYY-MM-DD HH:MM:SS"的形式。不同的是timestamp占4个字节，显示范围1970-01-01 00:00:00 UTC 到 2038-01-19 03:14:07 UTC。其实际存储内容为1970-01-01 00:00:00 到 当前时间的毫秒数。

注意点：

• 在建表时，timestamp的日期类型可以设置一个默认值，而datetime不行
• 在更新表时，可以设置timetamp的类型自动更新时间为当前时间

示例1，建表

create table t2(
    a int,
    b timestamp default current_timestamp
)engine = innodb;

insert into t2 (a) values(1);


select * from t2;

 示例2，更新

create table t3 (
    a int,
    b timestamp on update current_timestamp
);

insert into t3 select 1, current_timestamp;

select * from t3;

# 等待一段时间
update t3 set a = 2;

select * from t3;

等待一段时间后

 

可以看到执行update操作后，b列的时间更新为当前时间。

# 如果执行更新操作但是没有改变行中任何数据，那么b列不会执行更新操作

update t3 set a = 2;

select * from t3;

 

在建表时，可以将timestamp列设一个默认值，也可以设为在更新时的时间。

create table t (
    a int,
    b timestamp default on update current_timestamp
)engine = innodb;

3.3.year和time

year类型占用1个字节，并且在定义时可以指定显示的宽度为year(4)或year(2)。

create table t4(
    a year(2)
);

insert into t4 select '1990';

select * from t4;

 time类型占用3字节，显示的范围：-838:59:59 ~ 838:59:59。time不仅可以用来保存一天中的时间，也可以用来保存时间间隔。

create table t5(
    a time
);

insert into t5 select '14:40:20.123456';

select * from t5;

time类型同样可以显示微秒时间，但是在插入时，数据同样会进行截取操作。

3.3.1.与日期与时间相关的函数

now、current_timestamp、sysdate

这些函数都能返回当前的系统时间，但是之间有一定区别。

select now(), current_timestamp(), sysdate();

select now(), current_timestamp(), sysdate(),
       sleep(2),
       now(), current_timestamp(), sysdate();

可以看见sysdate()返回的时间和now()及curretn_timestamp()是不同的。其原因是这3个函数有略微区别：

• current_timestamp是now的同义词，也就是说两者是相同的
• sysdate函数返回执行到当前函数时的时间，而now返回的是执行SQL语句的时间。

时间加减函数

select now() as now, 
       date_add(now(), interval 1 day) as tomorrow,
       date_sub(now(), interval 1 day) as yesterday;

 这个例子使用了day，还可以使用microsecond、second、minute、hour、week、month等类型。

select date_daa(now(), interval 1 hour) as next_time;

 date_format函数

这个函数没有什么特别，就是按照需求格式化输出日期

select date_format(now(), '%Y%m%d') as datetime;

 注意：

select * from table where date_format(date, '%Y%m%d') = 'xxxx-xx-xx';

# 这条查询语句不会走索引，所以执行效率可能会很慢。

4.数字类型

4.1.整型

mysql数据库支持SQL标准支持的整数类型：int、smallint。此外mysql数据库还支持诸如tinyint、meniumint、bigint等类型。

整数类型占用空间和取值范围

类型	占用空间（字节）	最小值(signed/unsigned)	最大值(signed/unsigned)
tinyint	1	-128 0	127 255
smallint	2	-32768 0	32767 65535
mediumint	3	-8388608 0	8388607 16777215
int	4	-2147483648 0	2147483647 4294967295
bigint	8	-9223372036854775808 0	9223372036854775807 18446744073709551615

4.2.浮点型

mysql数据库支持两种浮点类型，单精度float类型和双精度double precision类型。这两种类型都是非精确类型，经过一系列操作不能保证运算的正确性，即 M*G/G不一定等于M。

4.3.高精度类型

decimal和numberic类型在mysql中被视为相同的类型，用于保存必须为确切精度的值。

decimal（5， 2）表示：5是精度，2是标度。精度表示保存值的主要位数，标度表示小数点后面可以保存的位数。

4.4.位类型

bit数据类型课用来保存位字段的值，bit(M)类型表示允许存储M位数值，M范围1到64.

create table t6 (
    a bit(4)
);

insert into t6 select b'1000';

select hex(a) from t6;

 5.字符类型

在mysql中，默认的字符集是latin1，对于英文来说问题不大，对于中文来说问题就很严重了。

# 查看mysql数据库支持的字符集
show charset


# 配置文件中可以设置默认字符集

# mysql5.5以前
default-character-set = utf8

# mysql5.5以后
character_set_server = utf8

5.1.char和varchar

char(N)用来保存固定长度的字符串，varchar(N)用来保存变长字符串类型。对于char类型，N的范围为0~255，对于varchar类型，N的范围为0~65535。其中N代表字符长度，而非字节长度。

注：本文的内容总结于书籍：《MySQL技术内幕：SQL编程》，感兴趣可以去研读一番，必有收获。