{"id":228,"date":"2022-04-11T17:01:25","date_gmt":"2022-04-11T09:01:25","guid":{"rendered":"http:\/\/danielw.top\/?p=228"},"modified":"2022-04-11T17:01:25","modified_gmt":"2022-04-11T09:01:25","slug":"mariadbmysql%e5%86%85%e7%bd%ae%e5%87%bd%e6%95%b0","status":"publish","type":"post","link":"http:\/\/danielw.top\/?p=228","title":{"rendered":"(MariaDB)MySQL\u5185\u7f6e\u51fd\u6570"},"content":{"rendered":"<h1>\u5b57\u7b26\u4e32\u51fd\u6570<\/h1>\n<p>\u5b8c\u6574\u7684\u5185\u7f6e\u5b57\u7b26\u4e32\u51fd\u6570\u89c1<a href=\"https:\/\/mariadb.com\/kb\/en\/string-functions\/\" target=\"_blank\"  rel=\"nofollow\" >\u5b98\u65b9\u624b\u518c<\/a>\u3002<\/p>\n<h2>\u5b57\u7b26\u4e32\u8fde\u63a5\u51fd\u6570<\/h2>\n<p>\u6709\u4e24\u4e2a\u5b57\u7b26\u4e32\u8fde\u63a5\u51fd\u6570\uff1aconcat(s1,s2,s3,...,sN)\u548cconcat_ws(sep,s1,s2,s3,...,sN)\u3002<\/p>\n<p>concat()\u5c06\u591a\u4e2a\u5b57\u7b26\u4e32\u8fde\u63a5\u8d77\u6765\u5f62\u6210\u4e00\u4e2a\u957f\u5b57\u7b26\u4e32\u3002\u5b83\u4f1a\u5c1d\u8bd5<strong>\u5c06\u5b57\u7b26\u5168\u90e8\u8f6c\u6362\u4e3a\u5b57\u7b26\u578b<\/strong>\uff0c\u5982\u679c\u5b58\u5728null\uff0c\u5219\u76f4\u63a5\u8fd4\u56denull\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select concat(&#039;a&#039;,&#039;b&#039;,1),concat(1,2,3),concat(&#039;a&#039;,null);\n+-------------------+---------------+------------------+\n| concat(&#039;a&#039;,&#039;b&#039;,1) | concat(1,2,3) | concat(&#039;a&#039;,null) |\n+-------------------+---------------+------------------+\n| ab1               | 123           | NULL             |\n+-------------------+---------------+------------------+\n1 row in set<\/code><\/pre>\n<p>concat_ws(sep,s1,s2,...,sN)\u51fd\u6570\u662fconcat()\u51fd\u6570\u7684\u7279\u6b8a\u683c\u5f0f\uff0c\u5b83\u7684\u7b2c\u4e00\u4e2a\u53c2\u6570sep\u662f\u7528\u4e8e\u8fde\u63a5s1,s2,...,sN\u7684\u5206\u9694\u7b26\u3002\u5206\u9694\u7b26\u53ef\u4ee5\u662f\u4e00\u4e2a\u5b57\u7b26\u6216\u4e00\u4e2a\u5b57\u7b26\u4e32\uff0c\u53ea\u8981\u5408\u7406\u5373\u53ef\u3002\u5982\u679c\u5206\u9694\u7b26sep\u4e3anull\uff0c\u5219\u8fd4\u56de\u7ed3\u679cnull\uff0c\u5982\u679cs1,s2,...,sN\u4e4b\u95f4\u51fa\u73b0\u4e86null\uff0c\u5219\u5ffd\u7565null\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select concat_ws(&#039;:&#039;,&#039;23&#039;,&#039;59&#039;,&#039;58&#039;),concat_ws(&#039;-&#039;,&#039;1st&#039;,&#039;2nd&#039;),concat_ws(&#039;XXX&#039;,&#039;wo&#039;,&#039;shi&#039;);\n+-------------------------------+----------------------------+-----------------------------+\n| concat_ws(&#039;:&#039;,&#039;23&#039;,&#039;59&#039;,&#039;58&#039;) | concat_ws(&#039;-&#039;,&#039;1st&#039;,&#039;2nd&#039;) | concat_ws(&#039;XXX&#039;,&#039;wo&#039;,&#039;shi&#039;) |\n+-------------------------------+----------------------------+-----------------------------+\n| 23:59:58                      | 1st-2nd                    | woXXXshi                    |\n+-------------------------------+----------------------------+-----------------------------+\n1 row in set\n\nmysql&gt; select concat_ws(&#039;:&#039;,&#039;23&#039;,&#039;59&#039;,null,&#039;58&#039;),concat_ws(null,&#039;1st&#039;,&#039;2nd&#039;);\n+------------------------------------+-----------------------------+\n| concat_ws(&#039;:&#039;,&#039;23&#039;,&#039;59&#039;,null,&#039;58&#039;) | concat_ws(null,&#039;1st&#039;,&#039;2nd&#039;) |\n+------------------------------------+-----------------------------+\n| 23:59:58                           | NULL                        |\n+------------------------------------+-----------------------------+\n1 row in set<\/code><\/pre>\n<p>\u7531\u4e8econcat()\u9047\u5230null\u65f6\u603b\u4f1a\u8fd4\u56denull\uff0c\u8fd9\u79cd\u5904\u7406\u65b9\u5f0f\u53ef\u80fd\u5e76\u975e\u6240\u671f\u671b\u7684\u7ed3\u679c\uff0c\u56e0\u6b64\u53ef\u4ee5\u91c7\u7528concat_ws()\u7684\u65b9\u5f0f\u5ffd\u7565null\u6216\u8005\u91c7\u7528ifnull()\u7684\u65b9\u5f0f\u5c06null\u8f6c\u6362\u4e3a\u7a7a\u5b57\u7b26\u4e32\u3002<\/p>\n<h2>lower(string)\u3001upper(string)\u3001left(string,x)\u3001right(string,x)<\/h2>\n<p>\u5206\u522b\u662f\u53d8\u5c0f\u5199\u3001\u53d8\u5927\u5199\u3001\u4ece\u5de6\u53d6x\u957f\u5ea6\u5b57\u7b26\u3001\u4ece\u53f3\u53d6x\u957f\u5ea6<strong>\u5b57\u7b26<\/strong>\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select lower(&#039;Dnaiel&#039;),upper(&#039;Daniel&#039;),left(&#039;MaLong&#039;,3),right(&#039;Daniel&#039;,3);\n+-----------------+-----------------+------------------+-------------------+\n| lower(&#039;Daniel&#039;) | upper(&#039;Daniel&#039;) | left(&#039;Daniel&#039;,3) | right(&#039;Daniel&#039;,3) |\n+-----------------+-----------------+------------------+-------------------+\n| daniel          | DANIEL          | Dan              | iel               |\n+-----------------+-----------------+------------------+-------------------+\n1 row in set<\/code><\/pre>\n<h2>\u586b\u5145\u51fd\u6570<\/h2>\n<p>\u6709\u4e24\u79cd\uff1alpad(string,n,pad)\u548crpad(string,n,pad)\u3002<\/p>\n<p>\u4f7f\u7528pad\u5bf9string\u6700\u5de6\u8fb9\u548c\u6700\u53f3\u8fb9\u8fdb\u884c\u586b\u5145\uff0c\u76f4\u5230\u586b\u5145\u540e\u603b\u957f\u5ea6\u4e3an\u4e2a\u5b57\u7b26\u3002pad\u53ef\u4ee5\u662f\u4e00\u4e2a\u5b57\u7b26\u4e32\uff0c\u5982\u679c\u662f\u5b57\u7b26\u4e32\u5219\u4ece\u5de6\u5411\u53f3\u53d6\u76f4\u5230\u7b26\u5408\u957f\u5ea6\u4e3a\u6b62\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select lpad(&#039;ddddd&#039;,10,&#039;x&#039;),lpad(&#039;ddddd&#039;,10,&#039;xy&#039;),rpad(&#039;ddddd&#039;,10,&#039;x&#039;);\n+-----------------------+------------------------+-----------------------+\n| lpad(&#039;dddddd&#039;,10,&#039;x&#039;) | lpad(&#039;dddddd&#039;,10,&#039;xy&#039;) | rpad(&#039;dddddd&#039;,10,&#039;x&#039;) |\n+-----------------------+------------------------+-----------------------+\n| xxxxdddddd            | xyxydddddd             | ddddddxxxx            |\n+-----------------------+------------------------+-----------------------+\n1 row in set<\/code><\/pre>\n<p>\u957f\u5ea6n\u53ef\u4ee5\u662f\u5c0f\u4e8e\u6216\u7b49\u4e8estring\u5b57\u7b26\u4e32\u957f\u5ea6\u7684\u503c\uff0c\u6b64\u65f6lpad\u6216\u8005rpad\u7684\u4f5c\u7528<strong>\u90fd\u662f\u4ece\u5de6\u8fdb\u884c\u5b57\u7b26\u4e32\u622a\u53d6\u800c\u975e\u586b\u5145<\/strong>\uff0c\u76f4\u5230\u957f\u5ea6\u4e3an\u3002\u4e5f\u5c31\u662f\u8bf4lpad\u548crpad\u51fd\u6570\u6700\u5f3a\u7ea6\u675f\u6761\u4ef6\u662f\u957f\u5ea6\u53c2\u6570n\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select rpad(&#039;Daniel&#039;,3,&#039;x&#039;),lpad(&#039;Daniel&#039;,3,&#039;x&#039;),lpad(&#039;Daniel&#039;,0,&#039;x&#039;);\n+----------------------+----------------------+----------------------+\n| rpad(&#039;Daniel&#039;,3,&#039;x&#039;) | lpad(&#039;Daniel&#039;,3,&#039;x&#039;) | lpad(&#039;Daniel&#039;,0,&#039;x&#039;) |\n+----------------------+----------------------+----------------------+\n| Dan                  | Dan                  |                      |\n+----------------------+----------------------+----------------------+\n1 row in set<\/code><\/pre>\n<h2>trim(string)\u3001ltrim(string)\u3001rtrim(sting)\u53catrim(substring from string)<\/h2>\n<p>\u5206\u522b\u7528\u6765\u6d88\u9664string\u884c\u9996\u548c\u884c\u5c3e\u3001\u884c\u9996\u3001\u884c\u5c3e\u7684\u7a7a\u683c\u4ee5\u53ca\u884c\u9996\u884c\u5c3e\u6307\u5b9a\u7684\u5b57\u7b26\u4e32\u3002<\/p>\n<pre><code class=\"language-sql\">         \u51fd\u6570                    \u4f5c\u7528\n-----------------------         -------------------------------\n      ltrim(string)             \u5220\u9664\u884c\u9996\u7a7a\u683c\n      rtrim(string)             \u5220\u9664\u884c\u5c3e\u7a7a\u683c\n      trim(string)              \u5220\u9664\u884c\u9996\u548c\u884c\u5c3e\u7a7a\u683c\ntrim(substring from string)     \u5220\u9664\u884c\u9996\u548c\u884c\u5c3e\u7684\u5b57\u7b26\u4e32substring<\/code><\/pre>\n<p>\u4f8b\u5982\uff1a<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select length(trim(&#039; Daniel &#039;)) as A,\n              length(ltrim(&#039;Daniel &#039;)) as B,\n              length(ltrim(&#039; Daniel &#039;)) as C,\n              length(rtrim(&#039; Daniel &#039;)) as D;\n+---+---+---+---+\n| A | B | C | D |\n+---+---+---+---+\n| 6 | 7 | 7 | 7 |\n+---+---+---+---+\n1 row in set (0.00 sec)\n\nmysql&gt; select trim(&#039;xy&#039; from &#039;xyxabxycdxyxy&#039;);\n+---------------------------------+\n| trim(&#039;xy&#039; from &#039;xyxabxycdxyxy&#039;) |\n+---------------------------------+\n| xabxycd                         |\n+---------------------------------+\n1 row in set<\/code><\/pre>\n<h2>\u91cd\u590d\u5b57\u7b26\u4e32repeat(string,x)<\/h2>\n<p>\u5c06string\u91cd\u590dx\u6b21\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select repeat(&#039;xy&#039;,3),length(repeat(&#039; &#039;,3)),repeat(&#039;0&#039;,3);\n+----------------+-----------------------+---------------+\n| repeat(&#039;xy&#039;,3) | length(repeat(&#039; &#039;,3)) | repeat(&#039;0&#039;,3) |\n+----------------+-----------------------+---------------+\n| xyxyxy         |                     3 | 000           |\n+----------------+-----------------------+---------------+\n1 row in set<\/code><\/pre>\n<h2>\u5b57\u7b26\u4e32\u66ff\u6362\u51fd\u6570replace(string,a,b)<\/h2>\n<p>\u4f7f\u7528\u5b57\u7b26\u4e32b\u66ff\u6362\u5b57\u7b26\u4e32string\u4e2d\u6240\u6709\u7684\u5b57\u7b26\u4e32a\u3002\u6ce8\u610f\u70b9\u662f\u5b83\u4eec\u90fd\u53ef\u4ee5\u662f\u5b57\u7b26\u4e32\u3002\u5982\u679c\u60f3\u8981\u66ff\u6362\u6389\u7684\u5b57\u7b26\u4e32a\u4e0d\u5728string\u4e2d\uff0c\u5219\u4e0d\u4f1a\u8fdb\u884c\u66ff\u6362\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select replace(&#039;woshiDanielWang&#039;,&#039;s&#039;,&#039;xxxx&#039;),replace(&#039;woshiDanielWang&#039;,&#039;ob&#039;,&#039;xxxx&#039;);\n+----------------------------------------+-----------------------------------------+\n| replace(&#039;woshiDanielWang&#039;,&#039;s&#039;,&#039;xxxx&#039;) | replace(&#039;woshiDanielWang&#039;,&#039;ob&#039;,&#039;xxxx&#039;) |\n+----------------------------------------+-----------------------------------------+\n| woxxxxhiDanielWang                    | woshiDanielWang                        |\n+----------------------------------------+-----------------------------------------+\n1 row in set<\/code><\/pre>\n<h2>\u5b57\u7b26\u4e32\u63d2\u5165\u66ff\u6362\u51fd\u6570insert(string,p1,len,instead_string)<\/h2>\n<p>\u5c06string\u4ece\u4f4d\u7f6ep1\u5f00\u59cb,len\u4e2a\u957f\u5ea6\u7684\u5b57\u7b26\u66ff\u6362\u4e3ainstead_string\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select insert(&#039;woshiDanielWang&#039;,6,2,&#039;gao&#039;);\n+--------------------------------------+\n| insert(&#039;woshiDanielWang&#039;,6,2,&#039;gao&#039;) |\n+--------------------------------------+\n| woshigaonielWang                    |\n+--------------------------------------+\n1 row in set<\/code><\/pre>\n<h2>\u5b57\u7b26\u4e32\u63d0\u53d6substring(string,x,y)<\/h2>\n<p>\u8fd4\u56destring\u4e2d\u4ecex\u4f4d\u7f6e\u5f00\u59cby\u4e2a\u957f\u5ea6\u7684\u5b57\u7b26\u4e32\u3002\u5982\u679c\u7ed9\u51fa\u7684\u4f4d\u7f6e\u4e0d\u5b58\u5728\uff0c\u5219\u65e0\u6cd5\u63d0\u53d6\u6240\u4ee5\u8fd4\u56de\u7a7a\u3002\u5982\u679c\u7ed9\u51fa\u7684\u957f\u5ea6\u8d85\u51fa\uff0c\u5219\u53ea\u63d0\u53d6\u5141\u8bb8\u8303\u56f4\u5185\u7684\u5b57\u7b26\u4e32\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select substring(&#039;AAAA&#039;,3,4) AS A,substring(&#039;AAAA&#039;,0,4) AS B,substring(&#039;AAAA&#039;,10,4) AS C,length(substring(&#039;AAAA&#039;,3,10)) AS D;\n+----+---+---+---+\n| A  | B | C | D |\n+----+---+---+---+\n| AA |   |   | 2 |\n+----+---+---+---+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h2>\u5b57\u7b26\u4e32\u6bd4\u8f83\u51fd\u6570strcmp(string1,string2)<\/h2>\n<p>\u6bd4\u8f83string1\u548cstring2\u7684ascii\u7801\u5927\u5c0f\uff0c\u4ece\u524d\u5411\u540e\u4f9d\u6b21\u6bd4\u8f83\u3002strcmp\u8ba4\u4e3a\u5927\u5c0f\u5199\u5b57\u6bcd\u662f\u7b49\u4ef7\u7684\uff0c\u6240\u4ee5\u5b83\u4eec\u76f8\u7b49\u3002\u4e14\u5b58\u5728null\u65f6\uff0c\u76f4\u63a5\u8fd4\u56denull\u3002<\/p>\n<ul>\n<li>\u5982\u679cstring1\u5c0f\u4e8estring2\uff0c\u8fd4\u56de-1\u3002<\/li>\n<li>\u5982\u679cstring1\u7b49\u4e8estring2\uff0c\u8fd4\u56de0\u3002<\/li>\n<li>\u5982\u679cstring1\u5927\u4e8estring2\uff0c\u8fd4\u56de1\u3002<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">mysql&gt; select strcmp(&#039;a&#039;,&#039;b&#039;),strcmp(&#039;a&#039;,&#039;A&#039;),strcmp(&#039;b&#039;,&#039;a&#039;);\n+-----------------+-----------------+-----------------+\n| strcmp(&#039;a&#039;,&#039;b&#039;) | strcmp(&#039;a&#039;,&#039;A&#039;) | strcmp(&#039;b&#039;,&#039;a&#039;) |\n+-----------------+-----------------+-----------------+\n|              -1 |               0 |               1 |\n+-----------------+-----------------+-----------------+\n1 row in set\nmysql&gt; select strcmp(&#039;ac&#039;,&#039;ab&#039;),strcmp(&#039;ac&#039;,&#039;ac&#039;),strcmp(&#039;a&#039;,null),strcmp(null,&#039;a&#039;);\n+-------------------+-------------------+------------------+------------------+\n| strcmp(&#039;ac&#039;,&#039;ab&#039;) | strcmp(&#039;ac&#039;,&#039;ac&#039;) | strcmp(&#039;a&#039;,null) | strcmp(null,&#039;a&#039;) |\n+-------------------+-------------------+------------------+------------------+\n|                 1 |                 0 | NULL             | NULL             |\n+-------------------+-------------------+------------------+------------------+\n1 row in set<\/code><\/pre>\n<p>\u5173\u4e8e\u5b57\u7b26\u4e32\u6bd4\u8f83\uff0c\u53e6\u5916\u4e24\u4e2a\u51fd\u6570least()\u548cgreatest()\u4e5f\u80fd\u5b9e\u73b0\uff0c\u8fd9\u4e24\u4e2a\u51fd\u6570\u66f4\u591a\u7684\u7528\u4e8e\u53d6\u6700\u503c\uff0c\u7279\u522b\u662f\u7528\u4e8e\u6570\u503c\u6bd4\u8f83\uff0c\u6240\u4ee5\u5728\u540e\u6587\u89e3\u91ca\u3002<\/p>\n<h2>\u5b57\u7b26\u4e32\u957f\u5ea6\u51fd\u6570length(string)\u548cchar_length(string)<\/h2>\n<p><strong>length()\u8fd4\u56de\u5b57\u7b26\u4e32\u7684\u5b57\u8282\u6570\uff0c\u6ce8\u610f\u4e0d\u662f\u5b57\u7b26\u6570\uff0cchar_length()\u8fd4\u56de\u7684\u624d\u662f\u5b57\u7b26\u6570<\/strong>\u3002\u5728SQL Server\u4e2d\u957f\u5ea6\u51fd\u6570\u662flen(string)\uff0c\u4e14\u8fd4\u56de\u7684\u662f\u5b57\u7b26\u6570\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select length(&#039;woshiyigeren&#039;),length(&#039;\u6211&#039;);\n+------------------------+--------------+\n| length(&#039;woshiyigeren&#039;) | length(&#039;\u6211&#039;) |\n+------------------------+--------------+\n|                     12 |            3 |\n+------------------------+--------------+\n1 row in set\n\nmysql&gt; select char_length(&#039;woshiyigeren&#039;),char_length(&#039;\u6211&#039;);\n+-----------------------------+-------------------+\n| char_length(&#039;woshiyigeren&#039;) | char_length(&#039;\u6211&#039;) |\n+-----------------------------+-------------------+\n|                          12 |                 1 |\n+-----------------------------+-------------------+\n1 row in set<\/code><\/pre>\n<p>\u5728SQL Server\u4e2d\uff1a<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/img.danielw.top\/733013-20171026211131773-1866785564.png\" alt=\"img\" \/><\/p>\n<h2>\u5b57\u7b26\u4e32\u4f4d\u7f6e\u51fd\u6570locate(sub_str,string)\u3001position(sub_str in string)\u548cinstr(str,sub_str)<\/h2>\n<p>\u8fd9\u4e09\u4e2a\u51fd\u6570\u7684\u4f5c\u7528\u76f8\u540c\uff0c\u90fd\u662f\u8fd4\u56desub_str\u5728string\u4e2d\u7684\u5f00\u59cb\u4f4d\u7f6e\u3002\u548cSQL Server\u4e2d\u7684charindex()\u51fd\u6570\u529f\u80fd\u7c7b\u4f3c\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; SELECT LOCATE(&#039;ball&#039;,&#039;football&#039;),POSITION(&#039;ball&#039; IN &#039;football&#039;) ,INSTR(&#039;football&#039;,&#039;ball&#039;);\n+---------------------------+--------------------------------+--------------------------+\n| LOCATE(&#039;ball&#039;,&#039;football&#039;) | POSITION(&#039;ball&#039; IN &#039;football&#039;) | INSTR(&#039;football&#039;,&#039;ball&#039;) |\n+---------------------------+--------------------------------+--------------------------+\n|                         5 |                              5 |                        5 |\n+---------------------------+--------------------------------+--------------------------+\n1 row in set<\/code><\/pre>\n<h2>\u5b57\u7b26\u4e32\u4f4d\u7f6e\u51fd\u6570find_in_set(sub_string,str_set)<\/h2>\n<p>\u8fd4\u56de\u5b50\u4e32sub_string\u5728str_set\u4e2d\u7684\u4f4d\u7f6e\uff0c\u5176\u4e2dstr_set\u662f\u4e00\u4e2a\u7531\u9017\u53f7\u9694\u5f00\u7684\u591a\u4e2a\u5b57\u7b26\u4e32\u96c6\u5408\u3002\u5982\u679c\u627e\u4e0d\u5230\u4f4d\u7f6e(sub_str\u4e0d\u5728str_set\u4e2d\u6216\u8005str_set\u4e3a\u7a7a\u4e32)\u5219\u8fd4\u56de0\uff0c\u5982\u679c\u4efb\u610f\u4e00\u4e2a\u4e3anull\uff0c\u5219\u8fd4\u56denull\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select find_in_set(&#039;ab&#039;,&#039;cd,ab,dc&#039;),find_in_set(&#039;ab&#039;,&#039;&#039;),find_in_set(null,&#039;ab,cd&#039;);\n+------------------------------+----------------------+---------------------------+\n| find_in_set(&#039;ab&#039;,&#039;cd,ab,dc&#039;) | find_in_set(&#039;ab&#039;,&#039;&#039;) | find_in_set(null,&#039;ab,cd&#039;) |\n+------------------------------+----------------------+---------------------------+\n|                            2 |                    0 | NULL                      |\n+------------------------------+----------------------+---------------------------+\n1 row in set<\/code><\/pre>\n<h2>\u5b57\u7b26\u4e32\u4f4d\u7f6e\u51fd\u6570field(s,str1,str2,...,strN)<\/h2>\n<p>\u8fd4\u56de\u5b57\u7b26\u4e32s\u5728\u5b57\u7b26\u4e32\u96c6\u5408str1,str2,...,strN\u4e2d\u7684\u4f4d\u7f6e\u3002\u5982\u679c\u627e\u4e0d\u5230\u6216\u8005\u5b57\u7b26\u4e32s\u4e3anull\uff0c\u5219\u8fd4\u56de0\uff0c\u56e0\u4e3anull\u65e0\u6cd5\u8fdb\u884c\u6bd4\u8f83\uff0c\u4e5f\u5c31\u662f\u627e\u4e0d\u5230\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select field(&#039;ab&#039;,&#039;abc&#039;,&#039;1ab&#039;,&#039;ab&#039;,&#039;cd&#039;) as col1,field(null,&#039;ab&#039;,&#039;cd&#039;) as col2;\n+------+------+\n| col1 | col2 |\n+------+------+\n|    3 |    0 |\n+------+------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h2>\u6307\u5b9a\u4f4d\u7f6e\u7684\u5b57\u7b26\u4e32\u51fd\u6570elt(n,str1,str2,...,strN)<\/h2>\n<p>elt\u8868\u793a\u4ece(\u6570\u636e)\u4ed3\u5e93\u4e2d\u63d0\u53d6\u9700\u8981\u7684\u4e1c\u897f\u3002n\u662f\u4f4d\u7f6e\uff0cn=1\u5219\u8fd4\u56destr1\uff0cn=2\u5219\u8fd4\u56destr2\uff0c\u4f9d\u6b21\u7c7b\u63a8\u3002\u5f53n&lt;1\u6216\u8005\u5927\u4e8e\u5b57\u7b26\u4e32\u7684\u6570\u91cf\uff0c\u5219\u8fd4\u56denull\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select elt(1,&#039;a&#039;,&#039;b&#039;,&#039;c&#039;),elt(2,&#039;a&#039;,&#039;b&#039;,&#039;c&#039;),elt(0,&#039;a&#039;,&#039;b&#039;),elt(10,&#039;a&#039;,&#039;b&#039;);\n+--------------------+--------------------+----------------+-----------------+\n| elt(1,&#039;a&#039;,&#039;b&#039;,&#039;c&#039;) | elt(2,&#039;a&#039;,&#039;b&#039;,&#039;c&#039;) | elt(0,&#039;a&#039;,&#039;b&#039;) | elt(10,&#039;a&#039;,&#039;b&#039;) |\n+--------------------+--------------------+----------------+-----------------+\n| a                  | b                  | NULL           | NULL            |\n+--------------------+--------------------+----------------+-----------------+\n1 row in set<\/code><\/pre>\n<h2>\u5b57\u7b26\u4e32\u53cd\u8f6c\u51fd\u6570reverse(str)<\/h2>\n<p>\u53cd\u8f6c\u5b57\u7b26\u4e32str\u7684\u5b57\u7b26\u987a\u5e8f\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select reverse(&#039;hello&#039;);\n+------------------+\n| reverse(&#039;hello&#039;) |\n+------------------+\n| olleh            |\n+------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h1>\u6570\u5b66\u51fd\u6570<\/h1>\n<p>\u5b8c\u6574\u7684\u5185\u7f6e\u6570\u5b66\u51fd\u6570\u89c1<a href=\"https:\/\/mariadb.com\/kb\/en\/numeric-functions\/\" target=\"_blank\"  rel=\"nofollow\" >\u5b98\u65b9\u624b\u518c<\/a>\u3002<\/p>\n<h2>\u7edd\u5bf9\u503c\u51fd\u6570ABS(x)<\/h2>\n<pre><code class=\"language-sql\">mysql&gt; select abs(0.9),abs(0),abs(-0.9);\n+----------+--------+-----------+\n| abs(0.9) | abs(0) | abs(-0.9) |\n+----------+--------+-----------+\n| 0.9      |      0 | 0.9       |\n+----------+--------+-----------+\n1 row in set<\/code><\/pre>\n<h2>\u53d6\u6a21\u51fd\u6570mod(x,y)<\/h2>\n<p>\u53d6x\/y\u540e\u7684\u4f59\u6570\u3002\u652f\u6301\u5c0f\u6570\u548c\u8d1f\u6570\u3002\u5982\u679c\u9664\u6570\u4e3a0\u6216\u8005\u9664\u6570\u88ab\u9664\u6570\u6709\u4e00\u4e2a\u4e3anull\uff0c\u5219\u8fd4\u56denull\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select mod(31,8),mod(31.56,8),mod(-31.56,8),mod(31,0),mod(0,8);\n+-----------+--------------+---------------+-----------+----------+\n| mod(31,8) | mod(31.56,8) | mod(-31.56,8) | mod(31,0) | mod(0,8) |\n+-----------+--------------+---------------+-----------+----------+\n|         7 | 7.56         | -7.56         | NULL      |        0 |\n+-----------+--------------+---------------+-----------+----------+\n1 row in set<\/code><\/pre>\n<h2>\u56db\u820d\u4e94\u5165\u51fd\u6570round(x,y)<\/h2>\n<p>\u8fd4\u56de\u503cx\u542b\u6709y\u4f4d\u5c0f\u6570\u7684\u56db\u820d\u4e94\u5165\u540e\u7684\u7ed3\u679c\uff0c\u5982\u679c\u7701\u7565y\uff0c\u5219\u9ed8\u8ba4y\u4e3a0\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select round(3.15),round(3.15,1),round(-3.15),round(-3.15,1);\n+-------------+---------------+--------------+----------------+\n| round(3.15) | round(3.15,1) | round(-3.15) | round(-3.15,1) |\n+-------------+---------------+--------------+----------------+\n| 3           | 3.2           | -3           | -3.2           |\n+-------------+---------------+--------------+----------------+\n1 row in set<\/code><\/pre>\n<h2>\u4f4d\u6570\u622a\u65ad\u51fd\u6570truncate(x,y)<\/h2>\n<p>\u622a\u65adx\u7684\u5c0f\u6570\u4f4d\u6570\u4f7f\u5f97\u6700\u7ec8\u4fdd\u7559y\u4e2a\u5c0f\u6570\u4f4d\u3002\u5b83\u7684\u7528\u6cd5\u548cround(x,y)\u51e0\u4e4e\u4e00\u6837\uff0c\u53ea\u4e0d\u8fc7truncate\u662f\u7528\u6765\u622a\u65ad\u800c\u4e0d\u7528\u6765\u56db\u820d\u4e94\u5165\u3002\u4e0d\u80fd\u7701\u7565y\u4f46\u53ef\u4ee5\u7b49\u4e8e0\uff0c\u4e14y\u4e0d\u80fd\u4e3a\u8d1f\u6570\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select truncate(3.156,2),truncate(3.156,0);\n+-------------------+-------------------+\n| truncate(3.156,2) | truncate(3.156,0) |\n+-------------------+-------------------+\n| 3.15              | 3                 |\n+-------------------+-------------------+\n1 row in set<\/code><\/pre>\n<h2>\u5730\u677f\u51fd\u6570floor(x)\u548c\u5929\u82b1\u677f\u51fd\u6570ceiling(x)<\/h2>\n<p>\u5730\u677f\u51fd\u6570\u8fd4\u56de\u6bd4x\u5c0f\u7684\u6700\u5927\u6574\u6570\uff0c\u5929\u82b1\u677f\u51fd\u6570\u8fd4\u56de\u6bd4x\u5927\u7684\u6700\u5c0f\u6574\u6570\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select floor(3.4),floor(-3.4),ceiling(3.4),ceiling(-3.4);\n+------------+-------------+--------------+---------------+\n| floor(3.4) | floor(-3.4) | ceiling(3.4) | ceiling(-3.4) |\n+------------+-------------+--------------+---------------+\n|          3 |          -4 |            4 |            -3 |\n+------------+-------------+--------------+---------------+\n1 row in set<\/code><\/pre>\n<h2>\u968f\u673a\u51fd\u6570rand()<\/h2>\n<p>\u6bcf\u6b21\u968f\u673a\u8fd4\u56de\u4e00\u4e2a0-1\u4e4b\u95f4\u4e0d\u5305\u62ec0\u548c1\u7684\u6570\uff0c\u4e14\u6bcf\u6b21\u8fd0\u884c\u7ed3\u679c\u90fd\u4e0d\u540c\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select rand(),rand();\n+--------------------+----------------------+\n| rand()             | rand()               |\n+--------------------+----------------------+\n| 0.7380041170287915 | 0.055543343588284534 |\n+--------------------+----------------------+\n1 row in set<\/code><\/pre>\n<p>\u82e5\u8981\u53d6\u5f970-100\u4e4b\u95f4\u7684\u6570\uff0c\u53ef\u4ee5\u4f7f\u7528100\u53bb\u4e58\u968f\u673a\u503c\uff0c\u4f46\u8fd9\u6837\u83b7\u5f97\u7684\u51fd\u6570\u8fd8\u662f\u4e0d\u5305\u542b0\u548c100\u8fd9\u4e24\u4e2a\u8fb9\u754c\u7684\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select 100*rand(),100*rand(),100*rand();\n+------------------+-------------------+--------------------+\n| 100*rand()       | 100*rand()        | 100*rand()         |\n+------------------+-------------------+--------------------+\n| 22.5249471352668 | 96.80735235736458 | 16.461923454387044 |\n+------------------+-------------------+--------------------+\n1 row in set<\/code><\/pre>\n<p>\u82e5\u8981\u53d6\u6574\uff0c\u5219\u53ef\u4ee5\u914d\u5408floor()\u6216\u8005ceiling()\u51fd\u6570\u3002\u4f46\u8fd9\u6837\u53d6\u5f97\u7684\u662f[0,99]\u6216\u8005[1,100]\uff0c\u800c\u4e0d\u80fd\u662f[0,100]\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select floor(100*rand()) as &#039;[0,99]&#039;,ceiling(100*rand()) as &#039;[1,100]&#039;;\n+--------+---------+\n| [0,99] | [1,100] |\n+--------+---------+\n|     90 |      24 |\n+--------+---------+\n1 row in set<\/code><\/pre>\n<p>\u5982\u679c\u8981\u60f3\u83b7\u5f97[0-100]\u8fd9\u6837\u5305\u542b\u8fb9\u754c\u7684\u503c\uff0c\u53ef\u4ee5\u62d3\u5bbd\u968f\u673a\u503c\u3002\u4ee5\u4e0b\u662f\u4e24\u79cd\u65b9\u6cd5\uff1a<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select ceiling(rand()*101-1),floor(rand()*101);\n+-----------------------+-------------------+\n| ceiling(rand()*101-1) | floor(rand()*101) |\n+-----------------------+-------------------+\n|                    92 |                55 |\n+-----------------------+-------------------+\n1 row in set<\/code><\/pre>\n<h2>\u6700\u503c\u51fd\u6570least(v1,v2,v3,\u2026,vn)<\/h2>\n<p>\u4ecev1,v2,v3,\u2026,vn\u4e2d\u53d6\u51fa\u6700\u5c0f\u503c\u3002\u6709\u4ee5\u4e0b\u51e0\u79cd\u60c5\u51b5\uff1a<br \/>\n(1)\u5f53\u53ea\u6709\u6570\u503c\u7c7b\u578b\u65f6\uff0c\u53d6\u6570\u503c\u6700\u5c0f\u7684\u3002\u4e14\u8d1f\u6570\u6709\u6548\u3002<br \/>\n(2)\u5f53\u53ea\u6709\u5b57\u7b26\u4e32\u65f6\uff0c\u4ece\u7b2c\u4e00\u4e2a\u5b57\u7b26\u5f00\u59cb\u5411\u540e\u6bd4\u8f83ascii\u7801\uff0c\u5c0f\u5199\u5b57\u6bcd\u5c0f\u4e8e\u5927\u5199\u5b57\u6bcd\u3002<br \/>\n(3)\u6570\u503c\u548c\u5b57\u7b26\u4e32\u6bd4\u8f83\uff0c\u8fd4\u56de\u7ed3\u679c\u4e3a0\u3002\u82e5\u8981\u6bd4\u8f83\uff0c\u9700\u8981\u5148\u5c06\u6570\u5b57\u8f6c\u6362\u4e3a\u5b57\u7b26\u4e32\u683c\u5f0f\uff0c\u4e14\u5b57\u7b26\u4e32\u7c7b\u578b\u7684\u6570\u5b57\u603b\u662f\u5c0f\u4e8e\u5b57\u6bcd\u3002<br \/>\n(4)\u5f53n\u4e2a\u6210\u5458\u4e4b\u95f4\u5b58\u5728null\u7684\u65f6\u5019\uff0c\u603b\u662f\u8fd4\u56denull\uff0c\u56e0\u4e3a\u65e0\u6cd5\u6bd4\u8f83\u3002  <\/p>\n<pre><code class=\"language-sql\">mysql&gt; select least(5,10,-1),least(&#039;ab&#039;,&#039;c&#039;,&#039;ac&#039;),least(&#039;a&#039;,1),least(&#039;a&#039;,&#039;999&#039;),least(&#039;a&#039;,1,null);\n+----------------+----------------------+--------------+------------------+-------------------+\n| least(5,10,-1) | least(&#039;ab&#039;,&#039;c&#039;,&#039;ac&#039;) | least(&#039;a&#039;,1) | least(&#039;a&#039;,&#039;999&#039;) | least(&#039;a&#039;,1,null) |\n+----------------+----------------------+--------------+------------------+-------------------+\n|             -1 | ab                   | 0            | 999              | NULL              |\n+----------------+----------------------+--------------+------------------+-------------------+\n1 row in set<\/code><\/pre>\n<h2>\u6700\u503c\u51fd\u6570greatest(v1,v2,v3,\u2026,vn)<\/h2>\n<p>\u548cleast()\u51fd\u6570\u76f8\u53cd\uff0c\u5b83\u53d6\u7684\u662f\u6700\u5927\u503c\u3002\u5305\u62ec\u4ee5\u4e0b\u51e0\u79cd\u60c5\u51b5\uff1a<br \/>\n(1)\u5f53\u53ea\u6709\u6570\u503c\u7c7b\u578b\u65f6\uff0c\u53d6\u6700\u5927\u503c\u3002\u8d1f\u503c\u6709\u6548\u3002<br \/>\n(2)\u5f53\u53ea\u6709\u5b57\u7b26\u4e32\u65f6\uff0c\u6bd4\u8f83ascii\u7801\uff0c\u5927\u5199\u5b57\u6bcd\u5927\u4e8e\u5c0f\u5199\u5b57\u6bcd\u3002<br \/>\n(3)\u5f53\u6570\u5b57\u548c\u5b57\u7b26\u4e32\u6bd4\u8f83\u65f6\uff0c\u6570\u5b57\u5927\u4e8e\u5b57\u7b26\u4e32\uff0c\u5373\u8fd4\u56de\u6570\u5b57\u4e2d\u6700\u5927\u503c\u3002\u4f46\u662f\u5b57\u7b26\u4e32\u7c7b\u578b\u7684\u6570\u5b57\u5c0f\u4e8e\u5b57\u6bcd\u3002\u8fd9\u4e2aleast()\u4e0d\u4e00\u6837\u3002<br \/>\n(4)\u5f53\u5b58\u5728null\u503c\u65f6\uff0c\u8fd4\u56denull\u3002  <\/p>\n<pre><code class=\"language-sql\">mysql&gt; select greatest(5,10,-1) as A,\n              greatest(&#039;ab&#039;,&#039;c&#039;,&#039;ac&#039;) as B,\n              greatest(&#039;a&#039;,1) as C,\n              greatest(&#039;a&#039;,&#039;999&#039;) as D,\n              greatest(&#039;a&#039;,1,null) as E;\n+----+---+---+---+------+\n| A  | B | C | D | E    |\n+----+---+---+---+------+\n| 10 | c | 1 | a | NULL |\n+----+---+---+---+------+\n1 row in set, 2 warnings (0.00 sec)<\/code><\/pre>\n<h1>\u65e5\u671f\u65f6\u95f4\u51fd\u6570<\/h1>\n<p>\u6709\u5f88\u591a\u5f88\u591a\uff0c<a href=\"https:\/\/mariadb.com\/kb\/en\/library\/day\/\" target=\"_blank\"  rel=\"nofollow\" >\u5b98\u65b9\u624b\u518c\uff1a\u65e5\u671f\u65f6\u95f4\u51fd\u6570<\/a>\u3002\u4ee5\u4e0b\u6311\u51e0\u4e2a\u4ecb\u7ecd\u3002<\/p>\n<h2>\u5f53\u524d\u65e5\u671f\u65f6\u95f4<\/h2>\n<p>\u8fd4\u56de\u5f53\u524d\u65e5\u671f\uff1acurdate()\u3001current_date()\uff0c\u5b83\u4eec\u662f\u540c\u4e49\u8bcd\uff1b<br \/>\n\u8fd4\u56de\u5f53\u524d\u65f6\u95f4\uff1acurtime()\u3001current_time()\uff0c\u5b83\u4eec\u662f\u540c\u4e49\u8bcd\uff1b<br \/>\n\u8fd4\u56de\u5f53\u524d\u65e5\u671f\u65f6\u95f4\uff1anow()\u3001current_timestamp()\u3001localtime()\u3001localtimestamp\u3001localtimestamp()\u3001sysdate()\uff0c\u9664\u4e86sysdate()\uff0c\u5176\u4f59\u7684\u90fd\u662fnow()\u7684\u540c\u4e49\u8bcd\u3002  <\/p>\n<pre><code class=\"language-sql\">mysql&gt; select curdate(),current_date(),current_timestamp(),curtime(),localtime(),now(),sysdate();<\/code><\/pre>\n<p>\u6ce8\u610f\uff0cnow()\u548csysdate()\u662f\u4e0d\u540c\u7684\u3002now()\u8fd4\u56de\u7684\u662f\u6267\u884cSQL\u8bed\u53e5\u90a3\u4e00\u523b\u7684\u65f6\u95f4(\u5982\u679cnow()\u662f\u5728\u5b58\u50a8\u8fc7\u7a0b\u6216\u51fd\u6570\u6216\u89e6\u53d1\u5668\u4e2d\uff0c\u5219now()\u8fd4\u56de\u7684\u662f\u8fd9\u4e9b\u7a0b\u5e8f\u5f00\u59cb\u8c03\u7528\u6267\u884c\u7684\u65f6\u523b)\uff0c\u800csysdate()\u8fd4\u56de\u7684\u662f\u5b9e\u65f6\u66f4\u65b0\u7684\u5f53\u524d\u65f6\u95f4\uff0c\u5373\u64cd\u4f5c\u7cfb\u7edf\u5f53\u524d\u7684\u65f6\u95f4\u3002\u901a\u8fc7\u4e0b\u9762\u7684\u4f8b\u5b50\u5c31\u77e5\u9053\u4e86\uff1a<\/p>\n<pre><code class=\"language-sql\">mysql&gt; SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),LOCALTIME(),SLEEP(2),NOW(),CURRENT_TIMESTAMP(),SYSDATE(),LOCALTIME()\\G\n*************************** 1. row ***************************\n              NOW(): 2022-04-11 16:47:06\nCURRENT_TIMESTAMP(): 2022-04-11 16:47:06\n          SYSDATE(): 2022-04-11 16:47:06\n        LOCALTIME(): 2022-04-11 16:47:06\n           SLEEP(2): 0\n              NOW(): 2022-04-11 16:47:06\nCURRENT_TIMESTAMP(): 2022-04-11 16:47:06\n          SYSDATE(): 2022-04-11 16:47:08    # \u6ce8\u610f\u6b64\u5904sleep 2\u79d2\u540e\u7684\u65f6\u95f4\n        LOCALTIME(): 2022-04-11 16:47:06\n1 row in set (2.01 sec)<\/code><\/pre>\n<p>\u53ef\u4ee5\u770b\u5230\uff0csleep(2)\u540e\uff0csysdate()\u8fd4\u56de\u7684\u6bd4\u5176\u4ed6\u7684\u51fd\u6570\u665a\u4e86\u4e24\u79d2\uff0c\u800c\u5176\u4ed6\u7684\u51fd\u6570\u8fd4\u56de\u7684\u548csleep(2)\u4e4b\u524d\u7684\u65f6\u95f4\u662f\u4e00\u6837\u7684\uff0c\u4e14\u90fd\u662f\u5f00\u59cb\u6267\u884c\u8bed\u53e5\u7684\u65f6\u95f4\u3002<\/p>\n<h2>week(DATE)<\/h2>\n<p>\u8fd4\u56de\u7ed9\u5b9a\u65e5\u671f\u5728\u5f53\u5e74\u662f\u7b2c\u51e0\u5468\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select week(now());\n+-------------+\n| week(now()) |\n+-------------+\n|          12 |\n+-------------+\n1 row in set<\/code><\/pre>\n<h2>year(DATE)\u3001monthname(DATE)\u3001day(DATE)\u3001quarter(DATE)<\/h2>\n<p>\u8fd4\u56de\u6240\u7ed9\u65e5\u671f\u7684\u5e74\u4efd\u3001\u6708\u4efd\u3001\u6708\u4e2d\u5929(\u6240\u4ee5day()\u7684\u540c\u4e49\u8bcd\u662fdayofmonth()\u51fd\u6570)\u4ee5\u53ca\u5b63\u5ea6\uff0c\u4e0d\u8fc7\u8fd4\u56de\u7684\u6708\u4efd\u662f\u82f1\u6587\u5168\u540d\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select year(now()),monthname(now());\n+-------------+------------------+\n| year(now()) | monthname(now()) |\n+-------------+------------------+\n|        2022 | April            |\n+-------------+------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h2>hour(TIME)\u3001minute(TIME)\u3001second(TIME)<\/h2>\n<p>\u8fd4\u56de\u7ed9\u5b9a\u65f6\u95f4\u503c\u7684\u5c0f\u65f6\u3001\u5206\u949f\u3001\u79d2\u90e8\u5206\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select now(),hour(now()),minute(now()),second(now());\n+---------------------+-------------+---------------+---------------+\n| now()               | hour(now()) | minute(now()) | second(now()) |\n+---------------------+-------------+---------------+---------------+\n| 2022-04-11 16:49:45 |          16 |            49 |            45 |\n+---------------------+-------------+---------------+---------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h2>extract(part from DATE)<\/h2>\n<p>\u4ece\u7ed9\u5b9a\u7684DATETIME\u4e2d\u63d0\u53d6\u79d2(second)\u3001\u5206(minute)\u3001\u65f6(hour)\u3001\u65e5(day)\u3001\u6708(month)\u3001\u5468(week)\u3001\u5e74(year)\uff0c\u8fd8\u652f\u6301\u5b63\u5ea6(quarter)\u63d0\u53d6\u3002\u548cSQL Server\u4e2d\u7684datepart()\u51fd\u6570\u4e00\u6837\u7684\u529f\u80fd\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select extract(year from now()) as year_part,\n              extract(month from now()) as month_part,\n              extract(day from now()) as day_part,\n              extract(week from now()) as week_part;\n+-----------+------------+----------+-----------+\n| year_part | month_part | day_part | week_part |\n+-----------+------------+----------+-----------+\n|      2022 |          4 |       11 |        15 |\n+-----------+------------+----------+-----------+\n1 row in set (0.00 sec)\n\nmysql&gt; select now(),extract(hour from now()) as hour_part,\n              extract(minute from now()) as minute_part,\n              extract(second from now()) as second_part;\n+---------------------+-----------+-------------+-------------+\n| now()               | hour_part | minute_part | second_part |\n+---------------------+-----------+-------------+-------------+\n| 2022-04-11 16:50:51 |        16 |          50 |          51 |\n+---------------------+-----------+-------------+-------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h2>dayname(DATE)\u548cdayofweek(DATE)<\/h2>\n<p>dayname\u8fd4\u56de\u7ed9\u5b9a\u65e5\u671f\u662f\u661f\u671f\u51e0\uff0c\u8fd4\u56de\u7684\u5468\u65e5\u671fname\u7684\u90fd\u662f\u82f1\u6587\u5168\u540d\u3002\u800cdayofweek\u8fd4\u56de\u7684\u662f\u6570\u5b57\u4ee3\u8868\u7684\u661f\u671f\u51e0\uff0c1\u8868\u793a\u5468\u65e5\uff0c7\u8868\u793a\u5468\u516d\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select dayname(20131111),dayofweek(&#039;20131111&#039;);\n+-------------------+-----------------------+\n| dayname(20131111) | dayofweek(&#039;20131111&#039;) |\n+-------------------+-----------------------+\n| Monday            |                     2 |\n+-------------------+-----------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h2>\u65e5\u671f\u65f6\u95f4\u683c\u5f0f\u5316<\/h2>\n<p>\u65e5\u671f\u683c\u5f0f\u5316\uff1adate_format(date,fmt)<br \/>\n\u65f6\u95f4\u683c\u5f0f\u5316\uff1atime_format(time,fmt)  <\/p>\n<p>\u5176\u4e2dfmt\u4e3a\u65e5\u671f\u65f6\u95f4\u7684\u63cf\u8ff0\u683c\u5f0f\uff0c\u4f7f\u7528%\u5f00\u5934\u8fdb\u884c\u63cf\u8ff0\uff0c\u4f8b\u5982%Y\u8868\u793a4\u4f4d\u6570\u5b57\u7684\u5e74\u4efd\uff0c%m\u8868\u793a2\u4f4d\u6570\u5b57\u7684\u6708\u4efd\u7b49\uff0c\u66f4\u591a\u7684\u683c\u5f0f\u89c1\u5b98\u65b9\u624b\u518c<a href=\"https:\/\/mariadb.com\/kb\/en\/library\/date_format\/#description\" target=\"_blank\"  rel=\"nofollow\" >fmt<\/a><\/p>\n<pre><code class=\"language-sql\">mysql&gt; select date_format(&#039;20131012&#039;,&#039;%Y-%m-%d&#039;);\n+------------------------------------+\n| date_format(&#039;20131012&#039;,&#039;%Y-%m-%d&#039;) |\n+------------------------------------+\n| 2013-10-12                         |\n+------------------------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h2>\u65e5\u671f\u65f6\u95f4\u8ba1\u7b97<\/h2>\n<p>\u589e\u52a0\u65e5\u671f\uff1aDATE_ADD(date,interval expr unit)\uff0cADDDATE(date,interval expr unit)\uff0c\u5b83\u4eec\u662f\u540c\u4e49\u8bcd\uff1b<br \/>\n\u51cf\u53bb\u65e5\u671f\uff1aDATE_SUB(date,interval expr unit)\uff0cSUBDATE(date,interval expr unit)\uff0c\u5b83\u4eec\u662f\u540c\u4e49\u8bcd\uff1b  <\/p>\n<p>\u5728\u7ed9\u5b9a\u65e5\u671fdate\u57fa\u7840\u4e0a\u52a0\u6216\u51cf\u53bb\u67d0\u79cd\u683c\u5f0f\u8868\u8fbe\u7684\u65e5\u671f\u65f6\u95f4\u3002interval\u662f\u5173\u952e\u5b57\uff0cexpr\u662f\u7528\u6765\u7ed9\u5b9a\u52a0\u51cf\u591a\u5c11\u65f6\u95f4\u7684\u8868\u8fbe\u5f0f\uff0cunit\u662fexpr\u8981\u8868\u8fbe\u7684\u65e5\u671f\u7c7b\u578b\uff0c\u89c1\u4e0b\u56fe\u3002\u5176\u4e2dexpr\u7684\u63cf\u8ff0\u65b9\u5f0f\u548cunit\u662f\u5bf9\u5e94\u7684\u3002<\/p>\n<table>\n<thead>\n<tr>\n<th>Unit<\/th>\n<th>Description<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>MICROSECOND<\/td>\n<td>Microseconds<\/td>\n<\/tr>\n<tr>\n<td>SECOND<\/td>\n<td>Seconds<\/td>\n<\/tr>\n<tr>\n<td>MINUTE<\/td>\n<td>Minutes<\/td>\n<\/tr>\n<tr>\n<td>HOUR<\/td>\n<td>Hours<\/td>\n<\/tr>\n<tr>\n<td>DAY<\/td>\n<td>Days<\/td>\n<\/tr>\n<tr>\n<td>WEEK<\/td>\n<td>Weeks<\/td>\n<\/tr>\n<tr>\n<td>MONTH<\/td>\n<td>Months<\/td>\n<\/tr>\n<tr>\n<td>QUARTER<\/td>\n<td>Quarters<\/td>\n<\/tr>\n<tr>\n<td>YEAR<\/td>\n<td>Years<\/td>\n<\/tr>\n<tr>\n<td>SECOND_MICROSECOND<\/td>\n<td>Seconds.Microseconds<\/td>\n<\/tr>\n<tr>\n<td>MINUTE_MICROSECOND<\/td>\n<td>Minutes.Seconds.Microseconds<\/td>\n<\/tr>\n<tr>\n<td>MINUTE_SECOND<\/td>\n<td>Minutes.Seconds<\/td>\n<\/tr>\n<tr>\n<td>HOUR_MICROSECOND<\/td>\n<td>Hours.Minutes.Seconds.Microseconds<\/td>\n<\/tr>\n<tr>\n<td>HOUR_SECOND<\/td>\n<td>Hours.Minutes.Seconds<\/td>\n<\/tr>\n<tr>\n<td>HOUR_MINUTE<\/td>\n<td>Hours.Minutes<\/td>\n<\/tr>\n<tr>\n<td>DAY_MICROSECOND<\/td>\n<td>Days Hours.Minutes.Seconds.Microseconds<\/td>\n<\/tr>\n<tr>\n<td>DAY_SECOND<\/td>\n<td>Days Hours.Minutes.Seconds<\/td>\n<\/tr>\n<tr>\n<td>DAY_MINUTE<\/td>\n<td>Days Hours.Minutes<\/td>\n<\/tr>\n<tr>\n<td>DAY_HOUR<\/td>\n<td>Days Hours<\/td>\n<\/tr>\n<tr>\n<td>YEAR_MONTH<\/td>\n<td>Years-Months<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u4f8b\u5982year_month\u5355\u5143\uff0c\u4ece\u4e0a\u8868\u4e2d\u5f97\u51fa\u5b83\u7684\u683c\u5f0f\u662f&quot;years  month&quot;\u8868\u793a\u8ba1\u7b97year\u90e8\u5206\u548cmonth\u90e8\u5206\u7684\u95f4\u9694\u3002expr\u4e2dyear\u548cmonth\u4e4b\u95f4\u4f7f\u7528\u4efb\u610f\u5206\u9694\u7b26\u90fd\u53ef\u4ee5\uff0c\u4f8b\u5982&quot;1_2&quot;\u3001&quot;1!2&quot;\u3001&quot;1-2&quot;\u548c&quot;1 2&quot;\u90fd\u662f\u5141\u8bb8\u7684\u3002\u5982\u679c\u4f7f\u7528day_minute\u5355\u5143\uff0c\u5b83\u7684\u610f\u4e49\u662f&quot;days  hours.minutes&quot;\uff0c\u90a3\u4e48expr\u4e2d\u5c31\u9700\u8981\u7ed9\u5b9a3\u4e2a\u503c\uff0c\u8fd93\u4e2a\u503c\u4ece\u524d\u5411\u540e\u5206\u522b\u4ee3\u8868\u65e5\u3001\u65f6\u3001\u5206\uff0c\u4e2d\u95f4\u53ef\u4ee5\u7528\u4efb\u610f\u5206\u9694\u7b26\u5206\u9694\uff0c\u4f8b\u5982'3-2-1'\u8868\u793a3\u59292\u5c0f\u65f61\u5206\u949f\u3002<\/p>\n<p>expr\u7684\u524d\u9762\u53ef\u4ee5\u52a0\u4e0a&quot;+&quot;\u548c&quot;-&quot;\uff0c\u5206\u522b\u8868\u793a\u52a0\u548c\u51cf\uff0c\u4e0d\u5199\u65f6\u9ed8\u8ba4\u4e3a&quot;+&quot;\uff0c\u6240\u4ee5date_add\u548cdate_sub\u4e4b\u95f4\u901a\u8fc7\u6b63\u8d1f\u7b26\u53f7\u662f\u53ef\u4ee5\u7b49\u4ef7\u7684\u3002<\/p>\n<p>\u4ee5\u4e0b\u662f\u793a\u4f8b\uff1a<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select now(),\n              date_add(now(),interval 31 day) as add31days,\n              date_add(now(),interval &#039;1_2&#039; year_month) as add1year2month;\n+---------------------+---------------------+---------------------+\n| now()               | add31days           | add1year2month      |\n+---------------------+---------------------+---------------------+\n| 2022-04-11 16:52:30 | 2022-05-12 16:52:30 | 2023-06-11 16:52:30 |\n+---------------------+---------------------+---------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<p>\u4e0a\u8ff0\u4f8b\u5b50\u4e2d\u4f7f\u7528\u4e86\u4e0a\u9762\u7684\u7b2c\u4e8c\u5217\u8868\u793a\u5728\u5f53\u524d\u65e5\u671f\u5185\u52a0\u4e0a31\u5929\u540e\u7684\u65f6\u95f4\uff0c\u7b2c\u4e09\u5217\u8868\u793a\u5728\u5f53\u524d\u65e5\u671f\u57fa\u7840\u4e0a\u52a0\u4e0a1\u5e74\u53c82\u4e2a\u6708\u4e4b\u540e\u7684\u65f6\u95f4\u3002<\/p>\n<p>\u5982\u679cdate_add\u4e2dexpr\u4f7f\u7528\u7684\u662f\u8d1f\u6570\uff0c\u5219\u8868\u793a\u51cf\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select now(),\n              date_add(now(),interval &#039;-31&#039; day) as jian31days,\n              date_add(now(),interval &#039;-1_2&#039; year_month) as jian1year2month;\n+---------------------+---------------------+---------------------+\n| now()               | jian31days          | jian1year2month     |\n+---------------------+---------------------+---------------------+\n| 2022-04-11 16:52:56 | 2022-03-11 16:52:56 | 2021-02-11 16:52:56 |\n+---------------------+---------------------+---------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<p>\u4e0a\u9762\u7b2c\u4e8c\u5217\u8868\u793a\u5728\u5f53\u524d\u65e5\u671f\u4e0a\u51cf\u53bb31\u5929\u540e\u7684\u65f6\u95f4\uff0c\u7b2c\u4e09\u5217\u8868\u793a\u5728\u5f53\u524d\u65e5\u671f\u57fa\u7840\u4e0a\u51cf\u53bb1\u5e74\u53c82\u4e2a\u6708\u4e4b\u540e\u7684\u65f6\u95f4\u3002<\/p>\n<h2>datediff(expr1,expr2)<\/h2>\n<p>expr1\u548cexpr2\u4e4b\u95f4\u7684\u5929\u6570\u5dee\uff0c\u662fexpr1\u51cf\u53bbexpr2\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select now(),datediff(now(),&#039;2023-01-01&#039;);\n+---------------------+------------------------------+\n| now()               | datediff(now(),&#039;2023-01-01&#039;) |\n+---------------------+------------------------------+\n| 2022-04-11 16:53:28 |                         -265 |\n+---------------------+------------------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h2>LAST_DAY(datetime)<\/h2>\n<p>\u8fd4\u56de\u7ed9\u5b9a\u65e5\u671f\u6240\u5728\u6708\u7684\u6700\u540e\u4e00\u5929\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select last_day(now()),last_day(&#039;2016-02-03&#039;);\n+-----------------+------------------------+\n| last_day(now()) | last_day(&#039;2019-02-03&#039;) |\n+-----------------+------------------------+\n| 2022-04-30      | 2019-02-28             |\n+-----------------+------------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h1>\u6d41\u7a0b\u63a7\u5236\u4e4b\u6761\u4ef6\u5224\u65ad\u51fd\u6570<\/h1>\n<p>\u5728MySQL\/MariaDB\u4e2d\u4e3b\u8981\u6709if\u3001ifnull\u548ccase\u8bed\u53e5\u8fdb\u884c\u6761\u4ef6\u5224\u65ad\u3002\u5176\u4e2dif\u8bed\u53e5\u548cSQL Server\u4e2d\u7684if\u76f8\u5dee\u8f83\u5927\u3002<\/p>\n<h2>if(expr,true_value,false_value)<\/h2>\n<p>if\u51fd\u6570\u7528\u6765\u5224\u65adexpr\u662f\u5426\u4e3a\u771f\uff0c\u5982\u679c\u4e3a\u771f\uff0c\u5219\u8fd4\u56detrue_value\uff0c\u5426\u5219\u8fd4\u56defalse_value\u3002\u8fd9\u548c<a href=\"https:\/\/mariadb.com\/kb\/en\/library\/if\/\" target=\"_blank\"  rel=\"nofollow\" >if\u8bed\u53e5<\/a>\u662f\u4e0d\u4e00\u6837\u7684\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select if(1&gt;2,&#039;a&#039;,&#039;b&#039;),if(2&gt;1,&#039;a&#039;,&#039;b&#039;);\n+-----------------+-----------------+\n| if(1&gt;2,&#039;a&#039;,&#039;b&#039;) | if(2&gt;1,&#039;a&#039;,&#039;b&#039;) |\n+-----------------+-----------------+\n| b               | a               |\n+-----------------+-----------------+\n1 row in set<\/code><\/pre>\n<p>expr\u5224\u65ad\u662f\u5426\u4e3a\u771f\u7684\u4f9d\u636e\u662fexpr\u7684\u7ed3\u679c<strong>\u975e0\u4e14\u975enull<\/strong>\u3002\u6240\u4ee5\u4e5f\u53ef\u4ee5\u76f4\u63a5\u4f7f\u7528\u6570\u5b57\u8868\u793a\u771f\u5047\uff0c\u4f46\u4e0d\u80fd\u4f7f\u7528\u5b57\u6bcd\u8868\u793a\u771f\u5047\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select if(99,&#039;a&#039;,&#039;b&#039;),if(0,&#039;a&#039;,&#039;b&#039;),if(null,&#039;a&#039;,&#039;b&#039;),if(&#039;2&#039;,&#039;a&#039;,&#039;b&#039;),if(&#039;c&#039;,&#039;a&#039;,&#039;b&#039;);\n+----------------+---------------+------------------+-----------------+-----------------+\n| if(99,&#039;a&#039;,&#039;b&#039;) | if(0,&#039;a&#039;,&#039;b&#039;) | if(null,&#039;a&#039;,&#039;b&#039;) | if(&#039;2&#039;,&#039;a&#039;,&#039;b&#039;) | if(&#039;c&#039;,&#039;a&#039;,&#039;b&#039;) |\n+----------------+---------------+------------------+-----------------+-----------------+\n| a              | b             | b                | a               | b               |\n+----------------+---------------+------------------+-----------------+-----------------+\n1 row in set, 1 warning (0.00 sec)<\/code><\/pre>\n<h2>ifnull(value1,value2)<\/h2>\n<p>\u5982\u679cvalue1\u4e0d\u4e3a\u7a7a\u5219\u8fd4\u56devalue1\uff0c\u5426\u5219\u8fd4\u56devalue2\u3002\u603b\u4e4b\u5c31\u662f\u7ed9\u5b9a\u4e00\u4e2a\u975enull\u503c\u3002\u5141\u8bb8value2\u4e3anull\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select ifnull(1,&#039;a&#039;),ifnull(&#039;a&#039;,&#039;b&#039;),ifnull(null,&#039;a&#039;),ifnull(&#039;a&#039;,null),ifnull(null,null);\n+---------------+-----------------+------------------+------------------+-------------------+\n| ifnull(1,&#039;a&#039;) | ifnull(&#039;a&#039;,&#039;b&#039;) | ifnull(null,&#039;a&#039;) | ifnull(&#039;a&#039;,null) | ifnull(null,null) |\n+---------------+-----------------+------------------+------------------+-------------------+\n| 1             | a               | a                | a                | NULL              |\n+---------------+-----------------+------------------+------------------+-------------------+\n1 row in set<\/code><\/pre>\n<p>MySQL\u4e2d\u7684ifnull\u51fd\u6570\u57fa\u672c\u7b49\u4ef7\u4e8eSQL Server\u4e2d\u7684isnull()\u51fd\u6570\uff0c\u8ddfSQL  Server\u4e2d\u7684nullif\u51fd\u6570\u76f8\u5dee\u975e\u5e38\u5927\u3002\u4e14MySQL\u4e2d\u7684ifnull\u53ea\u80fd\u4ece\u4e24\u4e2a\u53c2\u6570\u4e2d\u53d6\u4e00\u4e2a\u975e\u7a7a\u503c\uff0c\u800cSQL  Server\u4e2d\u7684coalesce()\u51fd\u6570\u53ef\u4ee5\u4ece\u591a\u4e2a\u53c2\u6570\u4e2d\u9009\u7b2c\u4e00\u4e2a\u975e\u7a7a\u503c\u3002<\/p>\n<h2>nullif(expr1,expr2)<\/h2>\n<p>\u5982\u679cexpr1\u7b49\u4e8eexpr2\uff0c\u5219\u8fd4\u56denull\uff0c\u5426\u5219\u8fd4\u56deexpr1\u3002\u4e5f\u5c31\u662f\u8bf4\uff0c\u4e24\u8005\u4e0d\u76f8\u7b49\u65f6\u53d6\u524d\u8005\uff0c\u5426\u5219\u53d6NULL\u3002\u5982\u679cexpr1\u548cexpr2\u4efb\u610f\u4e00\u4e2a\u4e3anull\uff0c\u5219\u76f4\u63a5\u8fd4\u56denull\u3002\u8fd9\u7b49\u4ef7\u4e8e\uff1a<\/p>\n<pre><code class=\"language-sql\">case when expr1 = expr2 || expr1 is null || expr2 is null then null \nelse expr1\nend<\/code><\/pre>\n<p>\u4f8b\u5982\uff1a<\/p>\n<pre><code class=\"language-sql\">mysql&gt; select nullif(1,1),nullif(1,2),nullif(null,1);   \n+-------------+-------------+----------------+\n| nullif(1,1) | nullif(1,2) | nullif(null,1) |\n+-------------+-------------+----------------+\n|        NULL |           1 | NULL           |\n+-------------+-------------+----------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h2>case\u8bed\u53e5<\/h2>\n<p>\u548cSQL Server\u4e2d\u7684case\u8bed\u6cd5\u5dee\u4e0d\u591a\u3002\u4e5f\u662f\u4e24\u79cd\u683c\u5f0f:<code>case when ...then...else...end<\/code>\u548c<code>case ...when...then...else...end<\/code>\uff1a<\/p>\n<pre><code class=\"language-sql\">-- \u683c\u5f0f\u4e00\uff1a\nCASE WHEN express_1 THEN value_1 \n     WHEN express_2 THEN value_2 \n    \u2026 \n\nELSE value_n   \nEND;\n-- \u683c\u5f0f\u4e8c\uff1a\nCASE express WHEN value1 THEN value_1 \n             WHEN value2 THEN value_2 \n             ... \nELSE value_n   \nEND;<\/code><\/pre>\n<p>\u6ce8\u610f\uff0c\u5982\u679c\u91c7\u7528<code>CASE...WHEN<\/code>\u7684\u5199\u6cd5\u683c\u5f0f\uff0c\u5219express\u53ea\u80fd\u4e0evalue\u8fdb\u884c\u7b49\u540c\u6027\u68c0\u67e5\u3002\u4f8b\u5982\uff1a<\/p>\n<pre><code class=\"language-sql\">\/*\u683c\u5f0f\u4e00\u793a\u4f8b*\/\nSELECT  StudentID, \n        CASE WHEN Mark &lt; 60 THEN &#039;\u4e0d\u53ca\u683c&#039; \n             WHEN Mark &gt;= 60 AND Mark &lt; 70 THEN &#039;\u53ca\u683c&#039; \n             WHEN Mark &gt;= 70 AND Mark &lt; 80 THEN &#039;\u826f\u597d&#039; \n             ELSE &#039;\u4f18\u79c0&#039; \n        END \nFROM    Tscore;\n\n\/*\u683c\u5f0f\u4e8c\u793a\u4f8b*\/\nSELECT  StudentID , \n        CASE FLOOR(Mark \/ 10) \n          WHEN 5 THEN &#039;\u4e0d\u53ca\u683c&#039; \n          WHEN 6 THEN &#039;\u53ca\u683c&#039; \n          WHEN 7 THEN &#039;\u826f\u597d&#039; \n          ELSE &#039;\u4f18\u79c0&#039; \n        END \nFROM    Tscore;<\/code><\/pre>\n<p>\u5176\u4e2d\u683c\u5f0f\u4e8c\u4e3a<code>case ... when<\/code>\u7684\u683c\u5f0f\uff0c\u5b83\u7684when\u90e8\u5206\u7684\u503c\u90fd\u53ea\u80fd\u548cfloor(mark\/10)\u505a\u7b49\u4e8e\u53f7\u6bd4\u8f83\uff0c\u8fd9\u662f\u7b49\u540c\u6027\u68c0\u67e5\u3002\u800c\u683c\u5f0f\u4e00\u7684\u5199\u6cd5\u5c31\u7075\u6d3b\u7684\u591a\uff0c\u65e2\u53ef\u4ee5\u505a\u7b49\u53f7\u6bd4\u8f83\uff0c\u4e5f\u80fd\u505a\u5927\u4e8e\u53f7\u6216\u5176\u4ed6\u65b9\u5f0f\u7684\u6bd4\u8f83\u3002<\/p>\n<h1>\u7c7b\u578b\u8f6c\u6362\u51fd\u6570cast()\u548cconvert()<\/h1>\n<p>\u7c7b\u578b\u8f6c\u6362\u51fd\u6570\u7528\u6765\u8f6c\u6362\u6570\u636e\u7c7b\u578b\u3002\u5728MySQL\/MariaDB\u4e2d\u53ef\u4ee5\u8f6c\u6362\u7684\u7c7b\u578b\u6709\u4ee5\u4e0b\u51e0\u79cd\uff1a<\/p>\n<pre><code class=\"language-sql\">\u4e8c\u8fdb\u5236: BINARY[(N)] \n\u5b57\u7b26\u578b: CHAR[(N)] \n\u65e5\u671f : DATE \n\u65f6\u95f4: TIME \n\u65e5\u671f\u65f6\u95f4\u578b : DATETIME\n\u6d6e\u70b9\u6570 : DECIMAL \n\u6574\u6570 : SIGNED \n\u65e0\u7b26\u53f7\u6574\u6570 : UNSIGNED<\/code><\/pre>\n<p>\u5176\u4e2dconvert()\u6709\u4e24\u79cd\u8bed\u6cd5\uff1a<\/p>\n<pre><code class=\"language-sql\">CONVERT(expr,type), CONVERT(expr USING transcoding_name)<\/code><\/pre>\n<p>\u540e\u8005\u7528\u4e8e\u4e0d\u540c\u5b57\u7b26\u96c6\u4e4b\u95f4\u8f6c\u6362\u6570\u636e\u3002<\/p>\n<p>\u5728\u8f6c\u6362\u6570\u636e\u7c7b\u578b\u65f6\uff0ccast\u548cconvert\u7684\u529f\u80fd\u57fa\u672c\u662f\u4e00\u6837\u7684\uff0c\u53ea\u662f\u5199\u6cd5\u4e0d\u540c\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; SELECT CAST(&#039;3.35&#039; AS signed);\n+------------------------+\n| CAST(&#039;3.35&#039; AS signed) |\n+------------------------+\n|                      3 |\n+------------------------+\n1 row in set\n\nmysql&gt; SELECT  CAST(100 AS CHAR(2)),CONVERT(&#039;2013-8-9 12:12:12&#039;,TIME);\n+----------------------+-----------------------------------+\n| CAST(100 AS CHAR(2)) | CONVERT(&#039;2013-8-9 12:12:12&#039;,TIME) |\n+----------------------+-----------------------------------+\n| 10                   | 12:12:12                          |\n+----------------------+-----------------------------------+\n1 row in set<\/code><\/pre>\n<p>\u5e26\u6709using\u7684convert\u51fd\u6570\u7528\u6765\u8f6c\u6362\u5b57\u7b26\u96c6\u3002<\/p>\n<pre><code class=\"language-sql\">mysql&gt; SELECT  CHARSET(&#039;string&#039;),CHARSET(CONVERT(&#039;string&#039; USING latin1));\n+-------------------+-----------------------------------------+\n| CHARSET(&#039;string&#039;) | CHARSET(CONVERT(&#039;string&#039; USING latin1)) |\n+-------------------+-----------------------------------------+\n| utf8mb4           | latin1                                  |\n+-------------------+-----------------------------------------+\n1 row in set<\/code><\/pre>\n<h1>\u5176\u5b83\u5b9e\u7528\u51fd\u6570<\/h1>\n<ul>\n<li>\n<p>sleep(N)<\/p>\n<p>\u5ef6\u8fdfN\u79d2\u540e\u6267\u884c\u540e\u9762\u7684\u8bed\u53e5\u3002\u7279\u6b8a\u70b9\u5728\u4e8esleep()\u51fd\u6570\u53ef\u4ee5\u7528\u4e8eselect\u7684\u9009\u62e9\u5217\u8868\u3002<\/p>\n<pre><code class=\"language-sql\">select a,sleep(2),a from t;<\/code><\/pre>\n<p>\u6ce8\u610f\u4e0a\u9762\u7684\u8bed\u53e5\u4e2d\uff0c\u662f\u5148\u67e5\u8be2a\uff0c\u518d\u963b\u585e2\u79d2\uff0c\u4e4b\u540e\u518d\u67e5\u8be2a\uff0c\u800c\u4e0d\u662f\u5148\u963b\u585e\u540e\u518d\u67e5\u8be2\u4e24\u6b21a\u6216\u67e5\u8be2\u4e24\u6b21a\u540e\u518d\u963b\u585e\u3002\u4e5f\u5c31\u662f\u8bf4\uff0c\u5bf9\u4e8emysql\/mariadb\u6765\u8bf4\uff0cselect\u7684\u9009\u62e9\u5217\u8868\u4e4b\u95f4\u662f\u6709\u5148\u540e\u987a\u5e8f\u7684\uff0c\u4e0d\u50cfsql server\uff0c\u9009\u62e9\u5217\u4e4b\u95f4\u662f\u5b8c\u5168\u5e73\u884c\u7b49\u4ef7\u7684\u3002\u53ef\u4ee5\u901a\u8fc7\u4e0b\u9762\u7684\u4f8b\u5b50\u6765\u9a8c\u8bc1\uff1a<\/p>\n<pre><code class=\"language-sql\">select sysdate(),sleep(1),sysdate();<\/code><\/pre>\n<\/li>\n<li>\n<p>\u8fd4\u56de\u5f53\u524d\u6570\u636e\u5e93\u540ddatabase()<\/p>\n<\/li>\n<li>\n<p>\u8fd4\u56de\u5f53\u524d\u6570\u636e\u5e93\u7248\u672cversion()<\/p>\n<\/li>\n<li>\n<p>\u8fd4\u56de\u5f53\u524d\u767b\u5f55\u7528\u6237\u540duser()<\/p>\n<pre><code class=\"language-sql\">mysql> select database(),version(),user();\n+------------+-----------+----------------+\n| database() | version() | user()         |\n+------------+-----------+----------------+\n| test       | 5.7.37    | root@localhost |\n+------------+-----------+----------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<\/li>\n<li>\n<p>\u8fd4\u56de\u52a0\u5bc6\u5b57\u7b26\u4e32password(str)<\/p>\n<\/li>\n<li>\n<p>\u8fd4\u56de\u5b57\u7b26\u4e32\u7684MD5\u503cmd5(str)<\/p>\n<pre><code>mysql> select password('abc'),md5('abc');\n+-------------------------------------------+----------------------------------+\n| password('abc')                           | md5('abc')                       |\n+-------------------------------------------+----------------------------------+\n| *0D3CED9BEC10A777AEC23CCC353A8C08A633045E | 900150983cd24fb0d6963f7d28e17f72 |\n+-------------------------------------------+----------------------------------+\n1 row in set<\/code><\/pre>\n<\/li>\n<li>\n<p>last_insert_id()\u51fd\u6570<br \/>\nLAST_INSERT_ID()\u8fd4\u56de\u6700\u540e\u4e00\u4e2aINSERT\u6216UPDATE\u4e3aAUTO_INCREMENT\u5217\u8bbe\u7f6e\u7684\u7b2c\u4e00\u4e2a\u53d1\u751f\u7684\u503c\u3002\u8be5\u51fd\u6570\u503c\u4e0d\u662f\u57fa\u4e8e\u8868\u7684\uff0c\u8fd9\u4e00\u70b9\u548cSQL  Server\u662f\u4e0d\u540c\u7684\uff0c\u4e5f\u5c31\u662f\u8bf4\uff0c\u5bf9a\u8868\u63d2\u5165\u7684\u6700\u540e\u4e00\u4e2a\u503c\u662f10\uff0c\u518d\u5bf9b\u8868\u63d2\u5165\u7684\u6700\u540e\u4e00\u4e2a\u503c\u662f15\uff0c\u90a3\u4e48\u51fd\u6570\u8fd4\u56de\u7684\u5c06\u662f15\u3002\u5e76\u4e14last_insert_id\u7684\u503c\u548c\u4e00\u6b21\u63d2\u5165\u4e00\u6761\u8bb0\u5f55\u8fd8\u662f\u4e00\u6b21\u6279\u91cf\u63d2\u5165\u7684\u65b9\u5f0f\u6709\u5173\u3002<\/p>\n<pre><code class=\"language-sql\">mysql> create table test10(id int primary key auto_increment,name char(20));\n# \u4e00\u6b21\u63d2\u5165\u4e00\u6761\u8bb0\u5f55\u3002\nmysql> insert into test10 values(null,'gaoxiaofang');\nmysql> insert into test10 values(null,'malongshuai');\nmysql> insert into test10 values(null,'longshuai');\nmysql> insert into test10 values(null,'xiaofang');\nmysql> select * from test10;\n+----+-------------+\n| id | name        |\n+----+-------------+\n|  1 | gaoxiaofang |\n|  2 | malongshuai |\n|  3 | longshuai   |\n|  4 | xiaofang    |\n+----+-------------+\n4 rows in set<\/code><\/pre>\n<p>\u67e5\u770blast_insert_id\u7684\u503c\uff0c\u7ed3\u679c\u5c06\u662f4\u3002<\/p>\n<pre><code class=\"language-sql\">mysql> select last_insert_id();\n+------------------+\n| last_insert_id() |\n+------------------+\n|                4 |\n+------------------+\n1 row in set<\/code><\/pre>\n<p>\u4e00\u6b21\u63d2\u5165\u591a\u6761\u8bb0\u5f55\uff0c\u5e76\u67e5\u770blast_insert_id()\u7684\u503c\u3002<\/p>\n<pre><code class=\"language-sql\">mysql> insert into test10 values(null,'tun\\'er'),(null,'woniu'),(null,'wugui');\nmysql> select *,last_insert_id() from test10;\n+----+-------------+------------------+\n| id | name        | last_insert_id() |\n+----+-------------+------------------+\n|  1 | gaoxiaofang |                5 |\n|  2 | malongshuai |                5 |\n|  3 | longshuai   |                5 |\n|  4 | xiaofang    |                5 |\n|  5 | tun'er      |                5 |\n|  6 | woniu       |                5 |\n|  7 | wugui       |                5 |\n+----+-------------+------------------+\n7 rows in set<\/code><\/pre>\n<p>\u53ef\u4ee5\u53d1\u73b0\u8fd9\u91cclast_insert_id\u7684\u503c\u4e0d\u662f7\u800c\u662f5\uff0c\u8fd9\u662f\u56e0\u4e3a\u6279\u91cf\u63d2\u5165\u7684\u65f6\u5019last_insert_id\u7684\u503c\u5c06\u4f1a\u662f\u6279\u91cf\u4e2d\u7684\u7b2c\u4e00\u6761\u8bb0\u5f55\u7684\u81ea\u589e\u5217\u503c\u3002 \u4e14last_insert_id\u7684\u503c\u548c\u8868\u662f\u65e0\u5173\u7684\uff0c\u53ea\u548c\u4f1a\u8bdd\u73af\u5883\u6709\u5173\u3002\u4f8b\u5982\u518d\u5411\u53e6\u5916\u4e00\u4e2a\u8868\u63d2\u5165\u540e\uff0clast_insert_id\u7684\u503c\u5c06\u53d8\u4e3a\u53e6\u4e00\u4e2a\u503c\u3002<\/p>\n<pre><code class=\"language-sql\">mysql> create table test11(id int primary key auto_increment,name char(20));\nmysql> insert into test11 values(null,'gaoxiaofang');\nmysql> insert into test11 values(null,'malongshuai');\nmysql> insert into test11 values(null,'longshuai');\nmysql> insert into test11 values(null,'xiaofang');\nmysql> select last_insert_id();\n+------------------+\n| last_insert_id() |\n+------------------+\n|                4 |\n+------------------+\n1 row in set<\/code><\/pre>\n<p>\u53ef\u4ee5\u53d1\u73b0\u5b83\u53c8\u53d8\u56de\u4e864\u3002<\/p>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u5b57\u7b26\u4e32\u51fd\u6570 \u5b8c\u6574\u7684\u5185\u7f6e\u5b57\u7b26\u4e32\u51fd\u6570\u89c1\u5b98\u65b9\u624b\u518c\u3002 \u5b57\u7b26\u4e32\u8fde\u63a5\u51fd\u6570 \u6709\u4e24\u4e2a\u5b57\u7b26\u4e32\u8fde\u63a5\u51fd\u6570\uff1aconcat(s1,s2,s3,&#8230;,sN) &#8230;","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-228","post","type-post","status-publish","format-standard","hentry","category-4"],"_links":{"self":[{"href":"http:\/\/danielw.top\/index.php?rest_route=\/wp\/v2\/posts\/228","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/danielw.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/danielw.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/danielw.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/danielw.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=228"}],"version-history":[{"count":1,"href":"http:\/\/danielw.top\/index.php?rest_route=\/wp\/v2\/posts\/228\/revisions"}],"predecessor-version":[{"id":229,"href":"http:\/\/danielw.top\/index.php?rest_route=\/wp\/v2\/posts\/228\/revisions\/229"}],"wp:attachment":[{"href":"http:\/\/danielw.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=228"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/danielw.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=228"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/danielw.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=228"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}