MySQL の最適化 - よく使用される関数コードの詳細な説明 (写真)

数学関数

1.剰余関数MOD(X,Y)

MOD(X,Y)は、xをyで割った後の剰余を返します。MOD()は10進数の値です。パーツも機能し、除算後の正確な剰余を返します

SELECT MOD(31,8)

2。丸め関数 TRUNCATE(X,Y)

TRUNCATE(X,Y) は、小数点以下 y 桁に四捨五入された数値 x を返します。 。 y の値が 0 の場合、結果には小数点も小数部分もありません。

yに負の数を設定した場合、xの小数点以下y桁目以降の下位の値がすべて切り捨てられます(0に戻ります)。

SELECT TRUNCATE(1.32,1)

TRUNCATE(1.32,1) は小数点以下 1 桁を保持し、戻り値は 1.3 です

ヒント: ROUND(X,Y) 関数は値を切り取るときに丸めますが、TRUNCATE(x,y) ) 丸めずに直接値を切り取る

3. 剰余関数 HEX(X) および UNHEX(X) 関数

次のコードは HEX と UNHEX の関数を示すことができます:

SELECT HEX('this is a test str')

クエリ結果は次のとおりです: 746869732069732061207465737420737472

SELECT UNHEX('746869732069732061207465737420737472')

クエリの結果: これはテスト str

---

文字列関数

文字列の文字数を計算する関数です

1 の戻り値は文字数です。文字列 str に含まれています。マルチバイト文字は 1 文字としてカウントされます

SELECT CHAR_LENGTH('DATE')

2. 連結文字列関数 CONCAT_WS(x,s1,s2,...)

CONCAT_WS(x,s1,s2,... ... )、CONCAT_WS は CONCAT with Separator を表し、CONCAT() 関数の特別な形式です。

最初のパラメータ x は他のパラメータのセパレータであり、セパレータの位置は接続される 2 つの文字列の間に配置されます。区切り文字には文字列または

他のパラメータを使用できます。区切り文字が NULL の場合、結果は NULL になります。この関数は、区切り文字引数の後の NULL 値を無視します。

SELECT CONCAT_WS('-','1st','2nd','3rd'),CONCAT_WS('-','1st',NULL,'3rd')

CONCAT_WS('-','1st','2nd','3rd') は区切り文字 '-' を使用して 3 つの文字列を 1 つの文字列に連結します。結果は "1st-2nd-3rd" になります。

CONCAT_WS('-','1st',NULL,'3rd') 2番目の値はNULLのため、結果には2番目の値は表示されません

3. 文字列を置換する関数 INSERT(S1 ,X,LEN,S2) )

INSERT (S1, ,LEN,S2) および RPAD(S1,LEN,S2)

LPAD(S1,LEN,S2) は、文字列 s1 を返します。その左側には文字列 s2 が len まで埋め込まれています。文字列の長さ。 s1 の長さが len より大きい場合、戻り値は len 文字に短縮されます。

LPAD()

関数とRPAD()関数はSQLSERVERのREPLACE()に似ていますが、より強力です

SELECT LPAD('hello',4,'??'),LPAD('hello',10,'??')

文字列「hello」の長さは4より大きく、そうではありませんパディングが必要なため、LPAD ('hello',4,'??') は長さ 4 の短縮された部分文字列のみを返します

文字列 "hello" の長さは 10 未満です、LPAD('hello',10,'? ?') は結果を「????hello」として返し、「?」は左側に埋め込まれ、長さは 10

5 です。RPAD(S1,LEN,S2) は文字列 s1 を返します。右側には、文字列 s2 が len 文字の長さまで埋め込まれます。文字列 s1 の長さが len より大きい場合、戻り値は len 文字の長さに短縮されます

SELECT RPAD('hello',4,'?')

文字列 "hello" の長さは 4 より大きく、パディングは必要ないため、RPAD(' hello',4,'?') スペースを削除する関数 TRIM(S)

TRIM(S)

長さ 4 の短縮された部分文字列 "hell" のみを返します

