mysql系列 - 列类型

最新推荐文章于 2022-05-20 21:40:19 发布

小么嘛小二郎

最新推荐文章于 2022-05-20 21:40:19 发布

阅读量273

点赞数 1

分类专栏： mysql 文章标签： mysql mysql数据类型列类型

本文链接：https://blog.csdn.net/mahao25/article/details/100594122

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

数据库的表，每一列都有一种类型的数据声明，数据表的首行是表的描述，表示每列数据代表的意义，换位到文件，就是表头。

mysql有三大数据类型，分为

数值，日期和字符型

1.数值型

数值型包括整形和浮点型数据；
在这里插入图片描述

整形数值：

int 系列声明时的参数： （M） unsigned zerofill

那tinyint来说，带符号表示的范围是[-128,127]，不带符号的表示是[0,255],如何制定类型使用哪种呢？

学习tinyint的参数并验证字节与范围的关系：

创建一个表table02，用来验证tinyint的范围：

create table table02
	(
    	name varchar(20) not null default '',
        age tinyint not null default 0
    )engine myisam charset utf8;
    
 mysql> desc table02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | NO   |     |         |       |
| age   | tinyint(4)  | NO   |     | 0       |       |
+-------+-------------+------+-----+---------+-------+

插入一个负数年龄：

#插入正常的年龄，成功
mysql> insert into table02 values('马豪',25);
Query OK, 1 row affected (0.00 sec)

mysql> select * from table02;
+------+-----+
| name | age |
+------+-----+
| 马豪     |  25 |
+------+-----+
1 row in set (0.00 sec)

#插入负数，仍然可以成功
mysql> insert into table02 values('ls',-15);
Query OK, 1 row affected (0.00 sec)

mysql> select * from table02;
+------+-----+
| name | age |
+------+-----+
| 马豪     |  25 |
| ls   | -15 |
+------+-----+
2 rows in set (0.00 sec)

#当插入一个128的数，则超出范围。
mysql> insert into table02 values('zs',128);
ERROR 1264 (22003): Out of range value for column 'age' at row 1

所以，tinyint默认范围是[-128,127]之间，默认是有符号。

unsigned 无符号

修改表table01，添加新的一列学分,观察 score | tinyint(3) unsigned | NO | | 0 |

#添加新列 alert table [表名] add [列属性];
alert table table01 add score tinyint unsigned not null default 0;

mysql> desc table02;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   |     |         |       |
| age   | tinyint(4)          | NO   |     | 0       |       |
| score | tinyint(3) unsigned | NO   |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+

为学分列添加一个负数和一个大于127的数；

insert into table02 values('ml',18,200);

#错误
insert into table02 values('ww',18,-20);

mysql> insert into table02 values('ww',18,-20);
ERROR 1264 (22003): Out of range value for column 'score' at row 1

zerofill

zerofill用0填充，该参数必须配合(M)使用，当数不够m位数，则用0前补充。

修改数据表table02,新增加一列，作为排名

alter table table02 add pm smallint(6) zerofill not null default 0;


mysql> insert into table02 values ('aa',18,12,62565);
Query OK, 1 row affected (0.00 sec)

mysql> select * from table02;
+------+-----+-------+--------+
| name | age | score | pm     |
+------+-----+-------+--------+
| 马豪     |  25 |     0 | 000000 |
| ls   | -15 |     0 | 000000 |
| ml   |  18 |   200 | 000000 |
| aa   |  18 |    12 | 062565 |
+------+-----+-------+--------+
4 rows in set (0.00 sec)

可以看到pm这一列，第六位都是左补0填充。

同时，如果添加了zerofill，则自动添加属性unsigned ，因为负数不可能0填充

2. 浮点与定点列详解

Float(M,D)

M叫做精度，代表总位数，而D代表标度，代表小数位（小数右边的位数）

新创建一个表salary

create table salary
(
	name varchar(10) not null default '',
    gz float(7,2) not null default 0.00
)engine myisam charset utf8;

则gz列，可以存的数，最大值是：（-9999.99-9999.99）之间；

insert into salary values ('zs',99999.99);

insert into salary values ('zs',-99999.99);

insert into salary values ('ls',-100000);
ERROR 1264 (22003): Out of range value for column 'gz' at row 1
	
mysql> select * from salary;
+------+-----------+
| name | gz        |
+------+-----------+
| zs   |   9999.99 |
| zs   |  99999.99 |
| ls   | -99999.99 |
| ls   | -99999.93 |
+------+-----------+

新增一列bound，为其添加unsigned属性，则范围值是。

alter table salary add boud float(5,2) unsigned not null default 0.00;

