MySQL之数据类型

MySQL之数据类型

1.分类

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型

2.数值类型

2.1分类

2.2int整数类型

int：整数类型

id int,    #整数类型

2.3double(x,y)小数类型

double:小数类型
分数 – 总分100分, 最多出现一位小数
score double(4,1)

score double(4,1),  #值这个小数总共有4位，小数点后有1位

2.4tinyint

年龄字段 – 不会出现负数, 而且人的年龄不会太大
age tinyint unsigned

2.5decimal

DECIMAL依赖于M(精度)和D(标度)的值
比如： DECIMAL123.45 , 则M=5,D=2

3.字符串类型

3.1分类

3.2varchar(x)字符串类型

varchar：字符串
注：name varchar(20)是"姓名最大20个字符"
zhangsan是8个字符 ； 张三是2个字符

name varchar(32),  #字符串   姓名最大32个字符

4.日期类型

4.2date日期类型

date:日期，只包含年月日，yyyy-MM-dd

birthday date,

4.3datetime日期类型(年月日)

datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
如：创建时间 createtime
createtime datetime

4.4timestamp时间戳类型(年月日时分秒)

timestamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss

insert_time timestamp

5.varchar(x)5.0.3版本前后变化

6.int(10)和char(10)的区别

int(10)中的10表示的是显示数据的长度，不是存储数据的大小；
int(10) 10指示最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关

而char(10)表示的是存储数据的长度。

最大是255

第一次实验

第二次实验

第三次实验

7.char(x)与varchar(x)的区别

char 与 varchar 都可以描述字符串，但char 和 varchar 类型在存储和检索方面有所不同；
char是定长字符串，char 列长度固定为创建表时声明的长度，长度值范围是 1 到 255，即指定长度多长，就占用多少个字符，和字段值的长度无关 。
当 char 值被存储时，它们被用空格填充到特定长度，检索 char 值时需删除尾随空格.

而varchar是变长字符串，指定的长度为最大占用长度 ,

相对来说，char的性能会更高些，varchar性能较差，原因是varchar在使用的时候，它会根据内容来计算空间;如果确定某个字段的值的长度,可以使用 char,否则应该尽量使用 varchar.例如存储用户 MD5 加密后的密码,则应该使用 char。

比如：char(10)即使我们只存储一个字符，它也会占用10个字符的空间，未占用的其他空间会使用空格及进行补位;

char 是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用 10 个字符,而 varchar 是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间.

如：
1). 用户名 username ------> 长度不定, 最长不会超过50
username varchar(50)
2). 性别 gender ---------> 存储值, 不是男,就是女
gender char(1)
3). 手机号 phone --------> 固定长度为11
phone char(11)

8.char(10)在utf8编码下占用多少字节

对于char(10)，最大允许存储的字节是30个字节(utf8) 编码 :
即如果存储中文的话，在utf8格式下10个中文占用30个字节，char(10)可以存储10个中文;
那么对于字母类型，在utf8格式下10个字母字符占用10个字节，虽然一个char(10)最大允许存储的字节是30个字节 ，但由于最大限制仍旧是以字符个数来计算的，所以char(10)只可以存储10个字母

 CREATE TABLE `test_char_1` (
   id int,
   s char(255)
   )ENGINE=InnoDB  DEFAULT CHARSET=utf8;

在上面是实验中即使是utf-8，也可以指定到255，按道理这样的话不就是700多字节，已经大于255了么？
对此 《innodb存储引擎》一书有下面这段说明：
从MySQL4.1 版本开始 ，char(n) 中的n 指字符长度，不再表示之前版本的字节长度。
在不同字符集下，char类型列的内部存储可能不是定长数据。
也就是说多于对字节字符集编码，char类型不再代表固定长度的字符串。对于多字节字符编码的char数据类型的存储，innodb存储引擎在内部将其视为变长字符类型

9.mysql 中的int(3)、int(10)、int(11)

普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295

其实，INT[(M)] [UNSIGNED] [ZEROFILL] M默认为11
这里的M代表的并不是存储在数据库中的具体的长度，以前总是会误以为int(3)只能存储3个长度的数字，int(11)就会存储11个长度的数字，这是大错特错的。
其实当我们在选择使用int的类型的时候，不论是int(3)还是int(11)，它在数据库里面存储的都是4个字节的长度；
在使用int(3)的时候如果你输入的是10，会默认给你存储位010,也就是说这个3代表的是默认的一个长度，当你不足3位时，会帮你不全，当你超过3位时，就没有任何的影响。
int(M) M指示最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关。

Mysql类型关键字后面的括号内指定整数值的显示宽度(例如，INT(3) INT(10) INT(11) )
所以int(10)与int(11)后的括号中的字符表示显示宽度，整数列的显示宽度与mysql需要用多少个字符来显示该列数值，与该整数需要的存储空间的大小都没有关系，int类型的字段能存储的数据上限还是2147483647(有符号型)和4294967295(无符号型)。

10.int(1) 和 int(10) 区别

我们知道在mysql中 int占4个字节，那么对于无符号的int，最大值是2^32-1 = 4294967295，将近40亿，难道用了int(1)，就不能达到这个最大值吗？
id字段为无符号的int(1)，我来插入一个最大值看看.
可以看到成功了，说明int后面的数字，不影响int本身支持的大小，int(1)、int(2)…int(10)没什么区别。

CREATE TABLE `user` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO `user` (`id`) VALUES (4294967295);

一般int后面的数字，配合零填充zerofill一起使用才有效。先看个例子：
注意int(4)后面加了个zerofill，我们先来插入5条数据
分别插入1、10、100、1000 、56232 五条数据，然后我们来查询下

CREATE TABLE `user` (
  `id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO `user` (`id`) VALUES (1),(10),(100),(1000);
 INSERT INTO `user` (`id`) VALUES (56232);

总结：int后面的数字不能表示字段的长度，int(num)一般加上zerofill，才有效果。
zerofill的作用一般可以用在一些编号相关的数字中，比如学生的编号 001 002 … 999这种

使用fillzero注意事项：使用fillzero后mysql自动增加UNSIGNED属性 ，即插入的值不能是负数

11.MySQL 里记录货币用什么字段类型好

NUMERIC 和 DECIMAL 类型被 MySQL 实现为同样的类型，这在 SQL92 标准允许。 他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定;
例如：
salary DECIMAL(9,2) 77
在这个例子中，9(precision)代表将被用于存储值的总的小数位数，而 2(scale)代表将被用于存储小数点后的位数。 因此，在这种情况下，能被存储在 salary 列中的值的范围是从-9999999.99 到 9999999.99