MariaDB 教程 / 连接、提取、替换字符串并计算长度
字符串函数可以连接、提取、替换或测量文本,生成查询需要的结果。根据任务选择 CONCAT、SUBSTRING、REPLACE 和 CHAR_LENGTH,并先分清 NULL、空字符串、字符数与字节数,避免在处理过程中改变原始数据的含义。
按文本处理任务选择函数
把商品名和分类拼成标签、从固定格式的编号中截取一段、删除电话号码的分隔符,是不同的处理任务。在 SELECT 中调用字符串函数只是计算显示结果,不会自动改写表里保存的名称。先明确哪些信息必须原样保留,哪些只是展示格式,再选择函数。
| 函数 | 用途 | 需要注意的行为 |
|---|---|---|
| CONCAT | 连接字符串 | 普通模式下任一参数为 NULL,结果就是 NULL |
| CONCAT_WS | 带分隔符连接 | 跳过待连接参数中的 NULL,但保留空字符串 |
| SUBSTRING | 按位置与长度提取 | 第一个字符的位置是 1 |
| REPLACE | 替换匹配的子串 | 这里是函数,不是修改表数据的 REPLACE 语句 |
| CHAR_LENGTH / LENGTH | 计算字符数或字节数 | 多字节文本中两者可能不同;LENGTH 还受模式影响 |
列的存储规则可参阅 CHAR、VARCHAR 与 TEXT 的选择,缺失值的含义可参阅 NULL 与空字符串的区别。函数的更多形式见 MariaDB 官方的 字符串函数指南和 SUBSTRING 文档。
准备数据并生成显示标签
用 course_user 连接练习服务器,在 MariaDB 客户端中执行完整代码。也可按照 UTF-8 SQL 文件教程保存文件,再通过实际路径运行 SOURCE C:/mariadb-lab/lesson.sql;。文件编码与连接字符集都应能正确处理 utf8mb4 文本。
执行前核对 SELECT VERSION(), DATABASE();、SHOW TABLES; 与 SELECT @@SESSION.sql_mode;。本节普通示例的预期结果不使用 ORACLE 或 EMPTY_STRING_IS_NULL 模式。若表 products_19_lab 已存在,先查看定义与记录,确认用途;不要覆盖业务表,也不要为了配合示例随意更改服务器全局模式。
USE shopcourse;
CREATE TABLE products_19_lab (
product_id INT NOT NULL AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
note VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (product_id),
CONSTRAINT ck_p19_price CHECK (price >= 0),
CONSTRAINT ck_p19_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_19_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
(2, '볼펜', '문구', 1500.00, 0, ''),
(3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
(4, '스탠드', '생활', 25000.00, 5, NULL),
(5, '파일', '문구', 1500.00, 50, ''),
(6, '케이블', '전자', 8000.00, 0, '호환 확인');
示例中的韩文字符串按原样保留,以便核对相同的 UTF-8 数据。编号 1~6 分别表示笔记本、圆珠笔、马克杯、台灯、文件夹和线缆,分类表示文具、生活用品与电子产品。编号 1、4 的备注是 NULL,编号 2、5 是空字符串,编号 3、6 有实际备注。
SELECT product_id, CONCAT(product_name,' / ',category) AS label,
CONCAT_WS(' / ',product_name,note) AS note_label
FROM products_19_lab ORDER BY product_id;
编号 1 的 label 为 노트 / 문구,意思是“笔记本 / 文具”;note_label 则只有 노트,因为 CONCAT_WS 跳过 NULL 备注。编号 2 的 note_label 为 볼펜 / ,末尾仍有分隔符,因为空字符串参与了连接。编号 3 则正常连接名称与“礼物包装”备注。
这里不要为了去掉一个不想要的分隔符,就直接把数据库里的空备注改成 NULL。先确定展示规则是否把两种状态视为相同;如需转换,应在查询表达式中明确体现,并保留原始值供核对。
按字符位置提取并替换分隔符
SELECT SUBSTRING('MariaDB',1,5), SUBSTRING('MariaDB',-2);
SELECT REPLACE('010-1234-5678','-','') AS normalized_phone;
第一条返回 Maria 与 DB,第二条返回 01012345678。SUBSTRING 从位置 1 开始,负数位置从末尾计数;这里 -2 指向倒数第二个字符,再取到字符串末尾。位置与长度按字符计算,不是直接截取 UTF-8 字节。
只有当业务编号确实具有固定的位置结构时,才适合按位置拆分。如果前缀长度可变或字段含可选段,固定截取可能得到错误的部分。结果太短时,先核对起始位置、长度和输入值,不要只看最终格式像不像目标。
REPLACE 在这里生成替换后的字符串,并没有更新表。电话号码去掉连字符后仍应作为字符串处理;转换成数字可能丢失开头的 0,也无法仅靠去标点处理国家区号与分机。标识符规范化应根据完整的业务格式决定,不能等同于删除所有符号。
分清 UTF-8 字符数、字节数与视觉符号
SELECT CHAR_LENGTH('한글'), LENGTH('한글'),
CHAR_LENGTH('🙂'), LENGTH('🙂');
在 utf8mb4 输入与连接条件下,한글 是两个字符、六个字节;示例笑脸是一个字符、四个字节。普通模式下 CHAR_LENGTH 计数字符,LENGTH 计数字节。因此“最多 100 个字符”和“最多 100 字节”是不同约束,不能用同一个长度检查替代。
视觉上一个符号还可能由多个 Unicode 码点组合而成,例如带组合标记的文字或某些表情序列。CHAR_LENGTH 不应直接理解成任何情况下的“用户看到的符号数”。同样,任意截断 UTF-8 的字节可能切断编码,产生无效文本。
ORACLE 兼容模式是一个明确的例外:该模式下 LENGTH 按字符计数,可用 OCTET_LENGTH 明确检查字节数。应先读取当前连接的 sql_mode,再解释结果;不要仅因函数同名就假定不同模式下单位一致。这个差异可查阅官方的 LENGTH 模式说明。
分别处理 NULL、空文本与首尾空格
SELECT CONCAT('노트',NULL),
CONCAT_WS(' / ','노트',NULL,'');
SELECT CONCAT('[',TRIM(' 노트 '),']');
普通模式下第一项 CONCAT 的结果为 NULL,CONCAT_WS 的结果为 노트 / ,仍然保留最后的空字符串。TRIM 示例则去掉名称两侧的普通空格,得到方括号包围的 [노트]。不要把这些行为混成一种“清理空内容”的规则。
TRIM 不是通用的 Unicode 空白清除器,也不会默认删除文本内部的空格。例如名称中间的空格可能有意义,不换行空格也不能一概视为普通空格。姓名、编号或密码是否允许空格,应由对应的输入与存储规则决定。
ORACLE 模式下 CONCAT 会忽略 NULL,因此普通模式的 NULL 结果不能直接套用到该模式。官方 CONCAT 文档区分了这个例外。比较大小写、重音或尾随空格时,还应同时检查数据类型与排序规则;更换一个字符串函数并不能自动修正比较策略。
先并排核对原值与转换值,再考虑写回
正式转换前,用 SELECT 同时显示主键、原始字段与计算结果,检查每一类边界数据。结果为 NULL 时逐个检查参数;提取长度不对时检查位置;韩文乱码时核对文件编码、客户端连接字符集和实际保存的数据。不要通过反复替换来掩盖编码问题。
如果确认需要 UPDATE,使用 修改前确认目标行的流程限定范围,核对预期旧值与实际结果,并准备恢复措施。本节仅通过查询生成展示结果,原始六行不需要被改写。
需要重新开始时,先核对数据库、表结构与现有记录,备份应保留的数据。下面的清理语句仅针对已确认可丢弃的本节练习表,会删除定义和全部记录,普通 ROLLBACK 无法恢复;无需清理时可以保留表继续检查。
DROP TABLE shopcourse.products_19_lab;