mysql> desc salary;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(10)         | NO   |     |         |       |
| gz    | float(7,2)          | NO   |     | 0.00    |       |
| boud  | float(5,2) unsigned | NO   |     | 0.00    |       |
+-------+---------------------+------+-----+---------+-------+

添加大于999.99的数值是否可以；

insert into salary values('mh',80000,1000);

insert into salary values('mh',80000,-1);


mysql> insert into salary values('mh',80000,1000);
ERROR 1264 (22003): Out of range value for column 'boud' at row 1
mysql> insert into salary values('mh',80000,-1);
ERROR 1264 (22003): Out of range value for column 'boud' at row 1

两种方式都是不允许添加的。

decimal是定点型，将整数和小数分开表示。

float的精度有时会损失精度，decimal比较精确。对于精度要高的，使用decimal；

#添加decimal列
alter table salary add de decimal(5,2) not null default 0;


mysql> desc salary;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(10)         | NO   |     |         |       |
| gz    | float(7,2)          | NO   |     | 0.00    |       |
| boud  | float(5,2) unsigned | NO   |     | 0.00    |       |
| de    | decimal(5,2)        | NO   |     | 0.00    |       |
+-------+---------------------+------+-----+---------+-------+


#插入数据
insert into salary values ('ls',10000,03,996.96,996.96); 正常

insert into salary values ('ls',10000,03,996.96,1000.96); 范围越界

3. 字符型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

有错误，关于char大小的问题，char最多是可以声明的字符长度是255 ，则意味着如果在utf8的字符集下，占用的大小则可以达到255*3个字节，而不是0-255字节。
在这里插入图片描述

** char(m) **定长字符串，表示每列的数值都用m个字符存储

