Jerry Chen

数据类型

字符串

数据类型

下面是mysql支持的字符串类型

类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-65535 字节变长字符串
TINYBLOB0-255字节不超过 255 个字符的二进制字符串
TINYTEXT0-255字节短文本字符串
BLOB0-65 535字节二进制形式的长文本数据
TEXT0-65 535字节长文本数据
MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215字节中等长度文本数据
LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据
LONGTEXT0-4 294 967 295字节极大文本数据

char类型是定长类型，比如定义了20长度的char类型，只存一个字符也占用20个长度，char好处是处理速度快，缺点是空间占用大，把手机号、邮箱、密码等长度相对固定的设置为char类型是不错的选择。

varchar类型与char相反，使用空间受内容影响，可以把文章标题、介绍等设置为 varchar类型更合适。

字符串分二进制与非二进制类型，二进制用于储存图片、声音等文件，非二进制用于储存文本数据。

非二进制文本受字符集和校对规则影响。

字符集

字符集(Character set)是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同。常用的字符集有GBK、BIG5、UTF8。

UTF8字符包含文字内容更广，如韩文、日文、德文兼容度更高，也是推荐使用的字符集。

下面是查看服务器支持的字符集

SHOW CHARACTER SET;
1

表不设置字符集继承数据库，字段不设置字符集继承表的

校对规则

是在字符集内用于字符比较和排序的一套规则，以_ci结束的为大小写不敏感、_bin结束的为不区分大小写。

校对规则就是针对字符串类型排序或者比较的一套规则算法 📌

下面是查看系统支持的校对规则

show COLLATION;
1

当使用不区分大小写的校对规则时A与a是相同的，否则则不相同，这会影响到排序与比对。

如果使用utf8_bin 校对规则时，下面的查询将匹配不到大写的PHP

select * from class WHERE cname = 'php';
1

修改表校对规则，对表的原字段将不影响，只对新增字段影响。

常用函数

大小写转换 UPPER() LOWER()

SELECT UPPER(cname) as cname,LOWER(description) as `desc` from class;
1

Left&right LEFT() RIGHT()

left与right函数用于取左或右指定数量的字符，下面是取班级介绍前8个字符并用… 连接。

SELECT CONCAT(LEFT(description,8),'...') FROM class;
1

mid MID()

从中间取字符串，参数二为起始，参数三为取的字符数量。下面是获取从第二个字符取两个字符值为hp的。

select *  from class where mid(cname,2,2) = 'hp';
1

substring SUBSTRING()

从指定位置开始向右取所有字符串，下面是获取从第二个位置开始的字符值为hp的记录。

select * from class where SUBSTRING(cname,2) = 'hp';
1

char_length

获取字符串长度

SELECT CHAR_LENGTH(cname) from class;
1

concat

连接字符串使用

SELECT concat('编号:',id) as id,concat('班级:',cname) as name FROM class;
1

将所有班级前加上你们好

UPDATE class SET cname = CONCAT('你们好:',cname);
1

截取班级介绍，超过8个字符的后面连接…

IF(confition, then, else)
1
SELECT if(CHAR_LENGTH(cname)>8,CONCAT(LEFT(cname,8),'...'),cname) as name FROM class;
1

cdn网址更新

UPDATE class set cname = CONCAT('https://cdn.com', mid(cname, 10)) where id >= 10;
1

正则表达式

Mysql支持正则表达式操作 REGEXP，可用于处理复杂的匹配操作。

查找第二个字符为h的字符串

SELECT * FROM class WHERE cname REGEXP '^.h';
1

查找班级名称中包含php 或 mysql的记录

SELECT * FROM class WHERE cname REGEXP 'php|mysql';
1

所有介绍中包含php与mysql的课程名前加上你们好

update class set cname = REPLACE(cname,cname,concat('你们好：',cname)) 
where description REGEXP  'php|mysql';
1
2

LIKE

在 LIKE 表达式中 % 用于匹配任意多个字符，_ 用于匹配一个字符。

