MySQL 数据值和列类型

大家好，欢迎来到IT知识分享网。

数据值和列类型

1、数值型

整型(整数)

非常小的整型 1字节 -128–127 0–255(无符号) TINYINT 极小的(tiny)

较小的整型 2字节 -32768–32767 0–65535 SMALLINT 小的(small)

中等大小的整型 3字节 — 0– MEDIUMINT 中等的(medium)

标准的整数型 4字节 —- INT 存储UNIX时间戳(10位或11位)

大整数型 8字节
— BIGINT 大的(big) 存储JAVA时间戳(13位)

整型数据在创建时, 会自动指定数据长度, 如tinyint(4)、mediumint(8)、int(11),

本身并不会校检整型数据的长度, 这个值只有在设置字段类型为zerofill时才会生效。

整数值的显示宽度(例如, INT(4))。该显示宽度规定用于插入的值小于指定的列宽度时从左侧填满宽度。

显示宽度并不限制可以在列内保存的值的范围, 也不限制超过列的指定宽度的值的显示; 而是与zerofill字段属性相结合,否则看不出效果和差别

浮点型(小数)

float(M,D) 4字节 单精度浮点数

double(M,D) 8字节 双精度浮点数

decimal(M,D) M+2字节 定点数

注意:M值不含小数点所占的位数(包括小数部分, 但不包括小数点)

float与double和decimal的区别：

decimal类型是以字符串形式被存储起来的, 它的小数位是固定的, 适合财务运算；CPU不能直接运算, 影响运算效率

float与double类型数据会进行四舍五入；

float(7,2) double(7,2) decimal(7,2)

如果插入的浮点数宽度(不包括小数点)大于M值, 则插入失败(如:float(7,2)和decimal(7,2)列内插入.123)

如果插入的浮点数的小数位大于D值,则四舍五入取近似值(如：float(7,4)和decimal(7,4)列内插入99.00009, 近似结果是999.0001)

注意: 以上结果是在MySQL5.5.27版本的结果, 各版本测试的结果可能不一样;

通常float和double类型是类似PHP中float类型的”不精确小数”, 因此 float和double并不适合设定长度;

float和double两者设定的总长度, 其实是包括了小数点, 只是float精度更低, 能保留的有效数字只有6位;

decimal类型被称为”精确小数”—— 它能够精确表达一个小数的真实大小——它绕过了二进制表达小数的原理性缺陷! 那么它通常都应该明确指定长度!

而decimail却通常要求设定长度是更佳做法。如果decimal没有设定长度, 则默认小数位数为0。

小数型数据, 除了使用具体值进行表述还可以使用科学计数法进行表述

create table t_decimal(

d1 decimal(11,2)

)engine=myisam charset=utf8

insert into t_decimal values(1.23e3)

2、字符型

utf8编码下,一个汉字算三个字节, 一个数字或字母算一个字节。

其他编码下,一个汉字算两个字节, 一个数字或字母算一个字节；

mysql支持双引号和单引号 “abcde” ‘abcde’

mysql能识别转义字符”\”

varchar： 可变长度字符类型, 必须设长度, 最大65535个字符。但是：设定的长度只是最大可存储的值, 实际可以不存那么多。

char： 固定长度字符类型, 不设定长度就默认为1, 最大255个字符。实际存储的长度如果不够, 则会自动补”空格”填满设定的长度。

char(M) 255个字符 固定长度 用于性别、密码 访问速度快

varchar(M) 65535个字符 可变长度 用于用户名、文章标题 占有容量小

char(M)： M代表字符长度, M理论值为255

varchar(M)：M代表字符长度, M理论值为65535, 但是其与字符集有关。但是在不同的编码格式下, 其存储的最大字符以及占用的字节长度也是不同的！

LATIN1：一个字符占用1个字节长度

GBK： 一个汉字占用2个字节长度

UTF8： 一个汉字占用3个字节长度

占用的字节

char(4) varchar(4) M

” 4 ” 1个字节

‘ab’ 4 ‘ab’ 3个字节

‘abcd’ 4 ‘abcd’ 5个字节

‘abcdefg’ 4 ‘abcdefg’ 5个字节 MySQL5.5版本插入失败

‘ab ‘ 4 ‘ab ‘ 4个字节

‘高洛峰’ 6 ‘高洛峰’ 7个字节 GBK

‘爱新觉罗’ 8 ‘爱新觉罗’ 13个字节 UTF8

由此可知：M值不是限制字节数, 而是限制字符数;

varchar(M)类型当存储的字符小于255时, 其数据长度占用1个字节, 当存储的字符大于255时, 其数据长度占用2个字节。

varchar(M)类型所能保存的最大M字符长度

在gbk编码格式下, varchar类型最大只能存储32766个字符长度。

在utf8编码格式下, varchar类型最大只能存储21844个字符长度。

注意: char(M)类型的数据列里, 每个值都占用M个字符;

varchar字符型所占字节等于自身长度加1(M+1或2)

如果插入的字符串的长度超出了char(M)和varchar(M)字符型的M值, 则插入失败;

(ERROR 1406 (22001): Data too long for column ‘name’ at row 1)

如果插入的值小于M, MySQL就会在它的右边用空格字符补足。(在检索操作中那些填补出来的空格字符将被去掉);