varchar(m表示变长，可以按照数据的长度，动态分配大小。

为什么char比varchar效率高

这是由他们在磁盘上存放的不同形式决定的，我们先来看一个图：
在这里插入图片描述
我们可以看到char类型在存放数据的时候，中间是没有间隔的，数据本身是有空格的，但是数据段之间没有间隔，因为我们在创建列的时候已经告诉MySQL``MySQL

但是varchar类型的存放就不同了，在每个数据段开头，都要有一段空间（1~2个字节）存放数据段的长度，在数据段的结尾还有一段空间（1个字节）标记此字段的节数。MySQL在读取一个数据段的时候，首先要读开头，比如读到了3，说明数据段的长度是3，之后就不多不少，只读3个字节。所以MySQL在遍历数据的时候，磁针要比char类型的列多读很多次磁盘来获取字段的真实长度，这就是为什么varchar比char查询效率低的原因了。

char和varchar的解析：

1. 对于存储数据的长度，两个都可以定义限制存储的字符的长度，在列定义时可以char(6),varchar(6),表示最多可以存储6个字符，注意是字符，不是字节，因为在不同编码下，存的字符转字节长度是不同的。按照字符长度存储，可以存储6个汉字，则在utf8下，则占了18个字节.
1. 存储方式：
  
  char：char是定长存储，最多大小是允许255个字符，也就是意味着在utf8编码下，最多可以占用存储255*3个字节了，当插入的数据不足长度时候，右边会用空格补齐，在查出来时，会将空格去掉，如果插入的字符超过定长，则会报错，不会截断串。但因为是定长的，所以查询效率会比较高，但在列容量不能充分利用的情况下会造成一定的空间浪费。
  
  **varchar：**varchar是不定长的，最多允许65536（2^16,两个字节存储长度）个字节，因为varchar是不定长的，所以需要前两个字节标记字段的实际长度，这可以用u盘来说明，买到一个256G的u盘，用工具查看u盘的实际容量时，会发现不足256G，因为系统也要占用一部分。
  
  需要注意的是65535只是字节个数，而且是理论字节个数，在减去头尾的"系统"占用字节后，只剩下65533可用字节。那么我们建表的时候，能不能直接写varchar(65533)呢？当然是不可以的，因为4.0之后，varchar后面的小括号里就不再是字节长度了，而是字符长度。
  字节和字符个数之间的换算关系是根据编码决定的：
  
  编码长度
  utf8 65533/3=21844(汉字占3个字符)
  utf8mb4 65533/4=16383(汉字占4个字符，包含了生僻汉字和文字表情)
  我们只列出了常用的编码格式。
  
  那么这是否意味着，在utf8mb4编码下我们可以用varchar(16383)来定义一个列呢？
  
  答案是要看情况，MySQL规定了一个row所有的字段加起来总长度不能超过65535字节，所以如果一个表只有一个列，那完全可以用varchar(16383)来定义这个列，如果这个表还有其他列，无论其他列多么短，都是会占用字节数的，所以，使用varchar(16383)来定义的时候，MySQL会返回错误提示：ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs,意思是row的容量太大，超出了row的最大容量65535，如果不改变列的长度的话，推荐使用TEXT or BLOBs类型。
  
  所以，如果我们要创建一个只包含两个字段的表（编码是utf8mb4），一列是主键，一列是字符串，字符串的最大长度是多少呢？你可以先自己算一下，再往下看。
  
  列长度
  id int(11)
  article varchar((65535-4)/4=16382)
  为什么65535要减去4呢？因为int(11)占4个字节，那么在utf8编码情况下，还是同样的数据结构，article的最大长度有事多少呢？
  
  列长度
  id int(11)
  article varchar((65535-4)/3=21843)
  相信这次你一定算对了。
  
  做实验验证：
  
  1. char可以最多存储255个字符
  
  字符中数据个数已经大于150个，则大小为150*3=450字节，则验证char(M),其中M表示字符个数，限制就是0-255之间。

编码	长度
utf8	65533/3=21844(汉字占3个字符)
utf8mb4	65533/4=16383(汉字占4个字符，包含了生僻汉字和文字表情)

列	长度
id	int(11)
article	varchar((65535-4)/4=16382)

列	长度
id	int(11)
article	varchar((65535-4)/3=21843)

create table tb3 
(
	ch char(255)
)engine myisam charset utf8;


mysql> insert into tb3 values ('就是意味着在utf8编码下，最多可以存储255/3个字符了，当插入的数据不足长度时候，右边会用空格补齐 ，在查出来时，会将空格去掉，如果插入的字符超过定长，则会报错，不会截断串。但因为是定长的，所以查询效率会比较高，但在列容量不能
Query OK, 1 row affected (0.00 sec)。');

2. varchar最多可以存储的个数：

在utf8的字符编码下分析varchar的存储个数。mysql规定，varchar中最多允许存储的字节数65536(因为，varchar中表示字符个数占2个字节，65536=2^16)。由于2个字节来记录保存的字符数，所以varchar最多可以用65534个字节来存储数据。但是我们不一定能使用65534个字节，对于mysql一行数据而言，一个row所有的字段加起来总长度不能超过65535字节。如果没有其他列，则可以使用65534个字节，在utf8环境下，一个字符占3个字节，则varchar定义的字符个数则是 65534/3 = 21844，我们定义成varchar(21844)

create table tb4
(
	name varchar(21844)
)engine myisam charset utf8;

#第一次成功，第二次失败。
mysql> create table tb4
    -> (
    -> name varchar(21844)
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> create table tb4
    -> (
    -> name varchar(21890)
    -> )engine myisam charset utf8;
ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead
mysql>

BLOB和TEXT类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

BLOB 列被视为二进制字符串(字节字符串)。TEXT列被视为非二进制字符串(字符字符串)。BLOB列没有字符集，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的校对规则对值进行排序和比较。

在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。

当未运行在严格模式时，如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值，值被截取以保证适合。如果截掉的字符不是空格，将会产生一条警告。使用严格SQL模式，会产生错误，并且值将被拒绝而不是截取并给出警告。参见5.3.2节，“SQL服务器模式”。

在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR：

· 当保存或检索BLOB和TEXT列的值时不删除尾部空格。(这与VARBINARY和VARCHAR列相同）。

请注意比较时将用空格对TEXT进行扩充以适合比较的对象，正如CHAR和VARCHAR。

· 对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。参见7.4.3节，“列索引”。

· BLOB和TEXT列不能有默认值。

LONG和LONG VARCHAR对应MEDIUMTEXT数据类型。这是为了保证兼容性。如果TEXT列类型使用BINARY属性，将为列分配列字符集的二元校对规则。

MySQL连接程序/ODBC将BLOB值定义为LONGVARBINARY，将TEXT值定义为LONGVARCHAR。

由于BLOB和TEXT值可能会非常长，使用它们时可能遇到一些约束：

· 当排序时只使用该列的前max_sort_length个字节。max_sort_length的默认值是1024；该值可以在启动mysqld服务器时使用–max_sort_length选项进行更改。参见5.3.3节，“服务器系统变量”。

运行时增加max_sort_length的值可以在排序或组合时使更多的字节有意义。任何客户端可以更改其会话max_sort_length变量的值：

mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM tbl_name
    -> ORDER BY comment;

当你想要使超过max_sort_length的字节有意义，对含长值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一种方式是将列值转换为固定长度的对象。标准方法是使用SUBSTRING函数。例如，下面的语句对comment列的2000个字节进行排序：

mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name
    -> ORDER BY SUBSTRING(comment,1,2000);

· BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。例如，可以使用 mysql和mysqldump来更改客户端的max_allowed_packet值。参见7.5.2节，“调节服务器参数”、8.3节，“mysql：MySQL命令行工具”和8.8节，“mysqldump：数据库备份程序”。

4. 日期型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

在这里插入图片描述

create table tb5
(
	name varchar(20) not null default '',
    birth date not null default '0000-00-00',
    sign time not null default '00:00:00',
    logintime datetime not null default '0000-00-00 00:00:00'
)engine myisam charset utf8;

mysql> desc tb5;
+-----------+-------------+------+-----+---------------------+-------+
| Field     | Type        | Null | Key | Default             | Extra |
+-----------+-------------+------+-----+---------------------+-------+
| name      | varchar(20) | NO   |     |                     |       |
| birth     | date        | NO   |     | 0000-00-00          |       |
| sign      | time        | NO   |     | 00:00:00            |       |
| logintime | datetime    | NO   |     | 0000-00-00 00:00:00 |       |
+-----------+-------------+------+-----+---------------------+-------+
4 rows in set (0.01 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> insert tb5 values ('马豪','1997-12-10','09:54:00','2019-9-7 09:54:35');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb5;
+------+------------+----------+---------------------+
| name | birth      | sign     | logintime           |
+------+------------+----------+---------------------+
| 马豪     | 1997-12-10 | 09:54:00 | 2019-09-07 09:54:35 |
+------+------------+----------+---------------------+
1 row in set (0.00 sec)


###### 练习2 ####
mysql> insert tb5 (name )values ('马豪');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb5;
+------+------------+----------+---------------------+
| name | birth      | sign     | logintime           |
+------+------------+----------+---------------------+
| 马豪     | 1997-12-10 | 09:54:00 | 2019-09-07 09:54:35 |
| 马豪     | 0000-00-00 | 00:00:00 | 0000-00-00 00:00:00 |
+------+------------+----------+---------------------+
2 rows in set (0.00 sec)

建表实战

主键id	name	age	性别	体重	生日	工资	上次登录时间	简介
id	name	age	gender	weight	birth	salary	logintime	info

建表：

列名称	列类型	默认值	主键
id	int unsigned		primary
name	varchar(20)	‘’
age	tinyint unsigned
gender	char(1)/tinyint
weight	tinyint unsigned
birth	date
salary	decimal(8.2)
logintime	datetime
info	varchar(1500)	‘’

优化：

除了name 和 Info列，其他的列都是定长的，对于name类，可以将varchar换成char(20)，来提高效率，但是对于Info，则不可以，那样浪费太多，所以可以新建造一个附表，用来存储info信息。

列名称	列类型	默认值	主键
id	int unsigned		primary
name	char(20)	‘’
age	tinyint unsigned
gender	char(1)/tinyint
weight	tinyint unsigned
birth	date
salary	decimal(8.2)
logintime	int unsigned

附表：

列名称	列类型	默认值	主键
id	int unsigned		primary
name	char(20)	‘’
logintime	datetime

则此时检索的速度，则会提升。

create table tb6
(
	id int unsigned auto_increment primary key,
    name char(20) not null default '',
    gender char(1) not null default '',
    weight tinyint unsigned not null default 0,
    birth date not null default '0000-00-00',
    salary decimal(8,2) not null default 0.00,
    logintime int unsigned not null default 0
    
)engine myisam charset utf8;

总结：

在对列类型的学习中，需要知道几个重点的列类型使用：

1.整形
整形中的使用主要是 tinyint int,其余的整形也使用，这两个类型范围使用面大，所以经常使用。
要记住，如何使用有符号位和无符号位的定义关键字 unsigned
除此，还要知道(m) zerofill 配合使用，左补0的语法。

2.小数
包括 float(D,M) decimal(D,M)类型，对于精度要求高的，使用decimal类型。

3.字符型
关于char(m)和varchar(m)中存储数据的大小的区别，char是可以最多存储255个字符，varchar是可以存储
(2^16-1)-2个字节，用2个字节存储数据长度。 关于varchar和char效率的高低。
text是存储文本的，blod是存值二进制文件的。

4.日期型
知道 date time datetime timestamp存值数据的格式和范围。

小么嘛小二郎

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql系列 - 列类型

数据库的表，每一列都有一种类型的数据声明，数据表的首行是表的描述，表示每列数据代表的意义，换位到文件，就是表头。mysql有三大数据类型，分为数值，日期和字符型1.数值型数值型包括整形和浮点型数据；整形数值：int 系列声明时的参数：（M） unsigned zerofill那tinyint来说，带符号表示的范围是[-128,127]，不带符号的表示是[0,255],如何制...
复制链接

扫一扫