数据类型(列类型)总览

Mysql数据类型
Mysql数据类型

可见,mysql中的数据类型,总体分3大类:

  • 数字型
  • 时间型
  • 字符型

其中,在sql语句中,数字型数据不用单引号引起来,而时间型和字符型数据需要用单引号引起来。

整型

整型数据类型包括:

  • tinyint :微整型
  • smallint :小整型
  • mediumint :中整型
  • int :整型
  • bigint :大整型

这些不同大小范围的整型信息如下表所示:

类型所占空间 (字节)最小值带符号最大值带符号最小值无符号最大值无符号
tinyint1-1281270255
smallint2-3276832767065535
mediumint3-83886088388607016777215
int4-2147483648214748364704294967295
bigint8-92233720368547758089223372036854775807018446744073709551615

默认整数类型是带符号的,即可以有正负值,比如:

create table zhengxing1(num1 int, num2 tinyint);

此时,num1和num2中都可以存储负数(但都不能超出范围)

不带符号的整数类型设置形式如下:

create table zhengxing2(num1 int unsigned, num2 tinyint unsigned);

代码演示:

-- 创建一个表,具有int和tinyint类型(默认带符号),并使用数据测试:
create  table  zhengxing1(num1  int, num2  tinyint);
insert into zhengxing1(num1, num2) values(10, 10); -- 正确的数据
select * from zhengxing;
insert into zhengxing1(num1, num2) values(1000,1000); -- 错误的数据
-- 错误的原因是:后一个1000超出了数据类型tinyint的范围

-- 另一个演示:unsigned :
create  table  zhengxing2(num1  int unsigned, num2  tinyint unsigned);
insert into zhengxing2(num1, num2) values(10, 10);-- 正确的数据
insert into zhengxing2(num1, num2) values(-10, -10);-- 错误的数据
-- 错误的原因:两个字段都设定了unsigned,只能是非负数

小数型

小数类型分为浮点小数和定点小数。

浮点小数

浮点小数是“不精确的小数”,包括float和double。

  • float:占用4字节存储空间,可称为“单精度浮点数”,约7位有效数字。
  • double:占用8字节存储空间,可称为“双精度浮点数”,约17位有效数字。

定点小数

浮点小数是“精确的小数”——它通过内部技巧,突破了“有些小数无法用二进制精确表示”的局限。

其设定方式通常是这样的: decimal(M, D);

其中M表示该小数的总的有效位数(最大65),D表示该小数的小数点后的位数。

演示:定义三个字段分别为float、double和decimal类型,并都插入数字“123456789.123456789123456789”,显示结果。

-- 演示: 定义三个字段分别为float、double和decimal类型,并都插入数字“123456789.123456789123456789”,显示结果。
create table xiaoshu1(x1 float, x2 double, x3 decimal(23, 14) );
insert into xiaoshu1(x1, x2, x3)values(123456789.123456789123456789, 123456789.123456789123456789,123456789.123456789123456789);
select * from xiaoshu1;

数据类型选择示例:

  • 要设置一个字段为“年龄”:应该用tinyint unsigned
  • 要设置一个人的“工资”: double unsigned可以,decima1 unsigned
  • 要存成全国人口的编号: int unsigned

日期时间型

日期时间类型包括如下几种:

  • date类型:表示日期,格式类似这样:'0000-00-00'
  • time类型:表示时间,格式类似这样:'00:00:00'
  • datetime类型:表示日期时间,格式类似这样:'0000-00-00 00:00:00'
  • timestamp类型:表示“时间戳”,其实就是一个整数数字,该数字是从是“时间起点”到现在为止的“秒数”。
  • year类型:表示年份,格式为:'0000'

“时间起点”是:1970-1-1 0:0:0,timestamp类型的字段,无需插入数据,而是会自动取得当前的日期时间(表示当前时刻)。

而且,此类型字段会在数据被更新时,也同样自动取得当前的日期时间(表示修改的时刻)。

示例:

创建一个表,设定5个字段分别为上述类型,并插入相应的数据值后查看结果。

-- 演示:创建一个表,设定5个字段分别为上述类型,并插入相应的数据值后查看结果。
create table shijian1(
    t_time time,
    t_date date,
    t_datetime datetime,
    t_timestamp timestamp,
    t_year year
);
insert into shijian1(t_time, t_date, t_datetime, t_year)values
            ('10:57:50','2018-7-29', '2008-8-8 20:0:0', '2008');