TRIM(S)ここでは、文字列 s

MYSQL の方が SQLSERVER よりも便利です。 SQLSERVER 両側のスペースを削除するには、次を使用する必要があります: SELECTLTRIM(') hello'))

SELECT TRIM(' book ')

関数 TRIM(S1 FROM S)

7. TRIM(S1 FROM S) は、文字列 s の両端の部分文字列 s1 をすべて削除します。 s1 は省略可能です。指定しない場合はスペースを削除します

SELECT TRIM('xy' FROM 'xyxboxyokxxyxy')

'xyxboxyokxxyxy' 両端の繰り返し文字列 "xy" ですが、中央の "xy" は削除されず、結果は

xboxyokx

となります。

8. 繰り返し生成文字列関数 REPEAT(S,N)

这个函数跟SQLSERVER里的REPLICATE()函数是一样的，参数个数都是一样的，这里不作介绍了

9、比较字符串大小的函数STRCMP(S1,S2)

STRCMP(S1,S2)若所有的字符串均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1

SELECT STRCMP('txt','txt2') ,STRCMP('txt2','txt'),STRCMP('txt','txt')

“txt”小于“txt2”，因此 STRCMP('txt','txt2') 返回结果为-1

STRCMP('txt2','txt')返回结果为1

“txt”与“txt”相等，因此STRCMP('txt','txt')返回结果为0

10、匹配子串开始位置的函数

LOCATE(STR1,STR)、POSITION(STR1 IN STR)、INSTR(STR,STR1)3个函数作用相同，返回子字符串str1在字符串str中的开始位置

这三个函数跟SQLSERVER里的CHARINDEX()函数功能类似

SELECT LOCATE('ball','football'),POSITION('ball' IN 'football') ,INSTR('football','ball')

子字符串“ball”在字符串“football”中从第5个字母位置开始，因此3个函数返回结果都为5

11、返回指定位置的字符串的函数

ELT(N,字符串1,字符串2,字符串3,...,)，若N=1,则返回值为字符串1，若N=2，则返回值为字符串2，以此类推。

若N小于1或大于参数的数目，则返回值为NULL

SELECT ELT(3,'1st','2nd','3rd'),ELT(3,'net','os')

由结果可以看到,ELT(3,'1st','2nd','3rd')返回第3个位置的字符串“3rd”；指定返回字符串位置超出参数个数，返回NULL

12、返回指定字符串位置的函数FIELD(S,S1,S2,...)

FIELD(S,S1,S2,...)返回字符串s在列表s1，s2，......中第一次出现的位置，在找不到s的情况下，返回值为0。

如果s为NULL，则返回值为0，原因是NULL不能同任何值进行同等比较。

SELECT FIELD('hi','hihi','hey','hi','bas') AS coll,
FIELD('hi','hihi','lo','hilo','foo') AS col2

FIELD('hi','hihi','hey','hi','bas')函数中字符串hi出现在列表的第3个字符串位置，因此返回结果为3

FIELD('hi','hihi','lo','hilo','foo') 列表中没有字符串hi，因此返回结果为0

13、返回子串位置的函数FIND_IN_SET(S1,S2)

