DB version： 5.7.25-log
目录：
一、字符串常规函数
二、字符串比较函数
三、正则表达式
四、字符集和排序规则函数
一、字符串常规函数
ASCII(str)返回字符串中第一个字符的ascii码，如果字符串为空，则返回0，为NULL，则返回NULL。只适合单字节的字符，不适用于多字节，如汉字。
多字节用 ORD(str)。
mysql> select ascii('123');
+--------------+
| ascii('123') |
+--------------+
| 49 |
+--------------+
mysql> select ascii(1);
+----------+
| ascii(1) |
+----------+
| 49 |
+----------+
mysql> select ascii('a');
+------------+
| ascii('a') |
+------------+
| 97 |
+------------+
mysql> select ascii('abc');
+--------------+
| ascii('abc') |
+--------------+
| 97 |
+--------------+
mysql> select ascii('');
+-----------+
| ascii('') |
+-----------+
| 0 |
+-----------+
mysql> select ascii(NULL);
+-------------+
| ascii(NULL) |
+-------------+
| NULL |
+-------------+
ORD(str) 如果字符串str的第一个字符是多字节字符，则返回该字符的代码，该代码使用以下公式根据其组成字节的数值计算得出：
(1st byte code) + (2nd byte code * 256) + (3rd byte code * 256^2) ...
mysql> select ord('我');
+-----------+
| ord('我') |
+-----------+
| 15108241 |
+-----------+
mysql> select ord('我是中国人');
+-------------------+
| ord('我是中国人') |
+-------------------+
| 15108241 |
+-------------------+
计算公式：
mysql> SELECT hex(CONVERT( '我' USING utf8 ));
+---------------------------------+
| hex(CONVERT( '我' USING utf8 )) |
+---------------------------------+
| E68891 |
+---------------------------------+
将三个字节对应的编码转为10进制再乘以256的次方。
第一个字节 91 ：(9*16+1)
第二个字节 88 ：(8*16+8)*256
第三个字节 E6 ：(14*16+6)*256*256
mysql> select (9*16+1)+(8*16+8)*256+(14*16+6)*256*256 as res;
+----------+
| res |
+----------+
| 15108241 |
+----------+
如果字符串str的第一个字符是单字节字符，则返回与ASCII(str) 相同的值。
mysql> select ord('a我是中国人');
+--------------------+
| ord('a我是中国人') |
+--------------------+
| 97 |
+--------------------+
BIN(N) 返回N的二进制值的字符串表示形式，其中N是一个BIGINT数字。最大可为2^64。
- 如果N为NULL或''，则返回NULL；
- 如果N为非数字，则返回0。
mysql> select bin(1234);
+-------------+
| bin(1234) |
+-------------+
| 10011010010 |
+-------------+
mysql> select bin(NULL);
+-----------+
| bin(NULL) |
+-----------+
| NULL |
+-----------+
mysql> select bin('');
+---------+
| bin('') |
+---------+
| NULL |
+---------+
mysql> select bin('w');
+----------+
| bin('w') |
+----------+
| 0 |
+----------+
mysql> select bin('我');
+-----------+
| bin('我') |
+-----------+
| 0 |
+-----------+
BIT_LENGTH(str) 以位为单位返回字符串str的长度。
gbk 编码下一个汉字占两个字节
mysql> select bit_length(CONVERT( '我' USING gbk )) ;
+----------------------------------------+
| bit_length(CONVERT( '我' USING gbk )) |
+----------------------------------------+
| 16 |
+----------------------------------------+
utf8 编码下一个汉字占三个字节，24位
mysql> select bit_length(CONVERT( '我' USING utf8mb4 ));
+--------------------------------------------+
| bit_length(CONVERT( '我' USING utf8mb4 )) |
+--------------------------------------------+
| 24 |
+--------------------------------------------+
emoji表情只能用 utf8mb4保存，且占四个字节，32位
mysql> select bit_length(CONVERT( '😄' USING utf8mb4 )) ;
+------------------------------------------+
| bit_length(CONVERT( '?' USING utf8mb4 )) |
+------------------------------------------+
| 32 |
+------------------------------------------+
CHAR(N) 返回N整数部分在ascii码表对应的字符（N为小数，则舍弃小数位）
1.NULL被跳过，不返回任何东西 ；
2.ascii码只有256（0-255）个，超过255的数字将被转换成多个字节结果；
3.默认返回二进制字符集，可用using语句进行指定字符集。
mysql> SELECT CHAR(77,121,null,83,81,'76');
+------------------------------+
| CHAR(77,121,null,83,81,'76') |
+------------------------------+
| MySQL |
+------------------------------+
mysql> SELECT CHAR(81,81.12,'81.3');
+-----------------------+
| CHAR(81,81.12,'81.3') |
+-----------------------+
| QQQ |
+-----------------------+
超过255被转为多字节
CHAR(256) 等价于 CHAR(1,0)，CHAR(65535)等价于CHAR(255,255)，以此类推
mysql> SELECT HEX(CHAR(255)),HEX(CHAR(256)),HEX(CHAR(1,0)),HEX(CHAR(65535)),HEX(CHAR(255,255)),HEX(CHAR(65536)),HEX(CHAR(1,0,0));
+----------------+----------------+----------------+------------------+--------------------+------------------+------------------+
| HEX(CHAR(255)) | HEX(CHAR(256)) | HEX(CHAR(1,0)) | HEX(CHAR(65535)) | HEX(CHAR(255,255)) | HEX(CHAR(65536)) | HEX(CHAR(1,0,0)) |
+----------------+----------------+----------------+------------------+--------------------+------------------+------------------+
| FF | 0100 | 0100 | FFFF | FFFF | 010000 | 010000 |
+----------------+----------------+----------------+------------------+--------------------+------------------+------------------+
如果给出了USING并且结果字符串对于给定的字符集是非法的，则会发出警告。
如果启用了严格的SQL模式，则CHAR（）的结果将为NULL。
mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));
+---------------------+--------------------------------+
| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |
+---------------------+--------------------------------+
| binary | utf8 |
+---------------------+--------------------------------+
CHARACTER_LENGTH(str) 是CHAR_LENGTH()的同义词，见下
CHAR_LENGTH(str)返回字符串str的长度，以字符为单位。不论多字节还是单字节字符均算作单个字符。
mysql> SELECT CHAR_LENGTH('MySQL'), CHAR_LENGTH('我是中国人');
+----------------------+---------------------------+
| CHAR_LENGTH('MySQL') | CHAR_LENGTH('我是中国人') |
+----------------------+---------------------------+
| 5 | 5 |
+----------------------+---------------------------+
CONCAT(str1,str2,...) 返回由一个或多个参数连接产生的字符串。
1.如果所有参数均为非二进制字符串，则结果为非二进制字符串。
2.如果参数包含任何二进制字符串，则结果为二进制字符串。
3.数字参数将转换为其等效的非二进制字符串形式。
4.连接的字符串中存在NULL，则返回NULL
mysql> SELECT CONCAT('My', 'S', 'QL'),CHARSET(CONCAT('My', 'S', 'QL')), CONCAT('My', 'S',NULL, 'QL'),CONCAT(X'65','f',12),CHARSET(CONCAT(X'65','f',12));
+-------------------------+----------------------------------+------------------------------+----------------------+-------------------------------+
| CONCAT('My', 'S', 'QL') | CHARSET(CONCAT('My', 'S', 'QL')) | CONCAT('My', 'S',NULL, 'QL') | CONCAT(X'65','f',12) | CHARSET(CONCAT(X'65','f',12)) |
+-------------------------+----------------------------------+------------------------------+----------------------+-------------------------------+
| MySQL | utf8mb4 | NULL | ef12 | binary |
+-------------------------+----------------------------------+------------------------------+----------------------+-------------------------------+
对于带引号的字符串，可以通过将字符串彼此相邻放置来实现串联
mysql> SELECT 'My' 'S' 'QL' as res ;
+-------+
| res |
+-------+
| MySQL |
+-------+
CONCAT_WS(separator,str1,str2,...) 代表用分隔符连接，是CONCAT()的一种特殊形式。
1.第一个参数是分隔符, 分隔符被添加到要连接的字符串之间；
2.分隔符可以是字符串，其余参数也可以；
3.如果分隔符为NULL，则结果为NULL；
4.不会跳过空字符串， 但是，它会跳过分隔符参数之后的所有NULL值。
mysql> SELECT CONCAT_WS(',','First name','Last Name'),CONCAT_WS(NULL,'First name','Last Name'),CONCAT_WS(',','First name','','Last Name'), CONCAT_WS(',','First name',NULL,'Last Name');
+-----------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+
| CONCAT_WS(',','First name','Last Name') | CONCAT_WS(NULL,'First name','Last Name') | CONCAT_WS(',','First name','','Last Name') | CONCAT_WS(',','First name',NULL,'Last Name') |
+-----------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+
| First name,Last Name | NULL | First name,,Last Name | First name,Last Name |
+-----------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+
EXPORT_SET(bits,on,off[,separator[,number_of_bits]]) 这函数鄙人第一次用。Talk is cheap，show you the code 😄
mysql> SELECT EXPORT_SET(111,'1对应我','0对应我','|',10);
+---------------------------------------------------------------------------------+
| EXPORT_SET(111,'1对应我','0对应我','|',10) |
+---------------------------------------------------------------------------------+
| 1对应我|1对应我|1对应我|1对应我|0对应我|1对应我|1对应我|0对应我|0对应我|0对应我 |
+---------------------------------------------------------------------------------+
转换步骤:
1.将 bits 转换为二进制，这里是111，二进制为 1101111 ；
2.对 1101111 从低到高位排序得到 1111011 ；
3.number_of_bits=10，表示总位数为10，不足时右侧补0，得到 1111011000 ；
4.然后将 1 替换成 '1对应我' ，0 替换成 '0对应我' ，得到最终结果。
ELT(N,str1,str2,str3,...) 返回字符串列表的第N个元素：如果N = 1，则返回str1；如果N = 2，则返回str2，依此类推。 如果N小于1或大于参数个数，则返回NULL。 ELT（）是FIELD（）函数的补充。
mysql> SELECT ELT(1, 'Aa', 'Bb', 'Cc', 'Dd'),ELT(3, 'Aa', 'Bb', 'Cc', 'Dd'),ELT(7, 'Aa', 'Bb', 'Cc', 'Dd') ;
+--------------------------------+--------------------------------+--------------------------------+
| ELT(1, 'Aa', 'Bb', 'Cc', 'Dd') | ELT(3, 'Aa', 'Bb', 'Cc', 'Dd') | ELT(7, 'Aa', 'Bb', 'Cc', 'Dd') |
+--------------------------------+--------------------------------+--------------------------------+
| Aa | Cc | NULL |
+--------------------------------+--------------------------------+--------------------------------+
FIELD(str,str1,str2,str3,...) 返回str在字符串 str1,str2,str3,... 中所在的位置，如找不到，则返回0。若str为NULL，则返回0。FIELD（）亦是ELT（）的补充。
mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc'),FIELD('Dd', 'Aa', 'Bb', 'Cc'),FIELD(NULL, 'Aa', 'Bb', 'Cc');
+-------------------------------+-------------------------------+-------------------------------+
| FIELD('Bb', 'Aa', 'Bb', 'Cc') | FIELD('Dd', 'Aa', 'Bb', 'Cc') | FIELD(NULL, 'Aa', 'Bb', 'Cc') |
+-------------------------------+-------------------------------+-------------------------------+
| 2 | 0 | 0 |
+-------------------------------+-------------------------------+-------------------------------+
FIND_IN_SET(str,strlist) 返回str在字符串strlist中的位置。如果str是字符串，strlist是一个集合，则FIND_IN_SET（）将会被优化成位运算。
mysql> SELECT FIND_IN_SET('b','a,b,c,d'),FIND_IN_SET('e','a,b,c,d'),FIND_IN_SET(NULL,'a,b,c,d'),FIND_IN_SET('b','a,NULL,b,c,d'),FIND_IN_SET('b,','a,b,c,d'),FIND_IN_SET('中','我,是,中,国,人');
+----------------------------+----------------------------+-----------------------------+---------------------------------+-----------------------------+------------------------------------+
| FIND_IN_SET('b','a,b,c,d') | FIND_IN_SET('e','a,b,c,d') | FIND_IN_SET(NULL,'a,b,c,d') | FIND_IN_SET('b','a,NULL,b,c,d') | FIND_IN_SET('b,','a,b,c,d') | FIND_IN_SET('中','我,是,中,国,人') |
+----------------------------+----------------------------+-----------------------------+---------------------------------+-----------------------------+------------------------------------+
| 2 | 0 | NULL | 3 | 0 | 3 |
+----------------------------+----------------------------+-----------------------------+---------------------------------+-----------------------------+------------------------------------+
FORMAT(X,D[,locale]) 将数字X格式化为类似于 '#,###,###.##' 的格式，四舍五入到D小数位，然后将结果作为字符串返回。 如果D为0，则结果没有小数点或小数部分。可选的第三个参数允许指定语言环境，以用于结果数字的小数点，千位分隔符以及分隔符之间的分组。 允许的语言环境值与lc_time_names系统变量的合法值相同，如果未指定语言环境，则默认值为“ en_US”。
mysql> SELECT FORMAT(12332.123456, 4), FORMAT(12332.12,4), FORMAT(12332.123456, 0), FORMAT(12332.1, 2,'en_US'), FORMAT(12332.123456, 2,'de_DE');
+-------------------------+--------------------+-------------------------+----------------------------+---------------------------------+
| FORMAT(12332.123456, 4) | FORMAT(12332.12,4) | FORMAT(12332.123456, 0) | FORMAT(12332.1, 2,'en_US') | FORMAT(12332.123456, 2,'de_DE') |
+-------------------------+--------------------+-------------------------+----------------------------+---------------------------------+
| 12,332.1235 | 12,332.1200 | 12,332 | 12,332.10 | 12.332,12 |
+-------------------------+--------------------+-------------------------+----------------------------+---------------------------------+
FROM_BASE64(str) 解码由函数TO_BASE64()编码得到的字符串。如果str为NULL或不是有效的base-64编码则返回NULL。
mysql> SELECT TO_BASE64('我'),FROM_BASE64(TO_BASE64('我')),FROM_BASE64(NULL);
+-----------------+------------------------------+-------------------+
| TO_BASE64('我') | FROM_BASE64(TO_BASE64('我')) | FROM_BASE64(NULL) |
+-----------------+------------------------------+-------------------+
| 5oiR | 我 | NULL |
+-----------------+------------------------------+-------------------+
TO_BASE64(str) 将字符串以base-64编码后返回。如果参数不是字符串，则在进行转换之前将其转换为字符串。解码时用FROM_BASE64(str)。
1.The encoding for alphabet value 62 is '+'（未理解）
2.The encoding for alphabet value 63 is '/'（未理解）
3.输入数据的每3个字节使用4个字符进行编码。 如果最后一组不完整，则用'='字符填充，长度为4。
4.在编码输出的每76个字符之后添加一个换行符，以将长输出分成多行。
5.解码识别并忽略换行符，回车符，制表符和空格。
mysql> SELECT TO_BASE64('ab'),TO_BASE64('abc'),TO_BASE64(NULL),FROM_BASE64('YW Jj') ;
+-----------------+------------------+-----------------+----------------------+
| TO_BASE64('ab') | TO_BASE64('abc') | TO_BASE64(NULL) | FROM_BASE64('YW Jj') |
+-----------------+------------------+-----------------+----------------------+
| YWI= | YWJj | NULL | abc |
+-----------------+------------------+-----------------+----------------------+
输出超过76个字符则换行
mysql> SELECT TO_BASE64('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz');
+----------------------------------------------------------------------------------------------------------------------------------------------+
| TO_BASE64('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz') |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| YWJjZGVmZ2hpamtsbW5vcHFyc3R1dnd4eXphYmNkZWZnaGlqa2xtbm9wcXJzdHV2d3h5emFiY2Rl
ZmdoaWprbG1ub3BxcnN0dXZ3eHl6YWJjZGVmZ2hpamtsbW5vcHFyc3R1dnd4eXo= |
+----------------------------------------------------------------------------------------------------------------------------------------------+
HEX(str), HEX(N)返回没有字符或数字的16进制编码。
mysql> SELECT HEX('我'),HEX('ab'),HEX(255),UNHEX('E68891');
+-----------+-----------+----------+-----------------+
| HEX('我') | HEX('ab') | HEX(255) | UNHEX('E68891') |
+-----------+-----------+----------+-----------------+
| E68891 | 6162 | FF | 我 |
+-----------+-----------+----------+-----------------+
UNHEX(str) 对于字符串参数str，UNHEX（str）将参数中的每对字符解释为十六进制数字，并将其转换为该数字表示的字节。 返回值是一个二进制字符串。
1.参数字符串中的字符必须为合法的十六进制数字：“ 0” ..“ 9”，“ A” ..“ F”，“ a” ..“ f”。 如果参数包含任何非十六进制数字，则结果为NULL；
2.如果UNHEX（）的参数是BINARY列，则可能会返回NULL；
3.对于数字参数N，HEX（N）的解码不是UNHEX（），改用CONV（HEX（N），16,10）。
mysql> SELECT UNHEX('62'),UNHEX('E68891'),HEX(255),UNHEX('FF'),CONV('FF',16,10);
+-------------+-----------------+----------+-------------+------------------+
| UNHEX('62') | UNHEX('E68891') | HEX(255) | UNHEX('FF') | CONV('FF',16,10) |
+-------------+-----------------+----------+-------------+------------------+
| b | 我 | FF | � | 255 |
+-------------+-----------------+----------+-------------+------------------+
INSERT(str,pos,len,newstr) 返回字符串str，子字符串从pos位置开始，并且len个字符由字符串newstr取代。
1.如果pos不在字符串的长度内，则返回原始字符串。
2.如果len不在字符串其余部分的长度内，则从位置pos替换字符串的其余部分。
3.如果任何参数为NULL，则返回NULL。
mysql> SELECT INSERT('who are you?', 1, 3, 'where'),INSERT('who are you?', -1, 3, 'where'),INSERT('who are you?', 1, 100, 'where'),INSERT('我是中国人', 3, 3, '渣渣辉');
+---------------------------------------+----------------------------------------+-----------------------------------------+--------------------------------------+
| INSERT('who are you?', 1, 3, 'where') | INSERT('who are you?', -1, 3, 'where') | INSERT('who are you?', 1, 100, 'where') | INSERT('我是中国人', 3, 3, '渣渣辉') |
+---------------------------------------+----------------------------------------+-----------------------------------------+--------------------------------------+
| where are you? | who are you? | where | 我是渣渣辉 |
+---------------------------------------+----------------------------------------+-----------------------------------------+--------------------------------------+
INSTR(str,substr)返回substr在str中第一次出现的位置，如果str或substr为NULL，则返回NULL。和两个参数的LOCATE(substr,str)相同，只是参数顺序相反。见下
mysql> SELECT INSTR('goodboy','boy'),INSTR('goodboy','o'),INSTR('goodboy','bye'),INSTR('goodboy',NULL);
+------------------------+----------------------+------------------------+-----------------------+
| INSTR('goodboy','boy') | INSTR('goodboy','o') | INSTR('goodboy','bye') | INSTR('goodboy',NULL) |
+------------------------+----------------------+------------------------+-----------------------+
| 5 | 2 | 0 | NULL |
+------------------------+----------------------+------------------------+-----------------------+
POSITION(substr IN str) 为LOCATE(substr,str)的同义词，见下
LOCATE(substr,str), LOCATE(substr,str,pos)第一个表达式返回substr在str中第一次出现的位置，与INSTR()一样。第二个表达式返回从str的pos位置往后第一次出现substr的位置。
mysql> SELECT LOCATE('boy','goodboy'),LOCATE('o','goodboy',5),LOCATE(NULL,'goodboy');
+-------------------------+-------------------------+------------------------+
| LOCATE('boy','goodboy') | LOCATE('o','goodboy',5) | LOCATE(NULL,'goodboy') |
+-------------------------+-------------------------+------------------------+
| 5 | 6 | NULL |
+-------------------------+-------------------------+------------------------+
LCASE(str) 为函数LOWER(str)的同义词，见下
LOWER(str) 根据当前字符集，返回字符串str的小写形式，默认字符集latin1。对应将str转为大写的函数UPPER（str）。
1.当应用于二进制字符串（BINARY，VARBINARY，BLOB）时，LOWER（）和UPPER（）无效。需要先将str中的数据字符集转换为非二进制。
2.早期版本中，视图里的LOWER（）会被改写成LCASE（），5.7中，LOWER（）将不会被改写。
mysql> SELECT LOWER('Good Boy'),LOWER(BINARY 'Good Boy'),LOWER(CONVERT(BINARY 'Good Boy' using utf8));
+-------------------+--------------------------+--------------------------------------------+
| LOWER('Good Boy') | LOWER(BINARY 'Good Boy') | LOWER(CONVERT(BINARY 'Good Boy' using utf8)) |
+-------------------+--------------------------+--------------------------------------------+
| good boy | Good Boy | good boy |
+-------------------+--------------------------+--------------------------------------------+
UCASE(str)为函数UPPER(str)的同义词，见下
UPPER(str) 根据当前字符集，返回字符串str的大写形式，默认字符集latin1。对应将str转为小写的函数LOWER（str）。
mysql> SELECT UPPER('Good Boy'),UPPER(BINARY 'Good Boy'),UPPER(CONVERT(BINARY 'Good Boy' using utf8));
+-------------------+--------------------------+----------------------------------------------+
| UPPER('Good Boy') | UPPER(BINARY 'Good Boy') | UPPER(CONVERT(BINARY 'Good Boy' using utf8)) |
+-------------------+--------------------------+----------------------------------------------+
| GOOD BOY | Good Boy | GOOD BOY |
+-------------------+--------------------------+----------------------------------------------+
LEFT(str,len)返回字符串str中最左边的len个字符；如果任何参数为NULL，则返回NULL。相对应的函数RIGHT(str,len)。见下
mysql> SELECT LEFT('Good Boy',4);
+------------------+
| LEFT('Good Boy',4) |
+------------------+
| Good |
+------------------+
RIGHT(str,len)返回字符串str中最右边的len个字符；
mysql> SELECT RIGHT('Good Boy',5);
+-------------------+
| RIGHT('Good Boy',5) |
+-------------------+
| d Boy |
+-------------------+
OCTET_LENGTH(str) 为LENGTH(str)的同义词，见下
LENGTH(str) 以字节为单位返回字符串str的长度。 多字节字符计为多个字节。 这意味着对于包含5个2字节字符的字符串，LENGTH（）返回10，而CHAR_LENGTH（）返回5。
mysql> SELECT LENGTH('text'),CHARSET('左轮'),LENGTH('左轮');
+----------------+-------------------+------------------+
| LENGTH('text') | CHARSET('左轮') | LENGTH('左轮') |
+----------------+-------------------+------------------+
| 4 | utf8 | 6 |
+----------------+-------------------+------------------+
LOAD_FILE(file_name) 读取文件内容并以字符串形式返回。
1.用户需要有file权限；
2.文件不要超过 max_allowed_packet大小；
3.参数secure_file_priv需要为非NULL，且指定到一个目录路径；
4.如果文件不可读或者不存在，则返回NULL。
mysql> SELECT LOAD_FILE('D:\\A.TXT'),LOAD_FILE('D:\\B.TXT');
+------------------------+------------------------+
| LOAD_FILE('D:\\A.TXT') | LOAD_FILE('D:\\B.TXT') |
+------------------------+------------------------+
| HAHAHAA | NULL |
+------------------------+------------------------+
可以用文件里的数据更新表中的字段。
mysql> UPDATE T SET T_NAME=LOAD_FILE('D:\\A.TXT') WHERE T_ID = '01';
LPAD(str,len,padstr) 返回字符串str
1.如果len > str 的长度，则用padstr左填充str至len长度；
2.如果len < str 的长度，则返回len长度的字符；
3.相对应函数RPAD（str）。
mysql> SELECT LPAD('GoodBoy',12,'$'),LPAD('GoodBoy',4,'$');
+------------------------+-----------------------+
| LPAD('GoodBoy',12,'$') | LPAD('GoodBoy',4,'$') |
+------------------------+-----------------------+
| $$$$$GoodBoy | Good |
+------------------------+-----------------------+
RPAD(str,len,padstr) 返回字符串str，用法同LPAD(str,len,padstr)
mysql> SELECT RPAD('GoodBoy',12,'$'),RPAD('GoodBoy',4,'$');
+------------------------+-----------------------+
| RPAD('GoodBoy',12,'$') | RPAD('GoodBoy',4,'$') |
+------------------------+-----------------------+
| GoodBoy$$$$$ | Good |
+------------------------+-----------------------+
LTRIM(str)返回已删除str前面空格后的字符串
RTRIM(str) 返回已删除str后面空格后的字符串
TRIM(str)返回已删除str前面及后面空格后的字符串
mysql> SELECT LTRIM(' GoodBoy ') LTR,length(LTRIM(' GoodBoy ')) Len_LTR,RTRIM(' GoodBoy ') RTR,length(RTRIM(' GoodBoy ')) Len_RTR,TRIM(' GoodBoy ') TR,length(TRIM(' GoodBoy ')) Len_TR ;
+-----------+----------+-----------+----------+----------+---------+
| LTR | Len_LTR | RTR | Len_RTR | TR | Len_TR |
+-----------+----------+-----------+----------+----------+---------+
| GoodBoy | 9 | GoodBoy | 9 | GoodBoy | 7 |
+-----------+----------+-----------+----------+----------+---------+
MAKE_SET(bits,str1,str2,...) Talk is cheap，show you the code 😄
mysql> SELECT MAKE_SET(18,'a','b','c','d','e','f');
+---------------------------+
| MAKE_SET(18,'a','b','c','d','e','f') |
+---------------------------+
| b,e |
+---------------------------+
转换步骤：
1.首先 'a','b','c','d','e','f' 分别对应 2^0,2^1,2^2 ... ，即1，2，4，8，16...，是从低位到高位的顺序。
2.18 = 2 + 16，对应 'b' 和 'e'，返回即可。
3.或者可以这么理解，将18转为2进制，得到 10010 ，从低位到高位则为 01001（颠倒下顺序即可），对应到'a','b','c','d','e','f'上。
'a' 'b' 'c' 'd' 'e' 'f'
0 1 0 0 1 0
即'b' 'e'。
mysql> SELECT MAKE_SET(18|6,'a','b','c','d','e','f');
+-----------------------------+
| MAKE_SET(18|6,'a','b','c','d','e','f') |
+-----------------------------+
| b,c,e |
+-----------------------------+
转换步骤：
此处只是将 18|6 进行了位或 运算
1 0 0 1 0
0 0 1 1 0
---------
1 0 1 1 0
结果 1 0 1 1 0 ，从低位到高位则为 01101（颠倒下顺序即可），对应到'a','b','c','d','e','f'上。
'a' 'b' 'c' 'd' 'e' 'f'
0 1 1 0 1 0
即 b c e
MATCH()完整表达式 MATCH (col1,col2,...) AGAINST (expr [search_modifier])，全文索引字符搜索，详细介绍见官档：https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html
mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> ) ENGINE=InnoDB;
mysql> INSERT INTO articles (title,body) VALUES
-> ('MySQL Tutorial','This database tutorial ...'),
-> ("How To Use MySQL",'After you went through a ...'),
-> ('Optimizing Your Database','In this database tutorial ...'),
-> ('MySQL vs. YourSQL','When comparing databases ...'),
-> ('MySQL Security','When configured properly, MySQL ...'),
-> ('Database, Database, Database','database database database'),
-> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
-> ('MySQL Full-Text Indexes', 'MySQL fulltext indexes use a ..');
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body)
-> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+------------------------------+-------------------------------+
| id | title | body |
+----+------------------------------+-------------------------------+
| 6 | Database, Database, Database | database database database |
| 3 | Optimizing Your Database | In this database tutorial ... |
| 1 | MySQL Tutorial | This database tutorial ... |
+----+------------------------------+-------------------------------+
MID(str,pos,len)为SUBSTRING(str,pos,len)的同义词。见下
SUBSTR(str,pos,len)为SUBSTRING(str,pos,len)的同义词。见下
SUBSTRING(str,pos), SUBSTRING(str FROM pos)
SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
1.SUBSTRING()有以上几种形式；
2.FROM pos 的写法是标准SQL语法；
3.pos也可以是负值，即从尾往前定位pos。
4.pos为0或len小于1，则返回空字符串。
mysql> SELECT SUBSTRING('Quadratically',5),SUBSTRING('Quadratically' FROM 5),SUBSTRING('Quadratically',5,3),SUBSTRING('Quadratically',-5),SUBSTRING('Quadratically',-5,4);
+------------------------------+-----------------------------------+--------------------------------+-------------------------------+---------------------------------+
| SUBSTRING('Quadratically',5) | SUBSTRING('Quadratically' FROM 5) | SUBSTRING('Quadratically',5,3) | SUBSTRING('Quadratically',-5) | SUBSTRING('Quadratically',-5,4) |
+------------------------------+-----------------------------------+--------------------------------+-------------------------------+---------------------------------+
| ratically | ratically | rat | cally | call |
+------------------------------+-----------------------------------+--------------------------------+-------------------------------+---------------------------------+
OCT(N) 返回N的八进制值的字符串表示形式，其中N是一个longlong（BIGINT）数字。 这等效于CONV（N，10,8）。 如果N为NULL，则返回NULL。
mysql> SELECT OCT(8),CONV(8,10,8),OCT(null);
+--------+--------------+----------+
| OCT(8) | CONV(8,10,8) | OCT(null) |
+--------+--------------+----------+
| 10 | 10 | NULL |
+--------+--------------+----------+
QUOTE(str)引用字符串以产生可在SQL语句中用作正确转义的数据值的结果。
mysql> SELECT QUOTE('Don\'t!'),QUOTE(NULL),QUOTE('Don\t!');
+---------------+--------------+---------------+
| QUOTE('Don\'t!') | QUOTE(NULL) | QUOTE('Don\t!') |
+---------------+--------------+---------------+
| 'Don\'t!' | NULL | 'Don !' |
+---------------+--------------+---------------+
REPEAT(str,count) 返回一个由重复计数次数count的字符串str组成的字符串。 如果count小于1，则返回一个空字符串。 如果str或count为NULL，则返回NULL。
mysql> SELECT REPEAT('MySQL', 3),REPEAT('MySQL', -1),REPEAT('MySQL', NULL);
+--------------------+---------------------+-----------------------+
| REPEAT('MySQL', 3) | REPEAT('MySQL', -1) | REPEAT('MySQL', NULL) |
+--------------------+---------------------+-----------------------+
| MySQLMySQLMySQL | | NULL |
+--------------------+---------------------+-----------------------+
REPLACE(str,from_str,to_str) 返回字符串str，其中所有出现的字符串from_str都替换为字符串to_str。REPLACE（）执行区分大小写的匹配。
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Qq'),REPLACE('www.mysql.com', 'W', 'Qq'),REPLACE('www.mysql.com', 'w',NULL);
+-------------------------------------+-------------------------------------+------------------------------------+
| REPLACE('www.mysql.com', 'w', 'Qq') | REPLACE('www.mysql.com', 'W', 'Qq') | REPLACE('www.mysql.com', 'w',NULL) |
+-------------------------------------+-------------------------------------+------------------------------------+
| QqQqQq.mysql.com | www.mysql.com | NULL |
+-------------------------------------+-------------------------------------+------------------------------------+
REVERSE(str) 返回str反转后的字符串。
mysql> SELECT REVERSE('GoodBoy');
+--------------------+
| REVERSE('GoodBoy') |
+--------------------+
| yoBdooG |
+--------------------+
SOUNDEX(str)从str返回一个soundex字符串。
1.发音听起来几乎相同的两个单词应该具有相同的soundex字符串。
2.标准soundex字符串长四个字符，但SOUNDEX（）函数返回任意长的字符串。
3.str中的所有非字母字符都将被忽略。
4.所有A-Z范围以外的国际字母字符都被视为元音。
5.此函数转换时是先丢弃元音，然后再去掉重复字符，属于原始的Soundex算法。增强版本是先去掉重复字符，然后再丢弃元音。
6.函数作用：在做搜寻引擎时面对用户传入的英文字符串，可用本函数做类似模糊比对的效果。
mysql> SELECT SOUNDEX('ladygaga');
+---------------------+
| SOUNDEX('ladygaga') |
+---------------------+
| L320 |
+---------------------+
各字符对应数字的转换规则：
'AEHIOUWY' : 0
'BFPV' : 1
'CGJKQSXZ' : 2
'DT' : 3
'L' : 4
'MN' : 5
'R' : 6
转换步骤：
1.先将对应0的元音字符去掉，得到 'ldgg'；
2.去掉重复字符，得到 'ldg';
3.首字符不变，其他字符转换为对应数字，得到 'l32';
4.长度不够4位，后面补0，得到 'l320';
5.首字符大写，得到最终结果 L320 。
发音相同的单词具有相同的soundex字符串。
mysql> select soundex('Hunt'),soundex('Hant');
+-----------------+-----------------+
| soundex('Hunt') | soundex('Hant') |
+-----------------+-----------------+
| H530 | H530 |
+-----------------+-----------------+
expr1 SOUNDS LIKE expr2 比较expr1的soundex字符串是否等于expr2的soundex字符串，返回布尔值。相同返回1，不同返回0。和表达式 SOUNDEX(str1) = SOUNDEX(str2) 效果一致。
mysql> SELECT 'Hunt' SOUNDS LIKE 'Hant',SOUNDEX('HUNT') = SOUNDEX('HANT'),'GOOD' SOUNDS LIKE 'BOY' ;
+---------------------------+-----------------------------------+--------------------------+
| 'Hunt' SOUNDS LIKE 'Hant' | SOUNDEX('HUNT') = SOUNDEX('HANT') | 'GOOD' SOUNDS LIKE 'BOY' |
+---------------------------+-----------------------------------+--------------------------+
| 1 | 1 | 0 |
+---------------------------+-----------------------------------+--------------------------+
SPACE(N)返回由N个空格字符组成的字符串。
mysql> SELECT CONCAT('A',SPACE(6),'B');
+--------------------------+
| CONCAT('A',SPACE(6),'B') |
+--------------------------+
| A B |
+--------------------------+
SUBSTRING_INDEX(str,delim,count) 在出现定界符delim之前，从字符串str返回子字符串。
1.如果count为正，则返回最后定界符左侧的所有内容（从左侧开始计数）。
2.如果count为负，则返回最后定界符右边的所有内容（从右边开始计数）。
3.搜索delim时，SUBSTRING_INDEX（）执行区分大小写。
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2),SUBSTRING_INDEX('www.mysql.com', '.', 6),SUBSTRING_INDEX('www.mysql.com', '.', -2);
+------------------------------------------+------------------------------------------+-------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 2) | SUBSTRING_INDEX('www.mysql.com', '.', 6) | SUBSTRING_INDEX('www.mysql.com', '.', -2) |
+------------------------------------------+------------------------------------------+-------------------------------------------+
| www.mysql | www.mysql.com | mysql.com |
+------------------------------------------+------------------------------------------+-------------------------------------------+
WEIGHT_STRING()完整表达式WEIGHT_STRING(str [AS {CHAR|BINARY}(N)] [LEVEL levels] [flags])
WEIGHT_STRING（）是供内部使用的调试函数。 不做描述。
二、字符串比较函数
LIKE() 完整表达式expr LIKE pat [ESCAPE 'escape_char']
1.使用SQL模式进行匹配。 返回布尔值，匹配到则为1（TRUE），否则为0（FALSE）；如果expr或pat为NULL，则结果为NULL；
mysql> select 'good' like 'good','good' like 'god','good' like NULL;
+--------------------+-------------------+------------------+
| 'good' like 'good' | 'good' like 'god' | 'good' like NULL |
+--------------------+-------------------+------------------+
| 1 | 0 | NULL |
+--------------------+-------------------+------------------+
2.默认不区分大小写匹配，如果字符串比较中的任何表达式区分大小写，则以区分大小写的方式执行比较；
SELECT 'abc' LIKE 'ABC','abc' LIKE BINARY 'ABC','abc' LIKE _latin1 'ABC' COLLATE latin1_general_cs;
+------------------+-------------------------+----------------------------------------------------+
| 'abc' LIKE 'ABC' | 'abc' LIKE BINARY 'ABC' | 'abc' LIKE _latin1 'ABC' COLLATE latin1_general_cs |
+------------------+-------------------------+----------------------------------------------------+
| 1 | 0 | 0 |
+------------------+-------------------------+----------------------------------------------------+
3.LIKE在每个字符的基础上执行匹配，因此它可以产生与=比较运算符不同的结果；
SELECT 'ä' LIKE 'ae' COLLATE utf8mb4_german2_ci,'ä' = 'ae' COLLATE utf8mb4_german2_ci;
+------------------------------------------+---------------------------------------+
| 'ä' LIKE 'ae' COLLATE utf8mb4_german2_ci | 'ä' = 'ae' COLLATE utf8mb4_german2_ci |
+------------------------------------------+---------------------------------------+
| 0 | 1 |
+------------------------------------------+---------------------------------------+
4.对于非二进制字符串，末尾空格需要参与到比较中。
MySQL8版本中则取决于排序集collations的PAD属性，有以下两个值：
> PAD SPACE 比较字符串时不考虑尾随空格
> NO PAD 比较字符串时考虑尾随空格
mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a ' |
+------------+---------------+
| 1 | 0 |
+------------+---------------+
5.通配符
% 匹配任意数量的字符，甚至零个字符
_ 精确匹配一个字符
mysql> SELECT 'David!' LIKE '%D%v%','David!' LIKE 'David_','David!!' LIKE 'David_';
+-----------------------+------------------------+-------------------------+
| 'David!' LIKE '%D%v%' | 'David!' LIKE 'David_' | 'David!!' LIKE 'David_' |
+-----------------------+------------------------+-------------------------+
| 1 | 1 | 0 |
+-----------------------+------------------------+-------------------------+
6.要测试通配符的文字实例，请在其前面加上转义符。 如果未指定ESCAPE字符，则假定\。
% 匹配一个%字符
_ 匹配一个_字符
mysql> SELECT 'David%' LIKE 'David\%','David_' LIKE 'David\_';
+-------------------------+-------------------------+
| 'David%' LIKE 'David\%' | 'David_' LIKE 'David\_' |
+-------------------------+-------------------------+
| 1 | 1 |
+-------------------------+-------------------------+
7.要指定其他转义字符，请使用ESCAPE子句
mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
+------------------------------------+
| 'David_' LIKE 'David|_' ESCAPE '|' |
+------------------------------------+
| 1 |
+------------------------------------+
8.作为对标准SQL的扩展，MySQL允许对数字表达式使用LIKE。
mysql> SELECT 10 LIKE '1%';
+--------------+
| 10 LIKE '1%' |
+--------------+
| 1 |
+--------------+
NOT LIKE() 完整表达式expr NOT LIKE pat [ESCAPE 'escape_char']
与NOT（expr LIKE pat [ESCAPE'escape_char']）相同，可参考LIKE用法。
注意字段包含NULL的情况，日常工作中也会经常遇到。
like 的数据量 + not like 的数据量 <> 总量，就是因为字段存在NULL导致。
有表数据如下：
mysql> select * From foo ;
+------+
| bar |
+------+
| NULL |
| NULL |
+------+
mysql> select count(*) from foo where bar like '%ba%';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
mysql> select count(*) from foo where bar not like '%ba%';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
mysql> select count(*) from foo where bar not like '%ba%' or bar is null;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
STRCMP(expr1,expr2)如果字符串相同，则STRCMP（）返回0；根据当前排序顺序，如果第一个参数小于第二个参数，则返回-1，否则返回1。
mysql> SELECT STRCMP('text', 'text'),STRCMP('text1', 'text2'),STRCMP('text2', 'text1');
+------------------------+--------------------------+--------------------------+
| STRCMP('text', 'text') | STRCMP('text1', 'text2') | STRCMP('text2', 'text1') |
+------------------------+--------------------------+--------------------------+
| 0 | -1 | 1 |
+------------------------+--------------------------+--------------------------+
指定排序规则的字符串进行比较
mysql> SELECT STRCMP(_latin1 'A' COLLATE latin1_general_ci,_latin1 'a' COLLATE latin1_general_ci),STRCMP(_latin1 'A' COLLATE latin1_general_cs,_latin1 'a' COLLATE latin1_general_cs);
+-------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
| STRCMP(_latin1 'A' COLLATE latin1_general_ci,_latin1 'a' COLLATE latin1_general_ci) | STRCMP(_latin1 'A' COLLATE latin1_general_cs,_latin1 'a' COLLATE latin1_general_cs) |
+-------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
| 0 | -1 |
+-------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
三、正则表达式
见链接 https://www.jianshu.com/p/cc18fd228e88
四、字符集和排序规则函数
1.简单字符串函数返回的值的字符集与输入字符串的字符集相同；如 INSTR(), LCASE(), LOWER(), LTRIM(), MID(), REPEAT(), REPLACE(), REVERSE(), RIGHT(), RPAD(), RTRIM(), SOUNDEX(), SUBSTRING(), TRIM(), UCASE(), and UPPER()等。
2.与所有其他函数不同，REPLACE（）函数始终忽略字符串输入的排序规则，并执行区分大小写的比较。
3.对于多个输入字符串（多个字符集），一个输出字符串的情形，满足以下规则：
> 如果明确COLLATE 为Y，则返回COLLATE 为Y；
> 如果明确COLLATE 为Y 和 Z，则抛出错误；
> 否则，如果所有COLLATE 为Y，则返回COLLATE 为Y；
> 否则，返回的结果集没有COLLATE。
例如，使用CASE ... WHEN a THEN b WHEN b THEN c COLLATE X END，所得的排序规则为X。同样适用于UNION，||，CONCAT（），ELT（），GREATEST（），IF（） 和LEAST（）。
4.对于转换为字符数据的操作，这些操作产生的字符串的字符集和排序规则由character_set_connection和collation_connection系统变量定义，这些系统变量确定默认的连接字符集和排序规则。 仅适用于CAST（），CONV（），FORMAT（），HEX（）和SPACE（）。
5.从MySQL 5.7.19开始，虚拟生成的列的表达式是以上规则的例外情况。 在这样的表达式中，表默认的字符集用于CONV（）或HEX（）的结果，而与连接字符集（character_set_connection和collation_connection）无关。
5.使用CHARSET()或collation()函数查找所属字符集。
mysql> SELECT CHARSET('a'),CHARSET(BINARY 'a'),CHARSET(COMPRESS('abc')), COLLATION(COMPRESS('abc'));
+--------------+---------------------+--------------------------+----------------------------+
| CHARSET('a') | CHARSET(BINARY 'a') | CHARSET(COMPRESS('abc')) | COLLATION(COMPRESS('abc')) |
+--------------+---------------------+--------------------------+----------------------------+
| utf8mb4 | binary | binary | binary |
+--------------+---------------------+--------------------------+----------------------------+
网友评论