「PHP」Mysql数据类型与列类型

请注意，本文编写于 1687 天前，最后修改于 1687 天前，其中某些信息可能已经过时。

数据类型（列类型）总览

可见，mysql中的数据类型，总体分3大类：

数字型
时间型
字符型

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

整型

整型数据类型包括：

tinyint ：微整型
smallint ：小整型
mediumint ：中整型
int ：整型
bigint ：大整型

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

类型	所占空间 (字节)	最小值（带符号）	最大值（带符号）	最大值（无符号）
tinyint	1	-128	127	255
smallint	2	-32768	32767	65535
mediumint	3	-8388608	8388607	16777215
int	4	-2147483648	2147483647	4294967295
bigint	8	-9223372036854775808	9223372036854775807	18446744073709551615

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

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(长度)

其特点是：

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

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

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

其特点是：

存储的字符长度是写入的实际长度，但不超过设定的长度。最长可设定为65532（字节）。
- 注：由于其最长的限制是字节数，因此存储中文和英文的实际字符个数是不同的；
- 英文：一个字符占一个字节；
- 中文（gbk编码）：一个字符占2个字节；
- 中文（utf8编码）：一个字符占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最多可以设置多长？最多可以存储多少个汉字？

一个表也有一个“最大字节长度的限制”，也是65532
则此时，c2最多可以设置65532-10 = 65522（长度设定）
如果c2中存储的全是英文字符，就可以存储65522个
如果存储中文：
- 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 表名 (列名列类型 [列属性...] )；

说明：

一个列可以有多个列属性；
多个列属性空格隔开就行。

列属性包括以下这些：

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,科目)
);

----- END -----

本文作者： Yacan Man
本文链接：https://blog.manyacan.com/archives/1799/
版权声明：本文章采用 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议 。

Web

「PHP」Mysql数据类型与列类型

数据类型（列类型）总览

整型

小数型

浮点小数

定点小数

日期时间型

字符串型

定长字符char和变长字符varchar

text长文本类型

enum和set类型

列属性

添加新评论

--------------- 此处无声胜有声~ ---------------

「PHP」Mysql数据类型与列类型

数据类型（列类型）总览

整型

小数型

浮点小数

定点小数

日期时间型

字符串型

定长字符char和变长字符varchar

text长文本类型

enum和set类型

列属性

「PHP」Mysql数据操作

「PHP」操作Mysql

添加新评论

--------------- 此处无声胜有声~ ---------------