select * from shijian1;


insert into shijian1(t_time, t_date, t_datetime, t_year)values
            ('10:57:50','2018-7-29', now() , '2008');

PowerShell输出:

mysql> select * from shijian1;
+----------+------------+---------------------+---------------------+--------+
| t_time   | t_date     | t_datetime          | t_timestamp         | t_year |
+----------+------------+---------------------+---------------------+--------+
| 10:57:50 | 2018-07-29 | 2008-08-08 20:00:00 | 2020-06-15 08:03:02 |   2008 |
+----------+------------+---------------------+---------------------+--------+
1 row in set (0.00 sec)

小细节:timestamp类型在一个表中只能用于一个字段!

字符串型

字符串类型常用的包括:char, varchar, text, enum, set,分述如下:

定长字符char和变长字符varchar

定长字符类型char:适用于存储的字符长度为固定长度的字符,比如中国邮政编码,中国身份证号码,手机号码等。

设定形式:字段名称 char(长度)

其特点是:

  1. 存储的字符长度固定,最长可设定为255个字符。
  2. 如果实际写入的字符不足设定长度,内部会自动用空格填充到设定的长度。
  3. 相对varchar类型,其存取速度更快。

变长字符类型varchar:适用于存储字符长度经常不确定的字符,比如姓名,用户名,标题,内容,等大多数场合的字符。

设定形式:字段名称 varchar(长度)

其特点是:

  1. 存储的字符长度是写入的实际长度,但不超过设定的长度。最长可设定为65532(字节)。

    • 注:由于其最长的限制是字节数,因此存储中文和英文的实际字符个数是不同的;
    • 英文:一个字符占一个字节;
    • 中文(gbk编码):一个字符占2个字节;
    • 中文(utf8编码):一个字符占3个字节;
  2. 如果实际写入的字符不足设定的长度,就按实际的长度存储。
  3. 相对于char字符串,其存取速度相对更慢。

代码示例:

-- 定义一个表,演示char和varchar的使用和区别:
create table char_varchar(
    id int auto_increment primary key,
    postcode char(6),
    user_name varchar(6)
);
insert into char_varchar( id, postcode, user_name) values
                        (null, '100110', 'lisi1');

insert into char_varchar( postcode, user_name) values
                        ('120130', 'lisi2');
insert into char_varchar( postcode, user_name) values
                        ('120150', '罗马里奥');

PowerShell控制台输出:

mysql> select * from char_varchar;
+----+----------+-----------+
| id | postcode | user_name |
+----+----------+-----------+
|  1 | 100110   | lisi1     |
|  2 | 120130   | lisi2     |
|  3 | 120150   | 罗马里奥      |
+----+----------+-----------+
3 rows in set (0.01 sec)

下面给出非常规(非法)的数据情形:

-- 下面给出非常规(非法)的数据情形:
insert into char_varchar( postcode, user_name) values
                        ('120130150', 'lisi3333');
insert into char_varchar( postcode, user_name) values
                        ('120140', 'lisi3333');
-- 以上两行都报错!

PowerShell控制台输出:

mysql> insert into char_varchar( postcode, user_name) values
    ->                                          ('120130150', 'lisi3333');
ERROR 1406 (22001): Data too long for column 'postcode' at row 1

mysql> insert into char_varchar( postcode, user_name) values
    ->                                          ('120140', 'lisi3333');
ERROR 1406 (22001): Data too long for column 'user_name' at row 1

演示不足个数的情况:

-- 演示不足个数的情况:
insert into char_varchar( id, postcode, user_name) values
                        (null, '110', 'ls4');
-- 上一行也可以执行成功!但:
-- postcode字段的值仍然占6个字符
-- 而user_name字段只真3个字符

PowerShell控制台输出:

mysql> select * from char_varchar;
+----+----------+-----------+
| id | postcode | user_name |
+----+----------+-----------+
|  1 | 100110   | lisi1     |
|  2 | 120130   | lisi2     |
|  3 | 120150   | 罗马里奥          |
|  4 | 110      | ls4       |
+----+----------+-----------+
4 rows in set (0.00 sec)

思考题:一个表中有一个字段为c1,其类型为char(10),另有一个字段为c2,类型为varchar,问,c2最多可以设置多长?最多可以存储多少个汉字?

  1. 一个表也有一个“最大字节长度的限制”,也是65532
  2. 则此时,c2最多可以设置65532-10 = 65522(长度设定)
  3. 如果c2中存储的全是英文字符,就可以存储65522个
  4. 如果存储中文:

    • gbk:最多65522/2 = 32761个
    • utf8:最多65522/3 = 21840个

