企业官网建设流程全解析

五、数据类型

5.1 数值类型

MySQL的数据类型主要分为数值类型、文本和二进制类型、时间日期类型以及String类型四大类。

整数类型按存储空间从小到大依次为TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT，各类型的字节数和取值范围如下：

整型默认是有符号的，可通过UNSIGNED关键字指定为无符号。

以TINYINT为例，有符号范围为-128至127，无符号范围为0至255。

插入超出范围的值时，MySQL会报错：

create table tt1(num tinyint); insert into tt1 values(1); -- 成功 insert into tt1 values(128); -- 越界，报错

实际开发中建议尽量不使用UNSIGNED，因为对于int类型可能存不下的数据，int unsigned同样可能存不下，不如直接将类型提升为bigint。

（1）bit类型

bit[(M)]为位字段类型，M表示每个值的位数，范围1至64，默认为1。

bit字段在显示时按照ASCII码对应值显示，例如插入65会显示为字符A。

create table tt5(gender bit(1)); insert into tt5 values(0); insert into tt5 values(1); insert into tt5 values(2); -- 越界，报错

（2）小数类型

float[(M,D)]中M指定显示长度，D指定小数位数，占用4字节。

例如float(4,2)表示范围为-99.99至99.99，保存值时会进行四舍五入。

create table tt6(id int, salary float(4,2)); insert into tt6 values(100, -99.99); insert into tt6 values(101, -99.991); -- 四舍五入后为-99.99

decimal(M,D)为定点数，精度高于float。

float的精度大约为7位有效数字，而decimal整数最大位数为65，小数最大位数为30。

create table tt8 (id int, salary float(10,8), salary2 decimal(10,8)); insert into tt8 values(100, 23.12345612, 23.12345612); -- 查询结果：salary为23.12345695，salary2为23.12345612

5.2 字符串类型

（1）char类型

char(L)为固定长度字符串，L表示可存储的字符数，最大为255。

char(2)可以存放两个字母或两个汉字。

create table tt9(id int, name char(2)); insert into tt9 values(100, 'ab'); insert into tt9 values(101, '中国');

（2）varchar类型

varchar(L)为可变长度字符串，L表示字符长度，最大为65535个字节。

其中有1至3个字节用于记录数据长度，因此有效字节数约为65532。

在utf8编码下，一个字符占用3字节，varchar(n)的n最大值约为21844；在gbk编码下，一个字符占用2字节，n最大值约为32766。

（3）char与varchar的比较

char为定长存储，无论实际数据长度多少都占用固定空间，磁盘空间浪费较多但读写效率较高；varchar为变长存储，按实际数据长度分配空间，节省磁盘但效率相对较低。

5.3 日期时间类型

常用的日期时间类型有三种：

使用示例：

create table birthday (t1 date, t2 datetime, t3 timestamp); insert into birthday(t1, t2) values('1997-7-1', '2008-8-8 12:1:1'); -- t3字段会自动填充为当前时间

5.4 枚举与集合类型

（1）枚举类型ENUM

ENUM是一个字符串对象，其值从建表时指定的枚举列表中选取，一次只能选一个值。

常用于性别、状态等单选场景。

（2）集合类型SET

SET也是字符串对象，可以有零个或多个值，各值之间用逗号分隔。

常用于多选场景，如兴趣爱好。

以下示例创建一个调查表，hobby字段为集合类型，gender字段为枚举类型：

create table votes( username varchar(30), hobby set('登山','游泳','篮球','武术'), gender enum('男','女') ); insert into votes values('雷锋', '登山,武术', '男'); insert into votes values('Juse', '登山,武术', 2); -- 2对应枚举中的'女'

查询集合中包含某个值的记录时，不能直接用等号比较，等号要求完全匹配。

应使用find_in_set函数：

select * from votes where find_in_set('登山', hobby);

find_in_set(sub, str_list)的作用是：如果sub在以逗号分隔的str_list中，则返回对应的下标位置；如果不在，则返回0。

六、表的约束

数据类型本身对字段有一定约束，但这种约束较为单一。

从业务逻辑角度确保数据正确性，还需要使用表的约束。

常用约束包括空属性、默认值、列描述、zerofill、主键、自增长、唯一键和外键。

6.1 空属性与默认值

（1）空属性

空属性有两个取值：null和not null。

数据库字段默认为空，因为null值无法参与运算，1+null的结果仍为null。

以班级表为例，班级名和教室名都不应为空，否则无法确定班级信息：

create table myclass( class_name varchar(20) not null, class_room varchar(10) not null ); -- 插入时未给class_room赋值，会报错 insert into myclass(class_name) values('class1');

（2）默认值

当某个字段的数据经常出现某个固定值时，可以在定义时指定默认值。

插入数据时不给该字段赋值，就会使用默认值。

create table tt10 ( name varchar(20) not null, age tinyint unsigned default 0, sex char(2) default '男' ); insert into tt10(name) values('zhangsan'); -- 查询结果：age为0，sex为'男'

