数据类型与运算符（七）

 

一、整数类型

MySQL提供多种数值数据类型，不同的数据类型提供的取值范围不同，
   可以存储的值的范围越大，其所需要的存储空间也就越大，
因此要根据实际需求选择适合的数据类型。

 

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

 

 

 

 

 MySQL在建表的时候int类型后的长度代表什么？是最大有效显示宽度

为什么我设置成int(1)，也一样能存10、100、1000呢。

其实建表时，对于整数而言，这个m并没有实际意义，见相关文章说明

 

我们看看如果这些整数类型不指定m的大小，它的默认值是多少？

 

 

 

 

为什么yinyint 的m是4？因为 - 这个符号占用一个数字，其他整数同理

 

二、浮点类型和定点类型

 

 

 

   设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，
但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，
存储的是12.1234，  如果插入12.12，存储的是12.1200.

 

 

 

- float和double的区别

+ 和编程开发中一样, 默认保留的小数位数不同
+ 和编程开发中一样, 有效精度也不同
+ 和编程开发中一样, 浮点类型是不准确的

+ 所以在企业开发中千万不要使用浮点数来保存用户的准确(珍贵)信息(RMB)

对比如下

 

 

 

 

 

定点类型

decimal(M, D)
m总位数, d小数位数
定点类型的本质: 是将数据分为两个部分来存储, 每个部分都是整数
所以点点数不要滥用, 因为非常消耗资源

 

 

 

 

 

当然也有丢失精度的情况

 三、日期类型

日期 / 时间类型

 

 

YEAR:   

1）无论输入的是 字符串‘2015’  还是数字2015  ，数据库中保存的都是2015

2）以两位字符串格式表示YEAR（其范围是‘00’-‘99’）时：  ‘00’ - ‘69’   被转化为 2000-2069  ；  ‘70’ - ‘99’   被转化为 1970-1999  ，输入‘0’ 或是‘00’ 结果都是2000

   输入的字符超过范围则转化为2000

 

3）以两位数字表示YEAR 范围是1-99,1-69 转化为2001 -2069 ； 70-99 转化 为 1970 -1999 ，注意：0被转化为0000

案例一：保存数字和字符串

 

 

案例二：YEAR也是有范围的

 

 

案例三、输入字符类型

 

 

案例四、输入数字类型

 

 

TIME:   

   用于只需记录时间信息的值，格式 HH:MM:SS  HH 表示小时，MM表示分钟，SS表示秒。 它的取值范围-838:59:59 - 838:59:59 

小时可以是一天内的时间，这么大值也可以是表示某一个事件过去发生的时间

两种语法如下

1）'D HH:MM:SS' 格式的字符串 或是HHMMSS格式的数字，也可以使用'HH:MM:SS' 、‘HH:MM’ 、 ‘D HH:MM’ 、‘D HH’ 或是 ' SS'等非严格语法， D 表示日，取值是0 - 34 ，

在插入数据库时被转化为小时，语法是D*24+HH.

2) 'HHMMSS'   ‘125034’ 存入数据库变成  ‘12:50:34’  ；  ‘136521’ 将变成 ‘00:00:00 ’  因为65 大于60 这个时间表示没意义，转化就默认存为‘00:00:00’

 

案例一  字符形式的

 

 

案例二 第二种

 

 

超过数值无法插入

 

 

 

插入当前系统时间

 

 

 

 

DATE:   

当只需要存储日期，不需要时间，格式YYYY-MM--DD

字符串格式

1）‘YYYY-MM-DD’  或是‘YYYYMMDD’ ,取值范围是‘1000-01-01’ - ‘9999-12-31’

2）‘YY-MM-DD’  , 'YYMMDD'  这里‘YY’ 表示‘00-69’ 转化为‘2000-2069’ ； ‘70-99’ 转化为‘1970-1999’ 

如‘18-12-22’ 转化为‘2018-12-22’

3）YYMMDD 这样的数字格式，同理 YY 中00-69 转化2000-2069.。。。

 

案例一  字符串‘YYYY-MM-DD’

 

 

案例二  字符串‘YY-MM-DD’ 

 

 

案例三、  数字  YYMMDD

 

 

插入当前系统时间

 

 

 

DATETIME

包含日期和时间

格式如下

字符串格式

1）‘YYYY-MM-DD HH:MM:SS’ 或者是‘YYYYMMDDHHMMSS’    取值范围是‘1000-01-01 00:00:00’ --  '9999-12-31 23:59:59'

2）‘YY-MM-DD HH:MM:SS’  或是‘YYMMDDHHMMSS’ 含义和DATE一样。

3）数值格式 YYYYMMDDHHMMSS 或是YYMMDDHHMMSS参考DATE.

 