text长文本类型

适用于存储“较长的文本内容”,比如文章内容。最长可存储65535个字符。

如果还需要存储更长的文本,可以使用 mediumtext(1600万左右)或 longtext(40亿左右)。

设定形式:字段名称 text

text类型的字段不能设置默认值。

-- 演示有关text类型和其他相关类型
create table article(    -- 文章表
    id int auto_increment primary key,
    title varchar(100), -- 标题
    author varchar(20),    -- 作者
    content text,
    pub_time datetime, -- 发布时间
    edit_time timestamp -- 更新时间
);

insert into article (title, author, content, pub_time) values
    ('文章标题1','张三','文章内容1','2018-8-8 12:15:30');

insert into article (title, author, content, pub_time) values
    ('文章标题2','张4','文章内容2', now() );

PowerShell控制台输出:

mysql> select * from article;
+----+-----------+--------+-----------+---------------------+---------------------+
| id | title     | author | content   | pub_time            | edit_time           |
+----+-----------+--------+-----------+---------------------+---------------------+
|  1 | 文章标题1        | 张三       | 文章内容1         | 2018-08-08 12:15:30 | 2020-06-15 08:27:40 |
|  2 | 文章标题2        | 张4      | 文章内容2         | 2020-06-15 08:27:42 | 2020-06-15 08:27:42 |
+----+-----------+--------+-----------+---------------------+---------------------+
2 rows in set (0.00 sec)

enum和set类型

enum类型和set类型都是用于存储“有给定值的可选字符”,比如类似表单中的单选,多选,下拉列表。

enum类型(单选类型/枚举类型):enum类型通常用于存储表单中的“单选项”的值。

设定形式:enum(‘选项值1’, ‘选项值2’, ‘选项值3’, ....)

这些选项值都对应了相应的“索引值”,类似索引数组的下标,但是从1开始的。

即这些选项的索引值分别为:1, 2, 3, 4, .....。enum类型最多可设定65535个选项。

set类型(多选类型):set类型通常用于存储表单中的“多选项”的值。

设定形式:set(‘选项值1’, ‘选项值2’, ‘选项值3’, ....)

这些选项值都对应了相应的“索引值”,其索引值从1开始,并“依次翻倍”。即这些选项的索引值分别为:1, 2, 4, 8, 16, ..... (其实就是2的n次方)enum类型最多可设定64个选项值。

示例:

-- enum和set类型:
create table user3(
    id int auto_increment primary key,
    user_name varchar(20),
    user_pass char(32),
    edu enum('小学', '中学','大学'),    -- 单选项数据
    aihao set('篮球','排球','足球','中国足球') -- 多选选数据
);

insert into user3 (user_name, user_pass, edu, aihao)
            values('user1','123','小学','篮球');
insert into user3 (user_name, user_pass, edu, aihao)
            values('user2','123','中学','篮球,排球');

PowerShell控制台输出:

mysql> select * from user3;
+----+-----------+-----------+------+-----------+
| id | user_name | user_pass | edu  | aihao     |
+----+-----------+-----------+------+-----------+
|  1 | user1     | 123       | 小学   | 篮球          |
|  2 | user2     | 123       | 中学    | 篮球,排球         |
+----+-----------+-----------+------+-----------+
2 rows in set (0.00 sec)

错误示例:

mysql> insert into user3 (user_name, user_pass, edu, aihao)              
    ->                  values('user2','123','中学','篮球,排球,乒乓球');
ERROR 1265 (01000): Data truncated for column 'aihao' at row 1

mysql> insert into user3 (user_name, user_pass, edu, aihao)
    ->                  values('user3','123','幼儿园','篮球,排球');
ERROR 1265 (01000): Data truncated for column 'edu' at row 1

使用索引号来插入数据:

-- 使用索引号来插入数据:
insert into user3 (user_name, user_pass, edu, aihao)
            values('user3','123', 3 , 4 );

insert into user3 (user_name, user_pass, edu, aihao)
            values('user4','123', 3 , 5 );
            -- 5 代表1+4,也就是篮球和足球
insert into user3 (user_name, user_pass, edu, aihao)
            values('user5','123', 2 , 7 );
            -- 7 代表1+2+4,也就是篮球和足球