需要注意的是，只有设置了default的列，插入时才可以省略该列。

6.2 列描述与zerofill

（1）列描述comment

comment用于描述字段含义，会随建表语句保存，方便程序员或DBA了解字段用途。

desc命令无法查看注释，需通过show create table查看：

create table tt12 ( name varchar(20) not null comment '姓名', age tinyint unsigned default 0 comment '年龄', sex char(2) default '男' comment '性别' ); show create table tt12\G

（2）zerofill

整型字段后括号中的数字，在没有zerofill属性时没有实际意义。

添加zerofill后，如果数值宽度小于设定宽度，会自动在左侧填充0。

alter table tt3 change a a int(5) unsigned zerofill; -- 查询时a的值1会显示为00001 -- 使用hex(a)可验证内部存储仍为1

6.3 主键与自增长

（1）主键primary key

主键用于唯一约束字段中的数据，不能重复且不能为空。

一张表中最多只能有一个主键，主键所在的列通常是整数类型。

create table tt13 ( id int unsigned primary key comment '学号不能为空', name varchar(20) not null ); insert into tt13 values(1, 'aaa'); -- 成功 insert into tt13 values(1, 'aaa'); -- 重复，报错

表创建后若未指定主键，可追加主键：

alter table 表名 add primary key(字段列表);

删除主键：

alter table 表名 drop primary key;

（2）自增长auto_increment

自增长字段在不给值时，系统会自动从当前最大值加1生成新值。

自增长的特点：

• 字段本身必须是一个索引

• 字段必须是整数类型

• 一张表最多只能有一个自增长字段

create table tt21( id int unsigned primary key auto_increment, name varchar(10) not null default '' ); insert into tt21(name) values('a'); -- id自动为1 insert into tt21(name) values('b'); -- id自动为2 select last_insert_id(); -- 获取上次插入的自增长值

6.4 唯一键

一张表中往往有多个字段需要保证唯一性，但一张表只能有一个主键。

主键更多用于标识记录的唯一性，唯一键更多用于保证业务字段不与其他信息重复。

一般建议将主键设计为与当前业务无关的字段，这样业务调整时不会对主键造成过大影响。

create table student ( id char(10) unique comment '学号，不能重复，但可以为空', name varchar(10) ); insert into student(id, name) values('01', 'aaa'); -- 成功 insert into student(id, name) values('01', 'bbb'); -- 重复，报错 insert into student(id, name) values(null, 'bbb'); -- 可以为空，成功

6.5 外键

外键用于定义主表和从表之间的关联关系。

外键约束定义在从表上，主表的对应列必须具有主键约束或unique约束。

以学生表和班级表为例，如果将班级信息冗余在每个学生记录中，会造成数据冗余。

创建外键时，需先创建主表，再创建从表：

-- 先创建主表（班级表） create table myclass ( id int primary key, name varchar(30) not null comment '班级名' ); -- 再创建从表（学生表） create table stu ( id int primary key, name varchar(30) not null comment '学生名', class_id int, foreign key (class_id) references myclass(id) );

正常插入数据：

insert into myclass values(10, 'C++大牛班'), (20, 'java大神班'); insert into stu values(100, '张三', 10), (101, '李四', 20);

插入一个不存在的班级号时，外键约束会阻止插入：

insert into stu values(102, 'wangwu', 30); -- 报错，外键约束失败

若学生暂时未分配班级，class_id可以插入null值。

6.6 综合案例

以下是一个商店数据库的综合设计案例，包含商品表、客户表和购买表三张表，综合运用了主键、自增长、非空、唯一键、枚举和外键等约束。

-- 创建数据库 create database if not exists bit32mall default character set utf8; use bit32mall; -- 商品表 create table if not exists goods ( goods_id int primary key auto_increment comment '商品编号', goods_name varchar(32) not null comment '商品名称', unitprice int not null default 0 comment '单价，单位分', category varchar(12) comment '商品分类', provider varchar(64) not null comment '供应商名称' ); -- 客户表 create table if not exists customer ( customer_id int primary key auto_increment comment '客户编号', name varchar(32) not null comment '客户姓名', address varchar(256) comment '客户地址', email varchar(64) unique key comment '电子邮箱', sex enum('男','女') not null comment '性别', card_id char(18) unique key comment '身份证' ); -- 购买表 create table if not exists purchase ( order_id int primary key auto_increment comment '订单号', customer_id int comment '客户编号', goods_id int comment '商品编号', nums int default 0 comment '购买数量', foreign key (customer_id) references customer(customer_id), foreign key (goods_id) references goods(goods_id) );

该设计中，商品表和客户表以自增长主键作为唯一标识；客户表的邮箱和身份证通过唯一键保证不重复；购买表通过两个外键分别关联客户表和商品表，确保订单数据的引用完整性。

以上内容覆盖了MySQL从环境搭建到表结构设计的基础知识。

掌握这些内容后，可以进一步学习数据的增删改查、索引优化和事务管理等进阶主题。