字符串函数
TiDB 支持使用大部分 MySQL 5.7 中提供的字符串函数、一部分 MySQL 8.0 中提供的字符串函数和一部分 Oracle 21 所提供的函数。
关于 Oracle 函数和 TiDB 函数的对照关系,请参考 Oracle 与 TiDB 函数和语法差异对照。
支持的函数
ASCII()
ASCII()
函数用于获取输入的参数中最左字符的 ASCII 值。该参数可以为字符串或数字。
- 如果输入参数不为空,该函数返回参数中最左字符的 ASCII 值。
- 如果输入参数为空字符串,该函数返回
0
。 - 如果输入参数为
NULL
,该函数返回NULL
。
查询示例:
SELECT ASCII('A'), ASCII('TiDB'), ASCII(23);
返回结果:
+------------+---------------+-----------+
| ASCII('A') | ASCII('TiDB') | ASCII(23) |
+------------+---------------+-----------+
| 65 | 84 | 50 |
+------------+---------------+-----------+
BIN()
BIN()
函数用于将输入的参数转换为其二进制值的字符串表示形式。该参数可以为字符串或数字。
- 如果输入参数为正数,该函数返回该参数的二进制值的字符串表示形式。
- 如果输入参数为负数,该函数会将该参数的绝对值转换为其二进制值,然后对二进制值的每位取反(
0
变为1
,1
变为0
),最后加上1
。 - 如果输入参数为字符串,且该字符串中只包含数字,该函数将按照该数字返回结果。例如,
"123"
与123
的返回结果相同。 - 如果输入参数为字符串,且该字符串第一个字符不是数字(如
"q123"
),该函数返回0
。 - 如果输入参数为字符串,且该字符串由数字和非数字组成,该函数将按照该参数中最前面连续的数字返回结果。例如,
'123q123'
与123
的返回结果相同,但BIN('123q123')
会产生一个Truncated incorrect INTEGER value: '123q123'
的警告。 - 如果输入参数为
NULL
,该函数返回NULL
。
查询示例 1:
SELECT BIN(123), BIN('123q123');
返回结果 1:
+----------+----------------+
| BIN(123) | BIN('123q123') |
+----------+----------------+
| 1111011 | 1111011 |
+----------+----------------+
查询示例 2:
SELECT BIN(-7);
返回结果 2:
+------------------------------------------------------------------+
| BIN(-7) |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111001 |
+------------------------------------------------------------------+
BIT_LENGTH()
BIT_LENGTH()
函数用于返回输入参数的长度,单位为 bit。
示例:
SELECT BIT_LENGTH("TiDB");
+--------------------+
| BIT_LENGTH("TiDB") |
+--------------------+
| 32 |
+--------------------+
每个字符 8 位 x 4 个字符 = 32 位
SELECT BIT_LENGTH("PingCAP 123");
+---------------------------+
| BIT_LENGTH("PingCAP 123") |
+---------------------------+
| 88 |
+---------------------------+
每个字符 8 位(空格也会被计算在内,因为它是非字母数字字符) x 11 个字符 = 88 位
SELECT CustomerName, BIT_LENGTH(CustomerName) AS BitLengthOfName FROM Customers;
+--------------------+-----------------+
| CustomerName | BitLengthOfName |
+--------------------+-----------------+
| Albert Einstein | 120 |
| Robert Oppenheimer | 144 |
+--------------------+-----------------+
CHAR()
CHAR()
函数用于获取指定 ASCII 值的对应字符。该函数执行的操作与 ASCII()
相反,ASCII()
用于返回指定字符的 ASCII 值。
示例:
SELECT CHAR(65);
+------------+
| CHAR(65) |
+------------+
| A |
+------------+
SELECT CHAR(84);
+------------+
| CHAR(84) |
+------------+
| T |
+------------+
CHAR()
函数还可用于获取超出标准 ASCII 范围(0
- 127
)的 ASCII 值的对应字符。
/*For extended ASCII: */
SELECT CHAR(128);
+------------+
| CHAR(128) |
+------------+
| 0x80 |
+------------+
CHAR()
函数还可用于获取 Unicode 值的对应字符。
/* For Unicode: */
--skip-binary-as-hex
SELECT CHAR(50089);
+--------------+
| CHAR(50089) |
+--------------+
| é |
+--------------+
CHAR_LENGTH()
CHAR_LENGTH()
函数用于获取输入参数中字符的总数。
示例:
SELECT CHAR_LENGTH("TiDB") AS LengthOfString;
+----------------+
| LengthOfString |
+----------------+
| 4 |
+----------------+
SELECT CustomerName, CHAR_LENGTH(CustomerName) AS LenghtOfName FROM Customers;
+--------------------+--------------+
| CustomerName | LenghtOfName |
+--------------------+--------------+
| Albert Einstein | 15 |
| Robert Oppenheimer | 18 |
+--------------------+--------------+
CHARACTER_LENGTH()
CHARACTER_LENGTH()
函数与 CHAR_LENGTH()
函数功能相同,返回结果相同,可以互换使用。
CONCAT()
CONCAT()
函数用于将输入的参数连接成一个字符串。
语法:
CONCAT(str1,str2,...)
str1, str2, ...
为要连接的参数。该参数可以是字符串或数字。
查询示例:
SELECT CONCAT('TiDB', ' ', 'Server', '-', 1, TRUE);
返回结果:
+---------------------------------------------+
| CONCAT('TiDB', ' ', 'Server', '-', 1, TRUE) |
+---------------------------------------------+
| TiDB Server-11 |
+---------------------------------------------+
如果任一参数的值为 NULL
, 则 CONCAT()
返回 NULL
。
查询示例:
SELECT CONCAT('TiDB', NULL, 'Server');
返回结果:
+--------------------------------+
| CONCAT('TiDB', NULL, 'Server') |
+--------------------------------+
| NULL |
+--------------------------------+
除了使用 CONCAT()
函数外,你也可以通过字符串彼此相邻的方式获取拼接字符串,但是该方式不支持数字类型。例如:
SELECT 'Ti' 'DB' ' ' 'Server';
返回结果:
+-------------+
| Ti |
+-------------+
| TiDB Server |
+-------------+
CONCAT_WS()
CONCAT_WS()
函数是一种带分隔符的 CONCAT()
,返回由分隔符连接的字符串。
语法:
CONCAT_WS(separator,str1,str2,...)
separator
:第一个参数为分隔符,用于连接其余的不为NULL
的参数。str1, str2, ...
:要连接的参数。该参数可以为字符串或数字。
查询示例:
SELECT CONCAT_WS(',', 'TiDB Server', 'TiKV', 'PD');
返回结果:
+---------------------------------------------+
| CONCAT_WS(',', 'TiDB Server', 'TiKV', 'PD') |
+---------------------------------------------+
| TiDB Server,TiKV,PD |
+---------------------------------------------+
如果分隔符为空,则
CONCAT_WS()
等效于CONCAT()
,返回其余参数连接后的字符串。查询示例:
SELECT CONCAT_WS('', 'TiDB Server', 'TiKV', 'PD');返回结果:
+--------------------------------------------+ | CONCAT_WS('', 'TiDB Server', 'TiKV', 'PD') | +--------------------------------------------+ | TiDB ServerTiKVPD | +--------------------------------------------+如果分隔符为
NULL
,则CONCAT_WS()
返回NULL
。查询示例:
SELECT CONCAT_WS(NULL, 'TiDB Server', 'TiKV', 'PD');返回结果:
+----------------------------------------------+ | CONCAT_WS(NULL, 'TiDB Server', 'TiKV', 'PD') | +----------------------------------------------+ | NULL | +----------------------------------------------+如果用于连接的参数中只有一个不为
NULL
,则CONCAT_WS()
返回此参数。查询示例:
SELECT CONCAT_WS(',', 'TiDB Server', NULL);返回结果:
+-------------------------------------+ | CONCAT_WS(',', 'TiDB Server', NULL) | +-------------------------------------+ | TiDB Server | +-------------------------------------+如果用于连接的参数中有
NULL
,CONCAT_WS()
会忽略NULL
。查询示例:
SELECT CONCAT_WS(',', 'TiDB Server', NULL, 'PD');返回结果:
+-------------------------------------------+ | CONCAT_WS(',', 'TiDB Server', NULL, 'PD') | +-------------------------------------------+ | TiDB Server,PD | +-------------------------------------------+如果用于连接的参数中有空字符串,
CONCAT_WS()
不会忽略该字符串。查询示例:
SELECT CONCAT_WS(',', 'TiDB Server', '', 'PD');返回结果:
+-----------------------------------------+ | CONCAT_WS(',', 'TiDB Server', '', 'PD') | +-----------------------------------------+ | TiDB Server,,PD | +-----------------------------------------+
ELT()
返回指定位置的字符串
EXPORT_SET()
返回一个字符串,其中值位中设置的每个位,可以得到一个 on 字符串,而每个未设置的位,可以得到一个 off 字符串
FIELD()
返回参数在后续参数中出现的第一个位置
FIND_IN_SET()
返回第一个参数在第二个参数中出现的位置
FORMAT()
FORMAT(X,D[,locale])
函数用于将数字 X
格式化为类似于 “#,###,###.##”
的格式,四舍五入保留 D
位小数,并将结果作为字符串返回。
参数:
X
:要格式化的数字。可以是直接的数字值、数字字符串、或科学记数法格式的数字。D
:指定返回值的小数位数。该函数根据D
对X
进行四舍五入。如果D
大于X
的实际小数位数,则会在结果中填充相应长度的零。[locale]
:指定一个区域设置,用于结果中数字的小数点、千位分隔符和分隔符之间的分组。合法的区域设置值与lc_time_names
系统变量的合法值相同。如果未指定或者设置为NULL
,则默认使用'en_US'
区域设置。该参数可选。
行为细节:
- 如果输入的第一个参数为字符串,且该字符串中只包含数字时,该函数将按照该数字返回结果。例如,
FORMAT('12.36', 1)
与FORMAT(12.36, 1)
的返回结果相同。 - 如果输入的第一个参数为科学计数法(
E/e
)表示的数字时,该函数将按照该数字返回结果。例如,FORMAT('1E2', 3)
),函数返回100.000
。 - 如果输入的第一个参数为非数字开头的字符串时,该函数除了返回零值外,还返回一个警告
(Code 1292)
。例如,FORMAT('q12.36', 5)
函数返回0.00000
,还会包含一个警告Warning (Code 1292): Truncated incorrect DOUBLE value: 'q12.36'
。 - 如果输入的第一个参数为数字和非数字混合的字符串时,该函数将基于该参数中开头连续的数字部分返回结果,还返回一个警告
(Code 1292)
。例如,FORMAT('12.36q56.78', 1)
与FORMAT('12.36', 1)
的返回的数字结果相同,但FORMAT('12.36q56.78', 1)
还会包含一个警告Warning (Code 1292): Truncated incorrect DOUBLE value: '12.36q56.78'
。 - 如果输入的第二个参数为零或负数,该函数将四舍五入小数部分并返回整数。
- 如果输入的任意参数为
NULL
,函数将返回NULL
。
示例:
格式化数字 12.36 到不同的小数位数:
mysql> SELECT FORMAT(12.36, 1);
+------------------+
| FORMAT(12.36, 1) |
+------------------+
| 12.4 |
+------------------+
mysql> SELECT FORMAT(12.36, 5);
+------------------+
| FORMAT(12.36, 5) |
+------------------+
| 12.36000 |
+------------------+
mysql> SELECT FORMAT(1234.56, 1, 'en_US');
+-----------------------------+
| FORMAT(1234.56, 1, 'en_US') |
+-----------------------------+
| 1,234.6 |
+-----------------------------+
FROM_BASE64()
FROM_BASE64(str)
函数用于对 Base64 编码的字符串进行解码,并将解码结果以十六进制字符串的形式返回。
- 此函数接受一个单一参数,即需要解码的 Base64 编码字符串。
- 如果输入参数为
NULL
或无效的 Base64 编码字符串,FROM_BASE64()
函数将返回NULL
。
示例:
以下示例解码 Base64 编码的字符串 'SGVsbG8gVGlEQg=='
,该字符串是 'Hello TiDB'
经过 TO_BASE64()
函数编码的结果。
mysql> SELECT TO_BASE64('Hello TiDB');
+-------------------------+
| TO_BASE64('Hello TiDB') |
+-------------------------+
| SGVsbG8gVGlEQg== |
+-------------------------+
mysql> SELECT FROM_BASE64('SGVsbG8gVGlEQg==');
+------------------------------------------------------------------+
| FROM_BASE64('SGVsbG8gVGlEQg==') |
+------------------------------------------------------------------+
| 0x48656C6C6F2054694442 |
+------------------------------------------------------------------+
mysql> SELECT CONVERT(FROM_BASE64('SGVsbG8gVGlEQg==') USING utf8mb4);
+--------------------------------------------------------+
| CONVERT(FROM_BASE64('SGVsbG8gVGlEQg==') USING utf8mb4) |
+--------------------------------------------------------+
| Hello TiDB |
+--------------------------------------------------------+
以下示例解码 Base64 编码的数字 MTIzNDU2
,该字符串是 123456
经过 TO_BASE64()
函数编码的结果。
mysql> SELECT FROM_BASE64('MTIzNDU2');
+--------------------------------------------------+
| FROM_BASE64('MTIzNDU2') |
+--------------------------------------------------+
| 0x313233343536 |
+--------------------------------------------------+
HEX()
HEX()
函数用于将输入的参数转换为其十六进制值的字符串表示形式。该参数可以为字符串或数字。
- 如果输入参数为字符串,
HEX(str)
返回str
的十六进制字符串表示。该函数将str
中每个字符的每个字节转换为两个十六进制数字。例如,在 UTF-8 或 ASCII 字符集中,字符a
的二进制表示为00111101
,十六进制表示为61
。 - 如果输入参数为数字,
HEX(n)
返回n
的十六进制字符串表示。该函数将参数n
视为BIGINT
数字,相当于CONV(n, 10, 16)
。 - 如果输入参数为
NULL
,该函数返回NULL
。
示例:
SELECT X'616263', HEX('abc'), UNHEX(HEX('abc')), 0x616263;
+-----------+------------+-------------------+----------+
| X'616263' | HEX('abc') | UNHEX(HEX('abc')) | 0x616263 |
+-----------+------------+-------------------+----------+
| abc | 616263 | abc | abc |
+-----------+------------+-------------------+----------+
SELECT X'F09F8DA3', HEX('🍣'), UNHEX(HEX('🍣')), 0xF09F8DA3;
+-------------+-------------+--------------------+------------+
| X'F09F8DA3' | HEX('🍣') | UNHEX(HEX('🍣')) | 0xF09F8DA3 |
+-------------+-------------+--------------------+------------+
| 🍣 | F09F8DA3 | 🍣 | 🍣 |
+-------------+-------------+--------------------+------------+
SELECT HEX(255), CONV(HEX(255), 16, 10);
+----------+------------------------+
| HEX(255) | CONV(HEX(255), 16, 10) |
+----------+------------------------+
| FF | 255 |
+----------+------------------------+
SELECT HEX(NULL);
+-----------+
| HEX(NULL) |
+-----------+
| NULL |
+-----------+
INSERT()
INSERT(str, pos, len, newstr)
函数用于将字符串 str
中的一个子字符串(从位置 pos
开始,长度为 len
)替换为字符串 newstr
。该函数是多字节安全的。
- 如果
pos
超过了str
的长度,函数返回原始字符串str
而不做修改。 - 如果
len
超过了从位置pos
开始的str
的剩余长度,函数将从位置pos
开始替换字符串的其余部分。 - 如果任一参数为
NULL
,该函数返回NULL
。
示例:
SELECT INSERT('He likes tennis', 4, 5, 'plays');
+------------------------------------------+
| INSERT('He likes tennis', 4, 5, 'plays') |
+------------------------------------------+
| He plays tennis |
+------------------------------------------+
SELECT INSERT('He likes tennis', -1, 5, 'plays');
+-------------------------------------------+
| INSERT('He likes tennis', -1, 5, 'plays') |
+-------------------------------------------+
| He likes tennis |
+-------------------------------------------+
SELECT INSERT('He likes tennis', 4, 100, 'plays');
+--------------------------------------------+
| INSERT('He likes tennis', 4, 100, 'plays') |
+--------------------------------------------+
| He plays |
+--------------------------------------------+
SELECT INSERT('He likes tennis', 10, 100, '🍣');
+-------------------------------------------+
| INSERT('He likes tennis', 10, 100, '🍣') |
+-------------------------------------------+
| He likes 🍣 |
+-------------------------------------------+
SELECT INSERT('PingCAP 数据库', 1, 7, 'TiDB');
+-------------------------------------------+
| INSERT('PingCAP 数据库', 1, 7, 'TiDB') |
+-------------------------------------------+
| TiDB 数据库 |
+-------------------------------------------+
INSTR()
INSTR(str, substr)
函数用于获取子字符串 substr
在字符串 str
中第一次出现的位置。substr
和 str
均可以为字符串或数字。该函数与 LOCATE(substr, str)
函数的两参数版本功能相同,但参数顺序相反。
- 如果任一输入参数为数字,该函数将数字视为字符串处理。
- 如果
substr
不在str
中,函数返回0
。否则,返回substr
在str
中第一次出现的位置。 - 如果任一参数为
NULL
,该函数返回NULL
。
示例:
SELECT INSTR("pingcap.com", "tidb");
+------------------------------+
| INSTR("pingcap.com", "tidb") |
+------------------------------+
| 0 |
+------------------------------+
SELECT INSTR("pingcap.com/tidb", "tidb");
+-----------------------------------+
| INSTR("pingcap.com/tidb", "tidb") |
+-----------------------------------+
| 13 |
+-----------------------------------+
SELECT INSTR("pingcap.com/tidb" COLLATE utf8mb4_bin, "TiDB");
+-------------------------------------------------------+
| INSTR("pingcap.com/tidb" COLLATE utf8mb4_bin, "TiDB") |
+-------------------------------------------------------+
| 0 |
+-------------------------------------------------------+
SELECT INSTR("pingcap.com/tidb" COLLATE utf8mb4_general_ci, "TiDB");
+--------------------------------------------------------------+
| INSTR("pingcap.com/tidb" COLLATE utf8mb4_general_ci, "TiDB") |
+--------------------------------------------------------------+
| 13 |
+--------------------------------------------------------------+
SELECT INSTR(0123, "12");
+-------------------+
| INSTR(0123, "12") |
+-------------------+
| 1 |
+-------------------+
LCASE()
LCASE(str)
函数与 LOWER(str)
函数功能相同,都是返回输入参数的小写形式。
LEFT()
LEFT()
函数用于返回字符串左侧指定数量的字符。
语法:
LEFT(`str`, `len`)
str
:要提取字符的原始字符串。如果str
包含一个多字节字符,该函数将其视为一个字符。len
:要返回的字符长度。- 如果
len
小于或等于 0,该函数返回空字符串。 - 如果
len
大于或等于str
的长度,该函数将返回原始的str
。
- 如果
- 如果任何参数为
NULL
,该函数返回NULL
。
示例:
SELECT LEFT('ABCED', 3);
+------------------+
| LEFT('ABCED', 3) |
+------------------+
| ABC |
+------------------+
SELECT LEFT('ABCED', 6);
+------------------+
| LEFT('ABCED', 6) |
+------------------+
| ABCED |
+------------------+
SELECT LEFT('ABCED', 0);
+------------------+
| LEFT('ABCED', 0) |
+------------------+
| |
+------------------+
SELECT LEFT('ABCED', -1);
+-------------------+
| LEFT('ABCED', -1) |
+-------------------+
| |
+-------------------+
SELECT LEFT('🍣ABC', 3);
+--------------------+
| LEFT('🍣ABC', 3) |
+--------------------+
| 🍣AB |
+--------------------+
SELECT LEFT('ABC', NULL);
+-------------------+
| LEFT('ABC', NULL) |
+-------------------+
| NULL |
+-------------------+
SELECT LEFT(NULL, 3);
+------------------------------+
| LEFT(NULL, 3) |
+------------------------------+
| NULL |
+------------------------------+
LENGTH()
LENGTH()
函数用于返回字符串的字节长度。LENGTH()
将单个多字节字符视为多个字节,而 CHAR_LENGTH()
将单个多字节字符视为单个字符。
如果输入参数为 NULL
,该函数将返回 NULL
。
示例:
SELECT LENGTH('ABC');
+---------------+
| LENGTH('ABC') |
+---------------+
| 3 |
+---------------+
SELECT LENGTH('🍣ABC');
+-------------------+
| LENGTH('🍣ABC') |
+-------------------+
| 7 |
+-------------------+
SELECT CHAR_LENGTH('🍣ABC');
+------------------------+
| CHAR_LENGTH('🍣ABC') |
+------------------------+
| 4 |
+------------------------+
SELECT LENGTH(NULL);
+--------------+
| LENGTH(NULL) |
+--------------+
| NULL |
+--------------+
LIKE
LIKE
用于进行简单字符串匹配。表达式 expr LIKE pat [ESCAPE 'escape_char']
返回 1
(TRUE
) 或 0
(FALSE
)。如果 expr
或 pat
中任一个为 NULL
,结果为 NULL
。
你可以在 LIKE
中使用以下两个通配符:
%
匹配任意数量的字符,包括零个字符。_
精确匹配一个字符。
以下示例使用 utf8mb4_bin
排序规则:
SET collation_connection='utf8mb4_bin';
SHOW VARIABLES LIKE 'collation_connection';
+----------------------+-------------+
| Variable_name | Value |
+----------------------+-------------+
| collation_connection | utf8mb4_bin |
+----------------------+-------------+
SELECT NULL LIKE '%' as result;
+--------+
| result |
+--------+
| NULL |
+--------+
SELECT 'sushi!!!' LIKE 'sushi_' AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
SELECT '🍣🍺sushi🍣🍺' LIKE '%sushi%' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
SELECT '🍣🍺sushi🍣🍺' LIKE '%SUSHI%' AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
SELECT '🍣🍺sushi🍣🍺' LIKE '%🍣%' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
默认的转义字符是 \
:
SELECT 'sushi!!!' LIKE 'sushi\_' AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
SELECT 'sushi_' LIKE 'sushi\_' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
你可以使用 ESCAPE
子句指定一个不同的转义字符,例如 *
:
SELECT 'sushi_' LIKE 'sushi*_' ESCAPE '*' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
SELECT 'sushi!' LIKE 'sushi*_' ESCAPE '*' AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
你可以使用 LIKE
匹配一个数字:
SELECT 10 LIKE '1%' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
SELECT 10000 LIKE '12%' AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
你可以使用 COLLATE
显式指定一个排序规则,例如 utf8mb4_unicode_ci
:
SELECT '🍣🍺Sushi🍣🍺' COLLATE utf8mb4_unicode_ci LIKE '%SUSHI%' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
LOCATE()
LOCATE(substr, str[, pos])
函数用于返回子字符串 substr
在字符串 str
中第一次出现的位置。pos
参数是可选的,用于指定查找的起始位置。
- 如果子字符串
substr
不在字符串str
中,该函数返回0
。 - 如果任一参数为
NULL
,该函数返回NULL
。 - 该函数是多字节安全的,并且只有当至少一个参数是二进制字符串时,才执行区分大小写的查找。
以下示例使用 utf8mb4_bin
排序规则:
SET collation_connection='utf8mb4_bin';
SHOW VARIABLES LIKE 'collation_connection';
+----------------------+-------------+
| Variable_name | Value |
+----------------------+-------------+
| collation_connection | utf8mb4_bin |
+----------------------+-------------+
SELECT LOCATE('bar', 'foobarbar');
+----------------------------+
| LOCATE('bar', 'foobarbar') |
+----------------------------+
| 4 |
+----------------------------+
SELECT LOCATE('baz', 'foobarbar');
+----------------------------+
| LOCATE('baz', 'foobarbar') |
+----------------------------+
| 0 |
+----------------------------+
SELECT LOCATE('bar', 'fooBARBAR');
+----------------------------+
| LOCATE('bar', 'fooBARBAR') |
+----------------------------+
| 0 |
+----------------------------+
SELECT LOCATE('bar', 'foobarBAR', 100);
+---------------------------------+
| LOCATE('bar', 'foobarBAR', 100) |
+---------------------------------+
| 0 |
+---------------------------------+
SELECT LOCATE('bar', 'foobarbar', 5);
+-------------------------------+
| LOCATE('bar', 'foobarbar', 5) |
+-------------------------------+
| 7 |
+-------------------------------+
SELECT LOCATE('bar', NULL);
+---------------------+
| LOCATE('bar', NULL) |
+---------------------+
| NULL |
+---------------------+
SELECT LOCATE('DB', 'TiDB tidb 数据库');
+-------------------------------------+
| LOCATE('DB', 'TiDB tidb 数据库') |
+-------------------------------------+
| 3 |
+-------------------------------------+
SELECT LOCATE('DB', 'TiDB tidb 数据库', 4);
+----------------------------------------+
| LOCATE('DB', 'TiDB tidb 数据库', 4) |
+----------------------------------------+
| 0 |
+----------------------------------------+
以下示例使用 utf8mb4_unicode_ci
排序规则:
SET collation_connection='utf8mb4_unicode_ci';
SHOW VARIABLES LIKE 'collation_connection';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
+----------------------+--------------------+
SELECT LOCATE('DB', 'TiDB tidb 数据库', 4);
+----------------------------------------+
| LOCATE('DB', 'TiDB tidb 数据库', 4) |
+----------------------------------------+
| 8 |
+----------------------------------------+
SELECT LOCATE('🍺', '🍣🍣🍣🍺🍺');
+----------------------------------------+
| LOCATE('🍺', '🍣🍣🍣🍺🍺') |
+----------------------------------------+
| 1 |
+----------------------------------------+
以下多字节和二进制字符串示例使用 utf8mb4_bin
排序规则:
SET collation_connection='utf8mb4_bin';
SHOW VARIABLES LIKE 'collation_connection';
+----------------------+-------------+
| Variable_name | Value |
+----------------------+-------------+
| collation_connection | utf8mb4_bin |
+----------------------+-------------+
SELECT LOCATE('🍺', '🍣🍣🍣🍺🍺');
+----------------------------------------+
| LOCATE('🍺', '🍣🍣🍣🍺🍺') |
+----------------------------------------+
| 4 |
+----------------------------------------+
SELECT LOCATE('b', _binary'aBcde');
+-----------------------------+
| LOCATE('b', _binary'aBcde') |
+-----------------------------+
| 0 |
+-----------------------------+
SELECT LOCATE('B', _binary'aBcde');
+-----------------------------+
| LOCATE('B', _binary'aBcde') |
+-----------------------------+
| 2 |
+-----------------------------+
SELECT LOCATE(_binary'b', 'aBcde');
+-----------------------------+
| LOCATE(_binary'b', 'aBcde') |
+-----------------------------+
| 0 |
+-----------------------------+
SELECT LOCATE(_binary'B', 'aBcde');
+-----------------------------+
| LOCATE(_binary'B', 'aBcde') |
+-----------------------------+
| 2 |
+-----------------------------+
LOWER()
LOWER(str)
函数用于将输入的参数 str
中的所有字符转换为小写。该参数可以为字符串或数字。
- 如果输入参数为字符串,该函数返回字符串的小写形式。
- 如果输入参数为数字,该函数将会去掉该数字中的前导零。
- 如果输入参数为
NULL
,该函数返回NULL
。
示例:
SELECT LOWER("TiDB");
+---------------+
| LOWER("TiDB") |
+---------------+
| tidb |
+---------------+
SELECT LOWER(-012);
+-------------+
| LOWER(-012) |
+-------------+
| -12 |
+-------------+
LPAD()
返回字符串参数,左侧添加指定字符串
LTRIM()
去掉前缀空格
MAKE_SET()
返回一组用逗号分隔的字符串,这些字符串的位数与给定的 bits 参数对应
MID()
返回一个以指定位置开始的子字符串
NOT LIKE
否定简单模式匹配
NOT REGEXP
REGEXP
的否定形式
OCT()
返回一个数值的八进制表示,形式为字符串
OCTET_LENGTH()
与 LENGTH()
功能相同
ORD()
返回该参数最左侧字符的字符编码
POSITION()
与 LOCATE()
功能相同
QUOTE()
使参数逃逸,为了在 SQL 语句中使用
REGEXP
使用正则表达式匹配模式
REGEXP_INSTR()
返回满足正则的子字符串的第一个索引位置(与 MySQL 不完全兼容,具体请参考正则函数与 MySQL 的兼容性)
REGEXP_LIKE()
判断字符串是否满足正则表达式(与 MySQL 不完全兼容,具体请参考正则函数与 MySQL 的兼容性)
REGEXP_REPLACE()
替换满足正则表达式的子字符串(与 MySQL 不完全兼容,具体请参考正则函数与 MySQL 的兼容性)
REGEXP_SUBSTR()
返回满足正则表达式的子字符串(与 MySQL 不完全兼容,具体请参考正则函数与 MySQL 的兼容性)
REPEAT()
以指定次数重复一个字符串
REPLACE()
替换所有出现的指定字符串
REVERSE()
反转字符串里的所有字符
RIGHT()
返回指定数量的最右侧的字符
RLIKE
与 REGEXP
功能相同
RPAD()
以指定次数添加字符串
RTRIM()
去掉后缀空格
SPACE()
返回指定数量的空格,形式为字符串
STRCMP()
比较两个字符串
SUBSTR()
返回指定的子字符串
SUBSTRING()
返回指定的子字符串
SUBSTRING_INDEX()
SUBSTRING_INDEX()
函数用于按照指定的分隔符和次数从字符串中提取子字符串。该函数在处理以特定分隔符分隔的数据时特别有用,例如解析 CSV 数据或处理日志文件。
语法:
SUBSTRING_INDEX(str, delim, count)
str
:要处理的字符串。delim
:指定字符串中的分隔符,大小写敏感。count
:指定分隔符出现的次数。- 如果
count
为正数,该函数返回从字符串左边开始的第count
个分隔符之前的子字符串。 - 如果
count
为负数,该函数返回从字符串右边开始的第count
个分隔符之后的子字符串。 - 如果
count
为0
,该函数返回一个空字符串。
- 如果
查询示例 1:
SELECT SUBSTRING_INDEX('www.tidbcloud.com', '.', 2);
返回结果 1:
+-----------------------------------------+
| SUBSTRING_INDEX('www.tidbcloud.com', '.', 2) |
+-----------------------------------------+
| www.tidbcloud |
+-----------------------------------------+
查询示例 2:
SELECT SUBSTRING_INDEX('www.tidbcloud.com', '.', -1);
返回结果 2:
+------------------------------------------+
| SUBSTRING_INDEX('www.tidbcloud.com', '.', -1) |
+------------------------------------------+
| com |
+------------------------------------------+
TO_BASE64()
TO_BASE64()
函数用于将输入的参数转换为 base-64 编码形式的字符串,并按照当前连接的字符集和排序规则返回结果。base-64 编码的字符串可以使用 FROM_BASE64()
函数进行解码。
语法:
TO_BASE64(str)
- 如果输入参数不是字符串,该函数会将其转换为字符串后再进行 base-64 编码。
- 如果输入参数为
NULL
,该函数返回NULL
。
查询示例 1:
SELECT TO_BASE64('abc');
返回结果 1:
+------------------+
| TO_BASE64('abc') |
+------------------+
| YWJj |
+------------------+
查询示例 2:
SELECT TO_BASE64(6);
返回结果 2:
+--------------+
| TO_BASE64(6) |
+--------------+
| Ng== |
+--------------+
TRANSLATE()
将字符串中出现的所有指定字符替换为其它字符。这个函数不会像 Oracle 一样将空字符串视为NULL
TRIM()
去掉前缀和后缀空格
UCASE()
UCASE()
函数将字符串转换为大写字母,此函数等价于 UPPER()
函数。
查询示例:
SELECT UCASE('bigdata') AS result_upper, UCASE(null) AS result_null;
返回结果:
+--------------+-------------+
| result_upper | result_null |
+--------------+-------------+
| BIGDATA | NULL |
+--------------+-------------+
UNHEX()
UNHEX()
函数执行 HEX()
函数的逆运算,将参数中的每对字符视为十六进制数字,并将其转换为该数字表示的字符,返回值为二进制字符串。
查询示例:
SELECT UNHEX('54694442');
返回结果:
+--------------------------------------+
| UNHEX('54694442') |
+--------------------------------------+
| 0x54694442 |
+--------------------------------------+
UPPER()
UPPER()
函数将字符串转换为大写字母,此函数等价于 UCASE()
函数。
查询示例:
SELECT UPPER('bigdata') AS result_upper, UPPER(null) AS result_null;
返回结果:
+--------------+-------------+
| result_upper | result_null |
+--------------+-------------+
| BIGDATA | NULL |
+--------------+-------------+
WEIGHT_STRING()
WEIGHT_STRING()
函数返回字符串的权重(二进制字符),主要用于多字符集场景下的排序和比较操作。如果参数为 NULL
,则返回 NULL
。语法示例如下:
WEIGHT_STRING(str [AS {CHAR|BINARY}(N)])
str
:字符串表达式。如果是非二进制字符串,例如 CHAR、VARCHAR 或 TEXT 值,则返回值包含该字符串的排序规则权重;如果是二进制字符串,例如 BINARY、VARBINARY 或 BLOB 值,则返回值与输入相同。AS {CHAR|BINARY}(N)
:可选参数,用于指定输出结果的类型和长度。CHAR
表示字符数据类型,而BINARY
表示二进制数据类型;N
指定输出的长度,取值为大于等于 1 的整数。
查询示例:
SET NAMES 'utf8mb4';
SELECT HEX(WEIGHT_STRING('ab' AS CHAR(3))) AS char_result, HEX(WEIGHT_STRING('ab' AS BINARY(3))) AS binary_result;
返回结果:
+-------------+---------------+
| char_result | binary_result |
+-------------+---------------+
| 6162 | 616200 |
+-------------+---------------+
不支持的函数
LOAD_FILE()
MATCH()
SOUNDEX()
正则函数与 MySQL 的兼容性
本节介绍 TiDB 中正则函数 REGEXP_INSTR()
、REGEXP_LIKE()
、REGEXP_REPLACE()
、REGEXP_SUBSTR()
与 MySQL 的兼容情况。
语法兼容性
MySQL 的实现使用的是 ICU (International Components for Unicode) 库,TiDB 的实现使用的是 RE2 库,两个库之间的语法差异可以查阅 ICU 文档和 RE2 文档。
匹配模式 match_type
兼容性
TiDB 与 MySQL 在 match_type
上的差异:
TiDB 中
match_type
可选值为:"c"
、"i"
、"m"
、"s"
。MySQL 中match_type
可选值为:"c"
、"i"
、"m"
、"n"
、"u"
。TiDB 中
"s"
对应 MySQL 中的"n"
,即.
字符匹配行结束符。例如:MySQL 中
SELECT REGEXP_LIKE(a, b, "n") FROM t1;
在 TiDB 中需要修改为SELECT REGEXP_LIKE(a, b, "s") FROM t1;
。TiDB 不支持
match_type
为"u"
。
数据类型兼容性
TiDB 与 MySQL 在二进制字符串 (binary string) 数据类型上的差异:
- MySQL 8.0.22 及以上版本中正则函数不支持二进制字符串,具体信息可查看 MySQL 文档。但在实际使用过程中,如果所有参数或者返回值的数据类型都是二进制字符串,则正则函数可以正常使用,否则报错。
- TiDB 目前完全禁止使用二进制字符串,无论什么情况都会报错。
其它兼容性
TiDB 与 MySQL 在替换空字符串上的差异:
下面以 REGEXP_REPLACE("", "^$", "123")
为例:
- MySQL 不会对空串进行替换,其结果为
""
。 - TiDB 会对空串进行替换,其结果为
"123"
。