如果插入的值大于M, 则插入失败;

注意：varchar字符型后面必须加M值, 否则创建表失败; char字符型不设定长度就默认为1。

个人建议：

在实际项目开发中, 对于固定长度的字符, 建议使用char类型进行存储;

在实际项目开发中, 对于长度不固定且小于255个字符的情况, 建议采用varchar类型;

在实际项目开发中, 对于大于255个字符的情况, 通常采用text类型进行存储;

text 文本数据 (文章) 不区分大小写 65,535个字节

MEDIUMTEXT 16,777,215个字节

LONGTEXT 4,294,967,295个字节

blob 二进制数据 (相片) 区分大小写 65,535个字节

MEDIUMBLOB 16,777,215个字节

LONGBLOB 4,294,967,295个字节

ENUM 枚举(表示”多选一”选项的字符) 1或2字节

ENUM(“one”, “two”, “three”, “four”) — 65535, 一次只能有一个值

“one” 对应的下标为1, 从1开始计数;

“four” 对应的下标为4,

因此, 数据插入可以使用1~4分别代替, 在表单中value值可以用1-4代替

SET 集合(表示”多选多”选项的字符) 1, 2, 3, 4, 8字节

SET(“one”, “two”, “three”, “four”) — 64, 一次可以用多个集合中的值, 中间使用”,”分开

数据可以使用1~4分别代替, 在表单中value值可以用1-4代替, 但是下标不能代替多选, 只能代替单选;

注意：枚举和集合是字符串型, 建表时enum(1, 2, 3, 4)和set(1, 2, 3, 4) 是错误的写法;

插入的数据也要注意是字符串型, 除非是下标对应的选项

以文章的推荐与未推荐为例： isrecommend enum(“0”, “1”)

$isrecommend = isset($_GET[‘isrecommend’])?($_GET[‘isrecommend’]):””;

if($isrecommend){

$isrecommend = “0”; //注意枚举是字符串型数据

}else{

$isrecommend = “1”; //注意枚举是字符串型数据

}

$sql = “update blog_article set isrecommend = ‘$isrecommend’ where artid = ‘$artid'”;

注意：变量isrecommend是字符串型, 必须要加引号,

解释：如果插入整型1，SQL执行语句没有错误; 因为1代表下标, 选中”0″选项

如果插入整型0，SQL执行语句出现错误；因为enum枚举的数据必须是字符串

3、日期型

DATE YYYY-MM-DD ‘1000-01-01’到’9999-12-31’

TIME HH:MM:SS ‘-838:59:59’到’838:59:59’

DATETIME YYYY-MM-DD HH:MM:SS ‘1000-01-01 00:00:00’到’9999-12-31 23:59:59’

TIMESTAMP YYYYMMDDHHMMSS ‘1970-01-01 00:00:00’到’2038-01-19 03:14:07’

时间戳, 就是表示”当前时刻”, 用于INSERT或UPDATE操作时自动获得当前时间(也就是无需给数据)

注意不是UNIX时间戳

YEAR YYYY ‘1901到2155’

创建表时最好不要使用这些中(上面)的时间格式(PHP中时间戳 1970-1-1 0:0:0)是一整数

用整数(int)保存时间 time(); 方便运算

datetime类型与timestamp类型区别

两者的存储范围不同:

datetime(‘1000-01-01 00:00:00’到’9999-12-31 23:59:59’) timestamp(‘1970-01-01 00:00:00’到’2038-01-19 03:14:07’)

默认值不同:

datetime默认值为NULL timestamp默认值为服务器当前时间

time存储值问题

time类型除了可以存储当前时间还可以存储过去或未来的某个时间

其值的范围是：’-838:59:59’到’838:59:59′

year类型数据长度问题

year(2)位的年也是被允许的, 但是表示的范围70-69 表示：1970-2069范围

系统自动对分隔符进行修改, 自动添加前导0

如对 DATETIME 2016/05/10 9:22:03 -> 2016-05-10 09:22:03

YEAR 16 -> 2016

4 NULL 值

NULL和任何值都不能比较

NULL只能用IS NULL或者IS NOT NULL来判断, 不能用=或者!=来判断

下列语句不返回任何行, 因为对任何表达式, expr = NULL是假的

SELECT * FROM my_table WHERE phone = NULL;

要想寻找NULL值, 你必须使用IS NULL

SELECT * FROM my_table WHERE phone IS NULL;

SELECT * FROM my_table WHERE phone = “”;

归纳：

float(M,D) double(M,D) decimal(M,D) char(M) varchar(M)

如果插入的值超过M值

MySQL5.1版本 小数型取近似值, 字符串进行截取

MySQL5.5版本 插入失败

MySQL5.1低版本会自动则对值进行裁剪以使其适合。如果被裁掉的字符不是空格, 则会产生一条警告,但数据被裁剪后,插入成功;

mysql> create table tb_user(

-> id int,

-> name varchar(5)

-> )engine=myisam;

Query OK, 0 rows affected (0.14 sec)

mysql> insert into tb_user values(null,’namamma’)

Query OK, 1 row affected, 1 warning (0.00 sec))

在varchar(M)类型的数据列里, 每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为M+1字节)。

select any_value(name), group_id from game group by group_id