案例一 字符

 

 

 

案例二   数值格式

 

 

系统时间

 

 

注意：MYSQL允许使用不严格的语法插入日期，如：

‘95.12.18 11+28+30’  ；  ‘95/12/18 11*28*30’   ‘95@12@18 11^28^30’  等表示方法是等价的，均可正确保存到数据库中

 

 

TIMESTAMP

显示格式和DATETIME相同，格式

'YYYY-MM-DD HH:MM:SS'    取值范围是‘1970-01-01  00;00:01’UTC --  '2038-01-19 03:14:07' UTC  ， UTC指的是世界标准时间。

 

 

案例二  ，当前时区是东8区

 

 设置当前时区为东6区

 

 东6区比东8慢两小时

 

 

 四、字符类型（表中是字节不是字符）

 

 注意：

UTF-8编码：

一个英文字符等于一个字节，一个中文（含繁体）等于三个字节。

Unicode编码：

一个英文等于两个字节，一个中文（含繁体）等于两个字节。

char和varchar：

CHAR(size) 0-255字节 定长字符串
VARCHAR(size) 0-65535 字节 变长字符串

如果没有指定长度, 那么默认char就是255,varchar65535

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。

2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（结束字符）（n<=255）或2个字节(n>255)，

所以varchar(4),存入3个字符将占用4个字节。

3.char类型的字符串检索速度要比varchar类型的快。
varchar和text：

1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字

节。

2.text类型不能有默认值。

3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

 

 

案例一  理论上varchar最多能够存储65535个字节, 但是实际的大小和表的编码方式有关

如果表的编码方式是GBK, 那么最多能够存储65535/2

 

 

 如果表的编码方式是UTF-8, 那么最多能够存储65535/3

 

 

 

案例二 注意点: 在MySQL中每一行数据最多只能存储65535个字节

 

 

 

 可创建

 

TEXT 类型

 保存文章，留言、评论等内容

 

分为4种

tinytext ：最大长度255（2^8 -1）个字符

text：最大长度65535（2^16-1）个字符

mediumtext ：最大长度16777215（2^24-1）个字符

longtext：最大长度4294967295 共4个GB（2^32-1）个字符

 

ENUM类型

是一个字符串对象，七值为创建表时在字段归定中枚举的一列值，

字段名 ENUM ('值1'，‘值2’，...)

enum类型的字段只能从指定的枚举列表中取值，而且一次只能取一个值。

enum在内部用整数表示，每一个枚举值只能有一个索引值。枚举允许有65535个元素。

如enum类型的字段（'first' ,'second', 'third'） ,则该字段可以取的值以及每一个值的索引如下。

enum类型的取值范围

 

 

enum值依照索引顺序排序，空字符排在非空字符前，null排在所有枚举值之前。

a+0是可以查看索引值

 

 

插入不在枚举列里面的值报错

 

 

 

 

 

总结

和编程开发中一样, 如果某个字段只能是固定的几个取值,并且只能是其中一个取值, 那么就可以使用枚举
和编程开发中一样, MySQL中的枚举类型本质也是整型, 但是是从1开始的
和编程开发中一样, 既然本质是整型, 所以就可以设置整型数据

 

SET 类型

集合类型

和编程开发中一样, 如果某个字段只能是固定的几个取值,并且可以是其中某几个取值, 那么就可以使用集合类型

 

 

 

注意点: MySQL中的集合本质也是整型

 

布尔类型

 

注意: MySQL不支持真正的布尔类型, 由于MySQL是用C/C++编写的, 所以非零即真
和C语言一样, ture===1 , false===0

 

 

 

总结：

1. 如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
2. 建议使用TINYINT代替ENUM、BITENUM、SET。
3. 避免使用整数的显示宽度，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。关于INT显示宽度。
4. 用INT UNSIGNED来存储IPv4地址，用VARBINARY来存储IPv6地址，当然存储之前需要用PHP函数转换。
5. DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
6. 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
7. 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。
8. MySQL能存储的最小时间粒度为秒。
9. 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
10. 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
11. 当数据格式为TIMESTAMP 和 DATETIME 时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。
12. TIMESTAMP是UTC时间戳，与时区相关。
13. DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
14. 除非有特殊需求，否则建议使用TIMESTAMP，它比DATETIME更节约空间。
15. 有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它。

       16.字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。

  17.CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。

     18. 那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。

  19.BINARY和VARBINARY存储的是二进制字符串，与字符集无关。

  20.BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。一般情况下，你可以认为BLOB是一个更大的VARBINARY；TEXT是一个更大的VARCHAR。

  21.BLOB和TEXT都不能有默认值。