查找第二个字符为 h 的班级。

SELECT *  FROM class WHERE cname LIKE '_h%';
1

数值类型

整型

MySQL数据类型范围（有符号）范围（无符号）
tinyint(m)1个字节 范围(-128~127)(0，255)
smallint(m)2个字节 范围(-32768~32767)(0，65 535)
mediumint(m)3个字节 范围(-8388608~8388607)(0，16 777 215)
int(m)4个字节 范围(-2147483648~2147483647)(0，4 294 967 295)
bigint(m)8个字节 范围(+-9.22*10的18次方)(0，18 446 744 073 709 551 615)

Tips

  • 取值范围如果加了（无符号）unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。
  • m的含义不是允许字段的长度，而是显示长度，在为字段设置 zerofill 时有效。

前导零

添加有前导零的字段 zerofill

ALTER TABLE class ADD stu_count smallint(6) ZEROFILL default null;
1

在命令行查看（有些GUI软件不显示前导零)，结果如下：

+----+-------+--------------------------------------------+-----------+
| id | cname | description                                | stu_count |
+----+-------+--------------------------------------------+-----------+
|  4 | Mysql | 数据库                                      |    000001 |
|  5 | NEST  | hello                                      |      NULL |
+----+-------+--------------------------------------------+-----------+
1
2
3
4
5
6

浮点型

类型大小范围（有符号）范围（无符号）
FLOAT4 字节(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)
DOUBLE8 字节(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
DECIMALDECIMAL(M,D) ，m<65 是总个数，d<30依赖于M和D的值依赖于M和D的值

下面是检测浮点数精度的示例

alter table class add e FLOAT(10,2);
update class set e = 12345.66 where id=11;     // 12345.66
update class set e = 992345.66 where id=11;    // 992346 近似值

alter table class add b DECIAMAL(10,2);
update class set e = 12345678.66 where id=11;    // 12345678.99
1
2
3
4
5
6

查看结果时会发布浮点数结果不精确

总结

  • float：2^23 = 8388608，一共七位，这意味着最多能有7位有效数字，但绝对能保证的为6位，即float的精度为6~7位有效数字。（可保存七位以内的数据）
  • double：2^52 = 4503599627370496，一共16位，double的精度为15~16位
  • 浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值
  • decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
  • 对货币等对精度敏感的数据，应该用定点数decimal存储

ENUM/SET

ENUM

📗 ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。换个枚举最大可以有 65535 个成员值

ALTER TABLE stu ADD sex ENUM('男','女') DEFAULT NULL;
1

1对应“男”； 2对应“女”

可以使用索引或值添加enum数据

INSERT INTO stu (sname,class_id,sex) VALUES('李岗',1,'男');
INSERT INTO stu (sname,class_id,sex) VALUES('李玉',1,2);
1
2

可以使用值与索引检索ENUM

SELECT * from stu WHERE sex='女';
SELECT * from stu WHERE sex=2;
1
2

SET

📗 SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。

一个 SET 类型最多可以包含 64 项元素。

使用SET类型添加文章属性字段

ALTER TABLE article ADD flag SET('推荐','置顶','图文','热门');
1

添加数据

INSERT INTO article (title,status,flag) VALUES('标题',1,'图文,推荐,置顶');
1

查找

使用 find_in_set 查找数据

SELECT * FROM article WHERE find_in_set('图文',flag);
1

使用like 查找数据

SELECT * FROM article WHERE flag like '%置顶%'
1

二进制比较

可以使用二进制方式对SET类型进行模糊筛选。（二进制值相加转十进制）

SET成员十进制值二进制值
推荐10001
置顶20010
图文40100
热门81000

获取包含推荐的文章

SELECT * FROM article WHERE flag & 1;
1

获取包含推荐或者置顶的文章

SELECT * FROM article WHERE flag & 3;
1

获取包含推荐或者热门的文章

SELECT * FROM article WHERE flag & 9;
1
上次更新: 2022/4/11 20:46:57
贡献者: Jerry Chen