mysql> update user3 set aihao = 5 where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user3;
+----+-----------+-----------+------+-----------+
| id | user_name | user_pass | edu  | aihao     |
+----+-----------+-----------+------+-----------+
|  1 | user1     | 123       | 小学   | 篮球          |
|  2 | user2     | 123       | 中学    | 篮球,排球         |
|  3 | user3     | 123       | 大学    | 足球          |
|  4 | user4     | 123       | 大学    | 篮球,足球         |
|  5 | user5     | 123       | 中学    | 篮球,足球         |
+----+-----------+-----------+------+-----------+
5 rows in set (0.00 sec)

错误示例:

mysql> -- 演示出错的代码(16是没有的,也从现有选项中无法构建)
mysql> insert into user3 (user_name, user_pass, edu, aihao)
    ->                  values('user4','123', 3 , 16 );
ERROR 1265 (01000): Data truncated for column 'aihao' at row 1

列属性

列属性是指定义或创建一个列的时候,可以给列额外增加的“附加特性”。

形式如下:create table 表名 (列名 列类型 [列属性...] );

说明:

  1. 一个列可以有多个列属性;
  2. 多个列属性空格隔开就行。

列属性包括以下这些:

  • null,not null:设定为空,或非空,表明该列数据是否可为空值(null)。
  • default:用于设定列默认值(不给值或给空值null并not null,就会自动使用该值)。使用形式:default 默认值 。
  • primary key:用于设定主键。主键就是一个表中数据的“关键值”,通过该关键值就可以找到该特定的数据行。一个表的主键值不能重复(相等),比如文章表中的文章编号id,比如用户表中的用户名。主键字段必须有值(不能为空)。一个表只能有一个主键(但一个主键可以是1个字段或2个以上的字段联合构成)
  • auto_increment:用于设定一个整数字段的值是“自增长的”,通常用于一个表中的数据行的编号(比如文章编号)。默认情况下自增长值从1开始。一个表只能设定一个字段为自增长特性。
  • unique key:用于设定“唯一键”的特性。唯一键表示一个表中的某字段的值是“唯一的”,“不重复的”。唯一键有点类似primay key,但其值可以为空(null)。一个表可以有多个唯一键。
  • comment:用于设定字段的说明性内容,类似注释,但又不是注释(属于有效的代码)。使用形式: comment ‘文字内容’

代码演示:

-- 演示null/not null, default, primary key 三个字段属性:
create table shuxing_test1 (
    id int primary key,
    userName varchar(10) not null,
    sex enum('男','女')  default '男' not null,
    f4 float null    -- 其实null可以不写,因为每个字段默认就是null
);

-- 数据测试如下:
insert into shuxing_test1(id, userName) values(1,'user1');
insert into shuxing_test1(id, userName, sex, f4) values(2,'user2','女',10000);

-- 下面演示错误的数据
-- 1,跟primary 冲突:
insert into shuxing_test1(id, userName, sex, f4) values(1,'user3','男',20000);

-- 2,跟 not null 冲突:
insert into shuxing_test1(id, userName,sex, f4)values(3, null, '男',15000);
insert into shuxing_test1(id,sex, f4)values(3, '男',15000);

-- 综合演示所有字段属性:创建一个表,并用上以上所有字段属性。字段可包括:id,  kecheng,  keshi,  intro.
create table shuxing_test2(
    id int auto_increment primary key comment '编号值',
    kecheng varchar(20) not null unique key comment '课程名称,不能重复',
    keshi tinyint unsigned default 1 comment '课时',
    intro varchar(1000) comment '课程介绍,应该限制在1000个字符以内为妥'
);

insert into shuxing_test2  values(null, 'PHP', 6, 'web领域最流行的后端语言');

两个注意点:

-- 有关primary key 的进一步探讨:
-- 1,主键的另一种设定方式:
create table tab1 (
    id int auto_increment, 
    name varchar(10),
    primary key(id)        -- 这就是主键的另一种设定方式!
    );

/*
2,多字段主键的含义及设定:
数据样例(成绩表):
学员id    科目    成绩
1        mysql    88
2        PHP        90
1        js        77
2         mysql   85
这种情况,就需要设置“多字段主键”,具体做法如下:
*/

create table  chengji (
    学员ID int,
    科目 varchar(20),
    成绩 tinyint unsigned,
    primary key(学员ID,科目)
);