FIND_IN_SET(S1,S2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号

‘,’分开的字符串组成的列表。如果s1不在s2或s2为空字符串，则返回值为0。如果任意一个参数为NULL，则返回值为NULL。

这个函数在第一个参数包含一个逗号‘,’时将无法正常运行。

SELECT FIND_IN_SET('hi','hihi,hey,hi,bas')

虽然FIND_IN_SET(S1,S2)和FIELD(S,S1,S2,...)两个函数格式不同，但作用类似，都可以返回指定字符串在字符串列表中的位置

14、选取字符串的函数MAKE_SET(X,S1,S2,...)

MAKE_SET(X,S1,S2,...)返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1，s2对应比特01以此类推。

s1，s2...中的NULL值不会被添加到结果中。

SELECT MAKE_SET(1,'a','b','c') AS col1,
MAKE_SET(1|4,'hello','nice','world') AS col2

1的二进制值为0001，4的二进制值为0100，1与4进行异或操作之后的二进制值为0101，从右到左第一位和第三位为1。

MAKE_SET(1,'a','b','c')返回第一个字符串

MAKE_SET(1|4,'hello','nice','world') 返回从左端开始第一和第三个字符串组成的字符串

 

---

日期和时间函数

1、获取当前日期的函数和获取当前时间的函数

CURDATE()、CURRENT_DATE()、CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

以上函数都是返回当前日期和时间值，MYSQL的函数数量的确比SQLSERVER多很多，SQLSERVER获取当前时间和日期用的

函数是： SELECT GETDATE() 

SELECT NOW()

 

返回UTC日期的函数和返回UTC时间的函数

MYSQL里返回UTC日期和时间是分开的，而SQLSERVER里是一起的

SELECT GETUTCDATE()

 

MYSQL

SELECT UTC_DATE(),UTC_TIME()

 

2、获取月份的函数MONTHNAME(DATE)

MONTHNAME(DATE)函数返回日期date对应月份的英文全名

SELECT MONTHNAME('2013-8-2')

返回8月份的英文

 

3、获取季度、分钟和秒钟的函数

QUARTER(DATE)返回date对应的一年中的季度值，范围是从1~4

使用QUARTER(DATE)函数返回指定日期对应的季度

SELECT QUARTER('11-04-01')

 

4月份在第二季度，所以返回2

 

4、MINUTE(TIME)返回time对应的分钟数，范围是从0~59

SELECT MINUTE('11-02-03 10:10:06')

返回10分钟

 

5、SECOND(time) 返回time对应的秒数，范围是从0~59

SELECT SECOND('10:23:10')

返回10秒

6、获取日期的指定值的函数EXTRACT(type FROM date)

EXTRACT(type FROM date)这个函数跟SQLSERVER里的DATEPART()函数是一样的

获取日期中的年份

SQLSERVER

SELECT DATEPART(YEAR,'2013-2-3')

MYSQL

SELECT EXTRACT(YEAR FROM '2013-2-3')

7、时间和秒钟转换的函数

TIME_TO_SEC(time)返回已转化为秒的time参数，转换公式为：小时*3600+分钟*60+秒

SELECT TIME_TO_SEC('23:22:00')

SEC_TO_TIME(second)函数可以将秒转换为小时、分钟和秒数的second参数值

SELECT SEC_TO_TIME('84120')

SEC_TO_TIME(second)函数和TIME_TO_SEC(time)函数互为反函数

8、计算日期和时间的函数

MYSQL里计算日期和时间的函数比较多

增加日期：DATE_ADD(date,interval  expr type)，ADDDATE(date,interval  expr type)

减去日期：DATE_SUB(date,interval  expr type)，SUBDATE(date,interval  expr type)

增加时间：ADD_TIME(date,expr)

减去时间：SUBTIME(date,expr)

时间差：DATEDIFF()

日期和时间格式化：

DATE_FORMAT(date,format)

TIME_FORMAT(time,format)

返回日期时间字符串的显示格式：GET_FORMAT(val_type,format_type)

相对来说，SQLSERVER在时间日期方面的计算就没有那么多函数了

基本上SQLSERVER利用下面两个函数来通杀日期时间计算的场景

SELECT DATEADD(),DATEDIFF()

---

条件判断函数

条件判断函数也称为流程控制函数，根据满足的条件的不同，执行相应的流程。

MYSQL中进行条件判断的函数有IF、IFNULL、CASE

虽然SQLSERVER里也有IF和CASE，不过MYSQL里的IF语句的语法跟SQLERVER有很大出入

1、IF(expr,v1,v2)函数

IF(expr,v1,v2)如果表达式expr是TRUE（expr0 and exprNULL），则IF()的返回值为v1；

否则返回值为v2。IF()的返回值为数字值或字符串值，具体情况视其所在语境而定

SELECT IF(1>2,2,3)

1>2的结果为FALSE，IF(1>2,2,3)返回第二个表达式的值3。

TIPS：如果v1或v2中只有一个明确是NULL，则IF()函数的结果类型为非NULL表达式的结果类型。

2、IFNULL(V1,V2)函数

IFNULL(V1,V2)假如v1不为NULL，则IFNULL(V1,V2)的返回值为v1；否则其返回值为v2。

IFNULL()的返回值是数字或是字符串，具体情况视语境而定

SELECT IFNULL(1,2),IFNULL(NULL,10)

IFNULL(1,2)虽然第二个值也不为空，但返回结果依然是第一个值；

IFNULL(NULL,10)第一个值为空，因此返回“10”

注意：IFNULL(V1,V2)函数跟SQLSERVER里的 SELECTNULLIF() 函数不一样

SQLSERVER里的NULLIF函数

需要两个参数,如果两个指定的表达式等价，则返回null 

例子：NULLIF(a,b) 

说明：如果a和b是相等的，那么返回NULL，如果不相等返回a 

select NULLIF('eqeqweqwe','1') 结果是eqeqweqwe 

select NULLIF(1,1) 结果是NULL 

a和b的类型要一致 

3、CASE函数

注意：一个CASE表达式的默认返回值类型是任何返回值的相容集合类型，但具体情况视其所在语境而定。

如果用字符串语境中，则返回结果为字符串。如果用在数字语境中，则返回结果为十进制、实数值或整数值

这个数据类型在拼接SQL语句的时候特别容易忽略，有时候会在拼接SQL语句的时候，case 后面的表达式或者when后面的表达式不一致报错！

---

系统信息函数

1、获取MYSQL版本号、连接数和数据库名的函数

VERSION()返回指示MYSQL服务器版本的字符串。这个字符串使用utf8字符集

SELECT VERSION()

 

2、CONNECTION_ID()返回MYSQL服务器当前连接的次数，每个连接都有各自唯一的ID

查看当前用户的连接数

SELECT CONNECTION_ID()

这里返回1，返回值根据登录的次数会有不同。

3、SHOW PROCESSLIST;

4、SHOW FULL PROCESSLIST;

processlist命令的输出结果显示了有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态

帮助识别出有问题的查询语句等。

如果是root帐号，能看到所有用户的当前连接。如果是其他普通帐号，则只能看到自己占用的连接。showprocesslist只能列出当前100条

如果想全部列出，可以使用SHOW FULL PROCESSLIST命令

SHOW PROCESSLIST

SHOW FULL PROCESSLIST

show full processlist会看到连接使用的内存

show processlist

show full processlist

 各个列的含义

（1）id列，用户登录mysql时，系统分配的“connection_id”

（2）user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

（3）host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

（4）db列，显示这个进程目前连接的是哪个数据库

（5）command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）

（6）time列，显示这个状态持续的时间，单位是秒

（7）state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有状态的描述，state只是语句执行中的某一个状态。一个sql语句，

以查询为例

可能需要经过

copying to tmp table，

sorting result，

sending data

等状态才可以完成

（8）info列，显示这个sql语句，是判断问题语句的一个重要依据。

5、DATABASE()和SCHEMA()函数返回使用utf8字符集的默认（当前）数据库名

SELECT DATABASE(),SCHEMA()

可以看到，两个函数的作用相同

6、获取用户名的函数

USER()、CURRENT_USER()、CURRENT_USER、SYSTEM_USER()、SESSION_USER()

这几个函数返回当前被MYSQL服务器验证的用户名和主机名组合。这个值符合确定当前登录用户

存取权限的MYSQL帐户。一般情况下，这几个函数的返回值是相同的。

SELECT    USER(),CURRENT_USER(),SYSTEM_USER()

返回结果指示了当前帐户连接服务器的用户名以及所连接的客户主机，root为当前登录的用户名，localhost为登录的主机名

7、获取字符串的字符集和排序方式的函数

CHARSET(STR)返回字符串str自变量的字符集

SELECT    CHARSET('abc') ,CHARSET(CONVERT('abc' USING latin1)),CHARSET(VERSION())

CHARSET('abc')返回系统默认的字符集utf8；

CHARSET(CONVERT('abc' USING latin1))返回的字符集为latin1；

VERSION()返回的字符串使用utf8字符集，因此CHARSET返回结果为utf8

8、COLLATION(str)返回字符串str的字符排列方式

SELECT    COLLATION(_latin2 'abc'),COLLATION(CONVERT('abc' USING utf8))

可以看到，使用不同字符集时字符串的排列方式不同

9、获取最后一个自动生成的ID值的函数

LAST_INSERT_ID()自动返回最后一个INSERT或UPDATE为AUTO_INCREMENT列设置的第一个发生的值

（1）一次插入一条记录

首先创建表worker，其ID字段带有AUTO_INCREMENT约束

CREATE TABLE worker(
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
NAME VARCHAR(30)
)

分别单独向表worker插入2条记录

INSERT INTO worker VALUES(NULL,'jimmy');INSERT INTO worker VALUES(NULL,'tom')SELECT * FROM worker

查看已经插入的数据可以发现，最后一条插入的记录的ID字段值为2，使用LAST_INSERT_ID()查看最后自动生成的ID值

SELECT LAST_INSERT_ID()

 可以看到，一次插入一条记录时，返回值为最后一条插入记录的ID值

（2）一次同时插入多条记录

 接下来，向表中插入多条记录

INSERT INTO worker VALUES(NULL,'kevin'),(NULL,'michal'),(NULL,'nick')

查询已经插入的记录

SELECT * FROM worker

可以看到最后一条记录的ID字段值为5，使用LAST_INSERT_ID()查看最后自动生成的ID值

SELECT LAST_INSERT_ID()

结果显示，ID字段值不是5而是3，这是为什麽呢？

在向数据表插入一条记录时，LAST_INSERT_ID()返回带有AUTO_INCREMENT约束的字段最新生成的值2；继续向表

中同时添加3条记录，这时候因为当使用一条INSERT语句插入多个行时，LAST_INSERT_ID只返回插入的第一行数据

时产生的值，在这里为第3条记录。之所以这样，是因为这使依靠其他服务器复制同样的INSERT语句变得简单

TIPS:LAST_INSERT_ID是与table无关的，如果向表a插入数据后，再向表b插入数据，LAST_INSERT_ID返回表b中的ID值

这里跟SQLSERVER不一样

使用下面脚本来测试，无论使用方式一还是方式二，当前的LAST_INSERT_ID都是最后一个值

CREATE TABLE [dbo].[aaa](    [a] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,    [name] [nvarchar](50) NOT NULL) ON [PRIMARY]

USE [sss]SELECT * FROM [dbo].[aaa]INSERT INTO [dbo].[aaa]
        ( [name] )VALUES  ( N'sdfsdf'  -- name - nvarchar(50)          )SELECT * FROM [dbo].[aaa]INSERT INTO [dbo].[aaa]
        ( [name] )VALUES  ( N'sdf969'  -- name - nvarchar(50)
          ),('lkjj96'),('565656')SELECT IDENT_CURRENT('aaa')SELECT * FROM [dbo].[aaa]

---

加密函数

1、加密函数PASSWORD(STR)

PASSWORD(STR)从原文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL

SELECT PASSWORD('NEWPWD')

MYSQL将PASSWORD函数加密后的密码保存到用户权限表中

TIPS:PASSWOR()函数在MYSQL服务器的鉴定系统中使用；不应将他用在个人应用程序中，PASSWORD（）函数加密是单向的（不可逆）

PASSWORD执行密码加密与UNIX中密码加密方式不同

2、加密函数MD5(str)

MD5(str)为字符串算出一个MD5 128比特校验和。该值以32位十六进制数字的二进制字符串形式返回，若参数为NULL，则会返回NULL

<p style="margin-bottom: 7px;">SELECT MD5(&#39;123&#39;)</p>

3、加密函数ENCODE(str,pswd_str)

ENCODE(str,pswd_str)使用pswd_str作为密码，加密str。使用DECODE()解密结果，结果是一个和str长度相同的二进制字符串

SELECT ENCODE('nihao','123')

可以看到加密后的结果为乱码

 4、解密函数DECODE(crypt_str,pswd_str)

 DECODE(crypt_str,pswd_str)使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE()返回的字符串

SELECT DECODE(ENCODE('nihao','123'),'123')

可以看到，解密出来的字符串

ENCODE()和DECODE互为反函数

---

其他函数

1、格式化函数FORMAT(x,n)

FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。

若n为0，则返回结果函数不含小数部分

SELECT FORMAT(12332.123465,4)

FORMAT(12332.123465,4)保留4位小数点值，并进行四舍五入，结果为12,332.1235

2、不同进制的数字转换的函数

CONV(N,from_base,to_base)函数进行不同进制数间的转换。

SELECT CONV('a',16,2)

 CONV('a',16,2)将十六进制的a转换为二进制表示的数值。

3、IP地址与数字相互转换的函数

INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。

地址可以是4或8比特地址

INET_NTOA(expr)给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示。

4、加锁函数和解锁函数

GET_LOCK(str,timeout)设法使用字符串str给定的名字得到一个锁，超时为timeout秒。

RELEASE_LOCK(str)解开被GET_LOCK()获取的，用字符串str所命名的锁。

IS_FREE_LOCK(str)检查名为str的锁是否可以使用

IS_USED_LOCK(str)检查名为str的锁是否正在被使用

5、重复执行指定操作的函数

BENCHMARK(count,expr)函数重复count次执行表达式expr。他可以用于计算MYSQL处理表达式的速度。

结果值通常为0（0只是表示处理过程很快，并不是没有花费时间）

另一个作用是他可以在MYSQL客户端内部报告语句执行的时间。

首先，使用PASSWORD函数加密密码

SELECT PASSWORD('nihao')

可以看到PASSWORD()函数执行花费的时间为0.00098秒

下面使用BENCHMARK函数重复执行PASSWORD操作500000次

SELECT BENCHMARK(500000,PASSWORD('nihao'))

由此可以看出，使用BENCHMARK执行500000次的时间为0.49690秒，明显比执行一次的时间延长了。 

TIPS：BENCHMARK报告的时间是客户端经过的时间，而不是在服务器端的CPU时间，每次执行后报告的时间并不一定是相同的。

6、改变字符集的函数

CONVERT(...using...)带有USING的CONVERT()函数被用来在不同的字符集之间转化数据。

SELECT  CHARSET('string'),CHARSET(CONVERT('string' USING latin1))

默认为utf8字符集，通过CONVERT()将字符串“string”的默认字符集改为latin1

7、改变数据类型的函数

CAST(x,AS type)和CONVERT(x,type)函数将一个类型的值转换为另一个类型的值，可转换的type有：

BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED

在SQLSERVER里也是使用这两个函数进行数据类型转换的~

SELECT  CAST(100 AS CHAR(2)),CONVERT('2013-8-9 12:12:12',TIME)

可以看到， CAST(100 AS CHAR(2))将整数数据100转换为带有2个显示宽度的字符串类型，结果为10

CONVERT('2013-8-9 12:12:12',TIME)将DATETIME类型的值，转换为TIME类型值，结果为“12:12:12”

---

TIPS:

1、MYSQL中，日期时间以字符串形式存储在数据表中，因此可以使用字符串函数分别截取日期时间值的不同部分

2、修改默认的字符集，更改MYSQL默认的字符集，在Windows中，只需要修改my.ini，该文件在MYSQL安装目录下。

修改配置文件中的default-character-set和character-set-server参数值，将其改为想要的字符集名称，如：

gbk、gb2312、latin1等，修改完之后，重启MYSQL服务，即可生效。

如果不确定当前使用的字符集，可以使用下面的SQL语句来查看当前字符集进行对比

SHOW VARIABLES LIKE 'character_set_%'

以上がMySQL の最適化 - よく使用される関数コードの詳細な説明 (写真)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。