Chapter聽12.聽Functions and Operators - MySQL 5.1参考手册中文版

鍋囧�傜粨鏋滅殑闀垮害澶т�� max_allowed_packet 绯荤粺鍙橀噺鐨勬渶澶у�兼椂锛屽瓧绗︿覆鍊煎嚱鏁扮殑杩斿洖鍊间负NULL銆傝�峰弬瑙�7.5.2鑺傦紝鈥滆皟鑺傛湇鍔″櫒鍙傛暟鈥�銆�

瀵逛簬鍦ㄥ瓧绗︿覆浣嶇疆鎿嶄綔鐨勫嚱鏁帮紝绗�涓�涓�浣嶇疆鐨勭紪鍙蜂�� 1銆�

• ASCII(str)

杩斿洖鍊间负瀛楃�︿��str 鐨勬渶宸﹀瓧绗︾殑鏁板�笺�傚亣濡�str涓虹┖瀛楃�︿覆锛屽垯杩斿洖鍊间负 0 銆傚亣濡�str 涓�NULL锛屽垯杩斿洖鍊间负 NULL銆� ASCII()鐢ㄤ簬甯︽湁浠� 0鍒�255鐨勬暟鍊肩殑瀛楃�︺��

mysql> SELECT ASCII('2');

        -> 50

mysql> SELECT ASCII(2);

        -> 50

mysql> SELECT ASCII('dx');

        -> 100

瑙� ORD()鍑芥暟銆�

• BIN(N)

杩斿洖鍊间负N鐨勪簩杩涘埗鍊肩殑瀛楃�︿覆琛ㄧず锛屽叾涓�  N 涓轰竴涓�longlong (BIGINT) 鏁板瓧銆傝繖绛夊悓浜� CONV(N,10,2)銆傚亣濡�N 涓�NULL锛屽垯杩斿洖鍊间负 NULL銆�

mysql> SELECT BIN(12);

        -> '1100'

• BIT_LENGTH(str)

杩斿洖鍊间负浜岃繘鍒剁殑瀛楃�︿��str 闀垮害銆�

mysql> SELECT BIT_LENGTH('text');

        -> 32

• CHAR(N,... [USING charset])

CHAR()灏嗘瘡涓�鍙傛��N鐞嗚В涓轰竴涓�鏁存暟锛屽叾杩斿洖鍊间负涓�涓�鍖呭惈杩欎簺鏁存暟鐨勪唬鐮佸�兼墍缁欏嚭鐨勫瓧绗︾殑瀛楃�︿覆銆�NULL鍊艰��鐪佺暐銆�

mysql> SELECT CHAR(77,121,83,81,'76');

        -> 'MySQL'

mysql> SELECT CHAR(77,77.3,'77.3');

        -> 'MMM'

澶т簬 255鐨�CHAR()鍙傛暟琚�杞�鎹�涓哄�氱粨鏋滃瓧绗︺�� 渚嬪�傦��CHAR(256) 鐩稿綋浜� CHAR(1,0), 鑰�CHAR(256*256) 鍒欑浉褰撲簬 CHAR(1,0,0)锛�

mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));

+----------------+----------------+

| HEX(CHAR(1,0)) | HEX(CHAR(256)) |

+----------------+----------------+

| 0100           | 0100           |

+----------------+----------------+

mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));

+------------------+--------------------+

| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |

+------------------+--------------------+

| 010000           | 010000             |

+------------------+--------------------+

CHAR()鐨勮繑鍥炲�间负涓�涓�浜岃繘鍒跺瓧绗︿覆銆傚彲閫夋嫨浣跨敤USING璇�鍙ヤ骇鐢熶竴涓�缁欏嚭鐨勫瓧绗﹂泦涓�鐨勫瓧绗︿覆锛�

mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));

mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));

+---------------------+--------------------------------+

| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |

+---------------------+--------------------------------+

| binary              | utf8                           |

+---------------------+--------------------------------+

濡傛灉 USING宸茬粡浜х敓锛岃�岀粨鏋滃瓧绗︿覆涓嶇�﹀悎缁欏嚭鐨勫瓧绗﹂泦锛屽垯浼氬彂鍑鸿�﹀憡銆�   鍚屾牱锛屽�傛灉涓ユ牸鐨�SQL妯″紡琚�婵�娲伙紝鍒�CHAR()鐨勭粨鏋滀細鎴愪负 NULL銆�

• CHAR_LENGTH(str)

杩斿洖鍊间负瀛楃�︿��str 鐨勯暱搴︼紝闀垮害鐨勫崟浣嶄负瀛楃�︺�備竴涓�澶氬瓧鑺傚瓧绗︾畻浣滀竴涓�鍗曞瓧绗︺��瀵逛簬涓�涓�鍖呭惈浜斾釜浜屽瓧鑺傚瓧绗﹂泦, LENGTH()杩斿洖鍊间负 10, 鑰�CHAR_LENGTH()鐨勮繑鍥炲�间负5銆�

• CHARACTER_LENGTH(str)

CHARACTER_LENGTH()鏄�CHAR_LENGTH()鐨勫悓涔夎瘝銆�

• COMPRESS(string_to_compress)

鍘嬬缉涓�涓�瀛楃�︿覆銆傝繖涓�鍑芥暟瑕佹�� MySQL宸茬粡鐢ㄤ竴涓�璇稿��zlib鐨勫帇缂╁簱鍘嬬缉杩囥��   鍚﹀垯锛岃繑鍥炲�煎�嬬粓鏄�NULL銆�UNCOMPRESS() 鍙�灏嗗帇缂╄繃鐨勫瓧绗︿覆杩涜�岃В鍘嬬缉銆�

mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));

        -> 21

mysql> SELECT LENGTH(COMPRESS(''));

        -> 0

mysql> SELECT LENGTH(COMPRESS('a'));

        -> 13

mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));

        -> 15

鍘嬬缉鍚庣殑瀛楃�︿覆鐨勫唴瀹规寜鐓т互涓嬫柟寮忓瓨鍌�锛�

• 绌哄瓧绗︿覆鎸夌収绌哄瓧绗︿覆瀛樺偍銆�
• 闈炵┖瀛楃�︿覆鏈�鍘嬬缉瀛楃�︿覆鐨勫洓瀛楄妭闀垮害杩涜�屽瓨鍌�(棣栧厛涓轰綆瀛楄妭),鍚庨潰鏄�鍘嬬缉瀛楃�︿覆銆傚�傛灉瀛楃�︿覆浠ョ┖鏍肩粨灏撅紝灏变細鍦ㄥ悗鍔犱竴涓�"."鍙�,浠ラ槻姝㈠綋缁撴灉鍊兼槸瀛樺偍鍦�CHAR鎴�VARCHAR绫诲瀷鐨勫瓧娈靛垪鏃�,鍑虹幇鑷�鍔ㄦ妸缁撳熬绌烘牸鍘绘帀鐨勭幇璞°��(涓嶆帹鑽愪娇鐢� CHAR 鎴�VARCHAR 鏉ュ瓨鍌ㄥ帇缂╁瓧绗︿覆銆傛渶濂戒娇鐢ㄤ竴涓� BLOB 鍒椾唬鏇匡級銆�
• CONCAT(str1,str2,...)                       

杩斿洖缁撴灉涓鸿繛鎺ュ弬鏁颁骇鐢熺殑瀛楃�︿覆銆傚�傛湁浠讳綍涓�涓�鍙傛暟涓�NULL 锛屽垯杩斿洖鍊间负 NULL銆傛垨璁告湁涓�涓�鎴栧�氫釜鍙傛暟銆� 濡傛灉鎵�鏈夊弬鏁板潎涓洪潪浜岃繘鍒跺瓧绗︿覆锛屽垯缁撴灉涓洪潪浜岃繘鍒跺瓧绗︿覆銆� 濡傛灉鑷�鍙橀噺涓�鍚�鏈変换涓�浜岃繘鍒跺瓧绗︿覆锛屽垯缁撴灉涓轰竴涓�浜岃繘鍒跺瓧绗︿覆銆備竴涓�鏁板瓧鍙傛暟琚�杞�鍖栦负涓庝箣鐩哥瓑鐨勪簩杩涘埗瀛楃�︿覆鏍煎紡锛涜嫢瑕侀伩鍏嶈繖绉嶆儏鍐碉紝鍙�浣跨敤鏄惧紡绫诲�� cast, 渚嬪�傦�� SELECT CONCAT(CAST(int_col AS CHAR), char_col)

mysql> SELECT CONCAT('My', 'S', 'QL');

        -> 'MySQL'

mysql> SELECT CONCAT('My', NULL, 'QL');

        -> NULL

mysql> SELECT CONCAT(14.3);

        -> '14.3'

• CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() 浠ｈ〃 CONCAT With Separator 锛屾槸CONCAT()鐨勭壒娈婂舰寮忋��   绗�涓�涓�鍙傛暟鏄�鍏跺畠鍙傛暟鐨勫垎闅旂�︺�傚垎闅旂�︾殑浣嶇疆鏀惧湪瑕佽繛鎺ョ殑涓や釜瀛楃�︿覆涔嬮棿銆傚垎闅旂�﹀彲浠ユ槸涓�涓�瀛楃�︿覆锛屼篃鍙�浠ユ槸鍏跺畠鍙傛暟銆傚�傛灉鍒嗛殧绗︿�� NULL锛屽垯缁撴灉涓� NULL銆傚嚱鏁颁細蹇界暐浠讳綍鍒嗛殧绗﹀弬鏁板悗鐨� NULL 鍊笺��

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');

        -> 'First name,Second name,Last Name'

mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');

        -> 'First name,Last Name'

CONCAT_WS()涓嶄細蹇界暐浠讳綍绌哄瓧绗︿覆銆� (鐒惰�屼細蹇界暐鎵�鏈夌殑 NULL锛�銆�

• CONV(N,from_base,to_base)

涓嶅悓鏁板熀闂磋浆鎹㈡暟瀛椼�傝繑鍥炲�间负鏁板瓧鐨�N瀛楃�︿覆琛ㄧず锛岀��from_base鍩鸿浆鍖栦负 to_base 鍩恒�傚�傛湁浠绘剰涓�涓�鍙傛暟涓�NULL锛屽垯杩斿洖鍊间负 NULL銆傝嚜鍙橀噺 N 琚�鐞嗚В涓轰竴涓�鏁存暟锛屼絾鏄�鍙�浠ヨ��鎸囧畾涓轰竴涓�鏁存暟鎴栧瓧绗︿覆銆傛渶灏忓熀鏁颁负 2 锛岃�屾渶澶у熀鏁板垯涓� 36銆� If to_base 鏄�涓�涓�璐熸暟锛屽�� N 琚�鐪嬩綔涓�涓�甯︾�﹀彿鏁般�傚惁鍒欙紝 N 琚�鐪嬩綔鏃犵�﹀彿鏁般�� CONV() 鐨勮繍琛岀簿纭�搴︿�� 64姣旂壒銆�

mysql> SELECT CONV('a',16,2);

        -> '1010'

mysql> SELECT CONV('6E',18,8);

        -> '172'

mysql> SELECT CONV(-17,10,-18);

        -> '-H'

mysql> SELECT CONV(10+'10'+'10'+0xa,10,10);

        -> '40'

• ELT(N,str1,str2,str3,...)

鑻�N = 1锛屽垯杩斿洖鍊间负  str1 锛岃嫢N = 2锛屽垯杩斿洖鍊间负 str2 锛屼互姝ょ被鎺ㄣ��   鑻�N 灏忎簬1鎴栧ぇ浜庡弬鏁扮殑鏁扮洰锛屽垯杩斿洖鍊间负 NULL 銆� ELT() 鏄�  FIELD()鐨勮ˉ鏁般��

mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');

        -> 'ej'

mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');

        -> 'foo'

• EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

杩斿洖鍊间负涓�涓�瀛楃�︿覆锛屽叾涓�瀵逛��bits鍊间腑鐨勬瘡涓�浣嶇粍锛屽彲浠ュ緱鍒颁竴涓� on 瀛楃�︿覆锛岃�屽�逛簬姣忎釜娓呴浂姣旂壒浣嶏紝鍙�浠ュ緱鍒颁竴涓�off 瀛楃�︿覆銆�bits 涓�鐨勬瘮鐗瑰�兼寜鐓т粠鍙冲埌宸︾殑椤哄簭鎺ュ彈妫�楠� (鐢变綆浣嶆瘮鐗瑰埌楂樹綅姣旂壒)銆傚瓧绗︿覆琚�鍒嗛殧瀛楃�︿覆鍒嗗紑(榛樿�や负閫楀彿鈥�,鈥�)锛屾寜鐓т粠宸﹀埌鍙崇殑椤哄簭琚�娣诲姞鍒扮粨鏋滀腑銆�number_of_bits 浼氱粰鍑鸿��妫�楠岀殑浜岃繘鍒朵綅鏁� (榛樿�や�� 64)銆�

mysql> SELECT EXPORT_SET(5,'Y','N',',',4);

        -> 'Y,N,Y,N'

mysql> SELECT EXPORT_SET(6,'1','0',',',10);

        -> '0,1,1,0,0,0,0,0,0,0'

• FIELD(str,str1,str2,str3,...)

杩斿洖鍊间负str1, str2, str3,鈥︹�﹀垪琛ㄤ腑鐨�str 鎸囨暟銆傚湪鎵句笉鍒�str 鐨勬儏鍐典笅锛岃繑鍥炲�间负 0 銆�

濡傛灉鎵�鏈夊�逛��FIELD() 鐨勫弬鏁板潎涓哄瓧绗︿覆锛屽垯鎵�鏈夊弬鏁板潎鎸夌収瀛楃�︿覆杩涜�屾瘮杈冦�傚�傛灉鎵�鏈夌殑鍙傛暟鍧囦负鏁板瓧锛屽垯鎸夌収鏁板瓧杩涜�屾瘮杈冦�傚惁鍒欙紝鍙傛暟鎸夌収鍙屽�嶈繘琛屾瘮杈冦��

濡傛灉str 涓�NULL锛屽垯杩斿洖鍊间负0 锛屽師鍥犳槸NULL涓嶈兘鍚屼换浣曞�艰繘琛屽悓绛夋瘮杈冦��FIELD() 鏄�ELT()鐨勮ˉ鏁般��

mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');

        -> 2

mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');

        -> 0

• FIND_IN_SET(str,strlist)

鍋囧�傚瓧绗︿��str 鍦ㄧ敱N 瀛愰摼缁勬垚鐨勫瓧绗︿覆鍒楄〃strlist 涓�锛� 鍒欒繑鍥炲�肩殑鑼冨洿鍦� 1 鍒� N 涔嬮棿 銆備竴涓�瀛楃�︿覆鍒楄〃灏辨槸涓�涓�鐢变竴浜涜��鈥�,鈥欑�﹀彿鍒嗗紑鐨勮嚜閾剧粍鎴愮殑瀛楃�︿覆銆傚�傛灉绗�涓�涓�鍙傛暟鏄�涓�涓�甯告暟瀛楃�︿覆锛岃�岀��浜屼釜鏄�type SET鍒楋紝鍒�   FIND_IN_SET() 鍑芥暟琚�浼樺寲锛屼娇鐢ㄦ瘮鐗硅�＄畻銆傚�傛��str涓嶅湪strlist 鎴�strlist 涓虹┖瀛楃�︿覆锛屽垯杩斿洖鍊间负 0 銆傚�備换鎰忎竴涓�鍙傛暟涓�NULL锛屽垯杩斿洖鍊间负 NULL銆� 杩欎釜鍑芥暟鍦ㄧ��涓�涓�鍙傛暟鍖呭惈涓�涓�閫楀彿(鈥�,鈥�)鏃跺皢鏃犳硶姝ｅ父杩愯�屻�� 

mysql> SELECT FIND_IN_SET('b','a,b,c,d');

        -> 2

• FORMAT(X,D)

灏�number X璁剧疆涓烘牸寮� '#,###,###.##', 浠ュ洓鑸嶄簲鍏ョ殑鏂瑰紡淇濈暀鍒板皬鏁扮偣鍚�D浣�, 鑰岃繑鍥炵粨鏋滀负涓�涓�瀛楃�︿覆銆傝�﹁��12.9.4鑺傦紝鈥滃叾浠栧嚱鏁扳��.

• HEX(N_or_S)

濡傛灉N_OR_S 鏄�涓�涓�鏁板瓧锛屽垯杩斿洖涓�涓� 鍗佸叚杩涘埗鍊� N 鐨�瀛楃�︿覆琛ㄧず锛屽湪杩欓噷锛�   N 鏄�涓�涓�longlong (BIGINT)鏁般�傝繖鐩稿綋浜� CONV(N,10,16)銆�

濡傛灉N_OR_S 鏄�涓�涓�瀛楃�︿覆锛屽垯杩斿洖鍊间负涓�涓�N_OR_S鐨勫崄鍏�杩涘埗瀛楃�︿覆琛ㄧず锛� 鍏朵腑姣忎釜N_OR_S 閲岀殑姣忎釜瀛楃�﹁��杞�鍖栦负涓や釜鍗佸叚杩涘埗鏁板瓧銆�

mysql> SELECT HEX(255);

        -> 'FF'

mysql> SELECT 0x616263;

        -> 'abc'

mysql> SELECT HEX('abc');

        -> 616263

• INSERT(str,pos,len,newstr)

杩斿洖瀛楃�︿�� str, 鍏跺瓙瀛楃�︿覆璧峰�嬩簬 pos 浣嶇疆鍜岄暱鏈熻��瀛楃�︿�� newstr鍙栦唬鐨�len 瀛楃�︺��  濡傛灉pos 瓒呰繃瀛楃�︿覆闀垮害锛屽垯杩斿洖鍊间负鍘熷�嬪瓧绗︿覆銆� 鍋囧��len鐨勯暱搴﹀ぇ浜庡叾瀹冨瓧绗︿覆鐨勯暱搴︼紝鍒欎粠浣嶇疆pos寮�濮嬫浛鎹��傝嫢浠讳綍涓�涓�鍙傛��涓�null锛屽垯杩斿洖鍊间负NULL銆�

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');

        -> 'QuWhattic'

mysql> SELECT INSERT('Quadratic', -1, 4, 'What');

        -> 'Quadratic'

mysql> SELECT INSERT('Quadratic', 3, 100, 'What');

        -> 'QuWhat'

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏�銆�

• INSTR(str,substr)

杩斿洖瀛楃�︿�� str 涓�瀛愬瓧绗︿覆鐨勭��涓�涓�鍑虹幇浣嶇疆銆傝繖鍜�LOCATE()鐨勫弻鍙傛暟褰㈠紡鐩稿悓锛岄櫎闈炲弬鏁扮殑椤哄簭琚�棰犲�掋�� 

mysql> SELECT INSTR('foobarbar', 'bar');

        -> 4

mysql> SELECT INSTR('xbar', 'foobar');

        -> 0

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冿紝骞朵笖鍙�鏈夊綋鑷冲皯鏈変竴涓�鍙傛暟鏄�浜岃繘鍒跺瓧绗︿覆鏃跺尯鍒嗗ぇ灏忓啓銆�

• LCASE(str)

LCASE() 鏄� LOWER()鐨勫悓涔夎瘝銆�

• LEFT(str,len)

杩斿洖浠庡瓧绗︿覆str 寮�濮嬬殑len 鏈�宸﹀瓧绗︺��

mysql> SELECT LEFT('foobarbar', 5);

        -> 'fooba'

• LENGTH(str)

杩斿洖鍊间负瀛楃�︿��str 鐨勯暱搴︼紝鍗曚綅涓哄瓧鑺傘�備竴涓�澶氬瓧鑺傚瓧绗︾畻浣滃�氬瓧鑺傘�傝繖鎰忓懗鐫� 瀵逛簬涓�涓�鍖呭��5涓�2瀛楄妭瀛楃�︾殑瀛楃�︿覆锛� LENGTH() 鐨勮繑鍥炲�间负 10, 鑰� CHAR_LENGTH()鐨勮繑鍥炲�煎垯涓�5銆�

mysql> SELECT LENGTH('text');

        -> 4

• LOAD_FILE(file_name)

璇诲彇鏂囦欢骞跺皢杩欎竴鏂囦欢鎸夌収瀛楃�︿覆鐨勬牸寮忚繑鍥炪�� 鏂囦欢鐨勪綅缃�蹇呴』鍦ㄦ湇鍔″櫒涓�,浣犲繀椤讳负鏂囦欢鍒跺畾璺�寰勫叏鍚嶏紝鑰屼笖浣犺繕蹇呴』鎷ユ湁FILE 鐗硅�告潈銆傛枃浠跺繀椤诲彲璇诲彇锛屾枃浠跺�归噺蹇呴』灏忎�� max_allowed_packet瀛楄妭銆�

鑻ユ枃浠朵笉瀛樺湪锛屾垨鍥犱笉婊¤冻涓婅堪鏉′欢鑰屼笉鑳借��璇诲彇锛� 鍒欏嚱鏁拌繑鍥炲�间负 NULL銆�

mysql> UPDATE tbl_name

           SET blob_column=LOAD_FILE('/tmp/picture')

           WHERE id=1;

• LOCATE(substr,str) , LOCATE(substr,str,pos)

绗�涓�涓�璇�娉曡繑鍥炲瓧绗︿覆 str涓�瀛愬瓧绗︿��substr鐨勭��涓�涓�鍑虹幇浣嶇疆銆傜��浜屼釜璇�娉曡繑鍥炲瓧绗︿�� str涓�瀛愬瓧绗︿��substr鐨勭��涓�涓�鍑虹幇浣嶇��, 璧峰�嬩綅缃�鍦�pos銆傚�傝��substr 涓嶅湪str涓�锛屽垯杩斿洖鍊间负0銆�

mysql> SELECT LOCATE('bar', 'foobarbar');

        -> 4

mysql> SELECT LOCATE('xbar', 'foobar');

        -> 0

mysql> SELECT LOCATE('bar', 'foobarbar',5);

        -> 7

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冿紝骞朵笖鍙�鏈夊綋鑷冲皯鏈変竴涓�鍙傛暟鏄�浜岃繘鍒跺瓧绗︿覆鏃跺尯鍒嗗ぇ灏忓啓銆� 

• LOWER(str)

杩斿洖瀛楃�︿�� str 浠ュ強鎵�鏈夋牴鎹�鏈�鏂扮殑瀛楃�﹂泦鏄犲皠琛ㄥ彉涓哄皬鍐欏瓧姣嶇殑瀛楃�� (榛樿�や��  cp1252 Latin1)銆�

mysql> SELECT LOWER('QUADRATICALLY');

        -> 'quadratically'

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冦��

• LPAD(str,len,padstr)

杩斿洖瀛楃�︿�� str, 鍏跺乏杈圭敱瀛楃�︿��padstr 濉�琛ュ��len 瀛楃�﹂暱搴︺�傚亣濡�str 鐨勯暱搴﹀ぇ浜�len, 鍒欒繑鍥炲�艰��缂╃煭鑷� len 瀛楃�︺��

mysql> SELECT LPAD('hi',4,'??');

        -> '??hi'

mysql> SELECT LPAD('hi',1,'??');

        -> 'h'

• LTRIM(str)

杩斿洖瀛楃�︿�� str 锛屽叾寮曞�肩┖鏍煎瓧绗﹁��鍒犻櫎銆�

mysql> SELECT LTRIM('  barbar');

        -> 'barbar'

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冦��

• MAKE_SET(bits,str1,str2,...)

杩斿洖涓�涓�璁惧畾鍊� (涓�涓�鍖呭惈琚�鈥�,鈥欏彿鍒嗗紑鐨勫瓧瀛楃�︿覆鐨勫瓧绗︿��) 锛岀敱鍦�bits 缁勪腑鍏锋湁鐩稿簲鐨勬瘮鐗圭殑瀛楃�︿覆缁勬垚銆�str1 瀵瑰簲姣旂壒 0, str2 瀵瑰簲姣旂壒1,浠ユ�ょ被鎺ㄣ��str1, str2, ...涓�鐨� NULL鍊间笉浼氳��娣诲姞鍒扮粨鏋滀腑銆�

mysql> SELECT MAKE_SET(1,'a','b','c');

        -> 'a'

mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');

        -> 'hello,world'

mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');

        -> 'hello'

mysql> SELECT MAKE_SET(0,'a','b','c');

        -> ''

• MID(str,pos,len)

MID(str,pos,len) 鏄� SUBSTRING(str,pos,len)鐨勫悓涔夎瘝銆�

• OCT(N)

杩斿洖涓�涓� N鐨勫叓杩涘埗鍊肩殑瀛楃�︿覆琛ㄧず锛屽叾涓� N 鏄�涓�涓�longlong (BIGINT)鏁般�傝繖绛夊悓浜�CONV(N,10,8)銆傝嫢N 涓� NULL 锛屽垯杩斿洖鍊间负NULL銆�

mysql> SELECT OCT(12);

        -> '14'

• OCTET_LENGTH(str)

OCTET_LENGTH() 鏄� LENGTH()鐨勫悓涔夎瘝銆�

• ORD(str)

鑻ュ瓧绗︿覆str 鐨勬渶宸﹀瓧绗︽槸涓�涓�澶氬瓧鑺傚瓧绗︼紝鍒欒繑鍥炶�ュ瓧绗︾殑浠ｇ爜锛� 浠ｇ爜鐨勮�＄畻閫氳繃浣跨敤浠ヤ笅鍏�寮忚�＄畻鍏剁粍鎴愬瓧鑺傜殑鏁板�艰�屽緱鍑�:

   (1st byte code)

+ (2nd byte code 脳 256)

+ (3rd byte code 脳 2562) ...

鍋囧�傛渶宸﹀瓧绗︿笉鏄�涓�涓�澶氬瓧鑺傚瓧绗︼紝閭ｄ�� ORD()鍜屽嚱鏁�ASCII()杩斿洖鐩稿悓鐨勫�笺��

mysql> SELECT ORD('2');

        -> 50

• POSITION(substr IN str)

POSITION(substr IN str)鏄� LOCATE(substr,str)鍚屼箟璇嶃��

• QUOTE(str)

寮曡瘉涓�涓�瀛楃�︿覆锛岀敱姝や骇鐢熶竴涓�鍦�SQL璇�鍙ヤ腑鍙�鐢ㄤ綔瀹屽叏杞�涔夋暟鎹�鍊肩殑缁撴灉銆�  杩斿洖鐨勫瓧绗︿覆鐢卞崟寮曞彿鏍囨敞锛屾瘡渚嬮兘甯︽湁鍗曞紩鍙� (鈥�'鈥�)銆� 鍙嶆枩绾跨�﹀�� (鈥�\鈥�)銆� ASCII NUL浠ュ強鍓嶉潰鏈夊弽鏂滅嚎绗﹀彿鐨�Control-Z 銆傚�傛灉鑷�鍙橀噺鐨勫�间负NULL, 鍒欒繑鍥炰笉甯﹀崟寮曞彿鐨勫崟璇� 鈥�NULL鈥濄��

mysql> SELECT QUOTE('Don\'t!');

        -> 'Don\'t!'

mysql> SELECT QUOTE(NULL);

        -> NULL

• REPEAT(str,count)

杩斿洖涓�涓�鐢遍噸澶嶇殑瀛楃�︿覆str 缁勬垚鐨勫瓧绗︿覆锛屽瓧绗︿覆str鐨勬暟鐩�绛変��count 銆� 鑻� count <= 0,鍒欒繑鍥炰竴涓�绌哄瓧绗︿覆銆傝嫢str 鎴� count 涓� NULL锛屽垯杩斿洖 NULL 銆�

mysql> SELECT REPEAT('MySQL', 3);

        -> 'MySQLMySQLMySQL'

• REPLACE(str,from_str,to_str)

杩斿洖瀛楃�︿��str 浠ュ強鎵�鏈夎��瀛楃�︿��to_str鏇夸唬鐨勫瓧绗︿覆from_str 銆�

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');

        -> 'WwWwWw.mysql.com'

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冦��

• REVERSE(str)

杩斿洖瀛楃�︿�� str 锛岄『搴忓拰瀛楃�﹂『搴忕浉鍙嶃��

mysql> SELECT REVERSE('abc');

        -> 'cba'

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冦��

• RIGHT(str,len)

浠庡瓧绗︿覆str 寮�濮嬶紝杩斿洖鏈�鍙�len 瀛楃�︺��

mysql> SELECT RIGHT('foobarbar', 4);

        -> 'rbar'

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冦��

• RPAD(str,len,padstr)

杩斿洖瀛楃�︿��str, 鍏跺彸杈硅��瀛楃�︿�� padstr濉�琛ヨ��len 瀛楃�﹂暱搴︺�傚亣濡傚瓧绗︿覆str 鐨勯暱搴﹀ぇ浜� len,鍒欒繑鍥炲�艰��缂╃煭鍒颁笌 len 瀛楃�︾浉鍚岄暱搴︺��

mysql> SELECT RPAD('hi',5,'?');

        -> 'hi???'

mysql> SELECT RPAD('hi',1,'?');

        -> 'h'

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冦��

• RTRIM(str)

杩斿洖瀛楃�︿�� str 锛岀粨灏剧┖鏍煎瓧绗﹁��鍒犲幓銆�

mysql> SELECT RTRIM('barbar   ');

        -> 'barbar'

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冦��

• SOUNDEX(str)

浠�str杩斿洖涓�涓�soundex瀛楃�︿覆銆� 涓や釜鍏锋湁鍑犱箮鍚屾牱鎺㈡祴鐨勫瓧绗︿覆搴旇�ュ叿鏈夊悓鏍风�� soundex 瀛楃�︿覆銆備竴涓�鏍囧噯鐨�soundex 瀛楃�︿覆鐨勯暱搴︿��4涓�瀛楃�︼紝鐒惰��SOUNDEX() 鍑芥暟浼氳繑鍥炰竴涓�浜轰互闀垮害鐨勫瓧绗︿覆銆� 鍙�浣跨敤缁撴灉涓�鐨�SUBSTRING() 鏉ュ緱鍒颁竴涓�鏍囧�� soundex 瀛楃�︿覆銆傚湪str涓�锛�浼氬拷鐣ユ墍鏈夋湭鎸夌収瀛楁瘝椤哄簭鎺掑垪鐨勫瓧绗︺�� 鎵�鏈変笉鍦�A-Z鑼冨洿涔嬪唴鐨勫浗闄呭瓧姣嶇�﹀彿琚�瑙嗕负鍏冮煶瀛楁瘝銆�

mysql> SELECT SOUNDEX('Hello');

        -> 'H400'

mysql> SELECT SOUNDEX('Quadratically');

        -> 'Q36324'

娉ㄦ剰锛�杩欎釜鍑芥暟鎵ц�屽師濮嬬��Soundex绠楁硶锛岃�岄潪鏇村姞娴佽�岀殑鍔犲己鐗堟��(濡�D. Knuth鎵�杩�)銆傚叾鍖哄埆鍦ㄤ簬鍘熷�嬬増鏈�棣栧厛浼氬垹鍘诲厓闊筹紝鍏舵�℃槸閲嶅�嶏紝鑰屽姞寮虹増鍒欓�栧厛鍒犲幓閲嶅�嶏紝鑰屽悗鍒犲幓鍏冮煶銆�

• expr1 SOUNDS LIKE expr2

杩欑浉褰撲簬SOUNDEX(expr1) = SOUNDEX(expr2)銆�

• SPACE(N)

杩斿洖涓�涓�鐢�N 闂撮殧绗﹀彿缁勬垚鐨勫瓧绗︿覆銆�

mysql> SELECT SPACE(6);

        -> '      '

• SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

涓嶅甫鏈�len 鍙傛暟鐨勬牸寮忎粠瀛楃�︿��str杩斿洖涓�涓�瀛愬瓧绗︿覆锛岃捣濮嬩簬浣嶇�� pos銆傚甫鏈�len鍙傛暟鐨勬牸寮忎粠瀛楃�︿��str杩斿洖涓�涓�闀垮害鍚�len瀛楃�︾浉鍚岀殑瀛愬瓧绗︿覆锛岃捣濮嬩簬浣嶇�� pos銆� 浣跨敤 FROM鐨勬牸寮忎负鏍囧噯 SQL 璇�娉曘�備篃鍙�鑳藉��pos浣跨敤涓�涓�璐熷�笺�傚亣鑻ヨ繖鏍凤紝鍒欏瓙瀛楃�︿覆鐨勪綅缃�璧峰�嬩簬瀛楃�︿覆缁撳熬鐨�pos 瀛楃�︼紝鑰屼笉鏄�瀛楃�︿覆鐨勫紑澶翠綅缃�銆傚湪浠ヤ笅鏍煎紡鐨勫嚱鏁颁腑鍙�浠ュ��pos 浣跨敤涓�涓�璐熷�笺��

mysql> SELECT SUBSTRING('Quadratically',5);

        -> 'ratically'

mysql> SELECT SUBSTRING('foobarbar' FROM 4);

        -> 'barbar'

mysql> SELECT SUBSTRING('Quadratically',5,6);

        -> 'ratica'       

mysql> SELECT SUBSTRING('Sakila', -3);

        -> 'ila'       

mysql> SELECT SUBSTRING('Sakila', -5, 3);

        -> 'aki'

mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);

        -> 'ki'

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冦��

娉ㄦ剰锛屽�傛灉瀵�len浣跨敤鐨勬槸涓�涓�灏忎��1鐨勫�硷紝鍒欑粨鏋滃�嬬粓涓虹┖瀛楃�︿覆銆�

SUBSTR()鏄� SUBSTRING()鐨勫悓涔夎瘝銆�

• SUBSTRING_INDEX(str,delim,count)

鍦ㄥ畾鐣岀�� delim 浠ュ強count 鍑虹幇鍓嶏紝浠庡瓧绗︿覆str杩斿洖鑷�瀛楃�︿覆銆傝嫢count涓烘�ｅ��,鍒欒繑鍥炴渶缁堝畾鐣岀��(浠庡乏杈瑰紑濮�)宸﹁竟鐨勪竴鍒囧唴瀹广�傝嫢count涓鸿礋鍊硷紝鍒欒繑鍥炲畾鐣岀�︼紙浠庡彸杈瑰紑濮嬶級鍙宠竟鐨勪竴鍒囧唴瀹广��

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);

        -> 'www.mysql'

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);

        -> 'mysql.com'

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冦��

• TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)

杩斿洖瀛楃�︿�� str 锛� 鍏朵腑鎵�鏈�remstr 鍓嶇紑鍜�/鎴栧悗缂�閮藉凡琚�鍒犻櫎銆傝嫢鍒嗙被绗�BOTH銆�LEADIN鎴�TRAILING涓�娌℃湁涓�涓�鏄�缁欏畾鐨�,鍒欏亣璁句负BOTH 銆� remstr 涓哄彲閫夐」锛屽湪鏈�鎸囧畾鎯呭喌涓嬶紝鍙�鍒犻櫎绌烘牸銆�

mysql> SELECT TRIM('  bar   ');

        -> 'bar'

mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');

        -> 'barxxx'

mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');

        -> 'bar'

mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');

        -> 'barx'

杩欎釜鍑芥暟鏀�鎸佸�氬瓧鑺傚瓧鍏冦��

• UCASE(str)

UCASE()鏄�UPPER()鐨勫悓涔夎瘝銆�

• UNCOMPRESS(string_to_uncompress)

瀵圭粡COMPRESS()鍑芥暟鍘嬬缉鍚庣殑瀛楃�︿覆杩涜�岃В鍘嬬缉銆傝嫢鍙傛暟涓哄帇缂╁�硷紝鍒欑粨鏋滀负 NULL銆傝繖涓�鍑芥暟瑕佹��  MySQL 宸茶��璇稿��zlib 涔嬬被鐨勫帇缂╁簱缂栬瘧杩囥�傚惁鍒�, 杩斿洖鍊煎皢濮嬬粓鏄� NULL銆�

mysql> SELECT UNCOMPRESS(COMPRESS('any string'));

        -> 'any string'

mysql> SELECT UNCOMPRESS('any string');

        -> NULL

• UNCOMPRESSED_LENGTH(compressed_string)

杩斿洖鍘嬬缉瀛楃�︿覆鍘嬬缉鍓嶇殑闀垮害銆�

mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));

        -> 30

• UNHEX(str)

鎵ц�屼��HEX(str)鐨勫弽鍚戞搷浣溿�傚氨鏄�璇达紝瀹冨皢鍙傛暟涓�鐨勬瘡涓�瀵瑰崄鍏�杩涘埗鏁板瓧鐞嗚В涓轰竴涓�鏁板瓧锛屽苟灏嗗叾杞�鍖栦负璇ユ暟瀛椾唬琛ㄧ殑瀛楃�︺�傜粨鏋滃瓧绗︿互浜岃繘鍒跺瓧绗︿覆鐨勫舰寮忚繑鍥炪��

mysql> SELECT UNHEX('4D7953514C');

        -> 'MySQL'

mysql> SELECT 0x4D7953514C;

        -> 'MySQL'

mysql> SELECT UNHEX(HEX('string'));

        -> 'string'

mysql> SELECT HEX(UNHEX('1267'));

        -> '1267'

• UPPER(str)

杩斿洖瀛楃�︿��str锛� 浠ュ強鏍规嵁鏈�鏂板瓧绗﹂泦鏄犲皠杞�鍖栦负澶у啓瀛楁瘝鐨勫瓧绗� (榛樿�や��cp1252 Latin1).

mysql> SELECT UPPER('Hej');

        -> 'HEJ'

璇ュ嚱鏁版敮鎸佸�氬瓧鑺傚瓧鍏冦��

鏈�绔犺�鸿堪浜嗕竴浜涘彲鐢ㄤ簬鎿嶄綔鏃堕棿鍊肩殑鍑芥暟銆傚叧浜庢瘡涓�鏃堕棿鍜屾棩鏈熺被鍨嬪叿鏈夌殑鍊煎煙鍙婃寚瀹氬�肩殑鏈夋晥鏍煎紡锛岃�峰弬瑙�11.3鑺傦紝鈥滄棩鏈熷拰鏃堕棿绫诲瀷鈥�銆�

涓嬮潰鐨勪緥瀛愪娇鐢ㄤ簡鏃堕棿鍑芥暟銆備互涓嬭�㈤棶閫夋嫨浜嗘渶杩戠殑 30澶╁唴鎵�鏈夊甫鏈�date_col 鍊肩殑璁板綍锛�

mysql> SELECT something FROM tbl_name

    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

娉ㄦ剰锛岃繖涓�璇㈤棶涔熻兘閫夋嫨灏嗘潵鐨勬棩鏈熻�板綍銆� 

鐢ㄤ簬鏃ユ湡鍊肩殑鍑芥暟閫氬父浼氭帴鍙楁椂闂存棩鏈熷�艰�屽拷鐣ユ椂闂撮儴鍒嗐�傝�岀敤浜庢椂闂村�肩殑鍑芥暟閫氬父鎺ュ彈鏃堕棿鏃ユ湡鍊艰�屽拷鐣ユ棩鏈熼儴鍒嗐��

杩斿洖鍚勮嚜褰撳墠鏃ユ湡鎴栨椂闂寸殑鍑芥暟鍦ㄦ瘡娆¤�㈤棶鎵ц�屽紑濮嬫椂璁＄畻涓�娆°�傝繖鎰忓懗鐫�鍦ㄤ竴涓�鍗曚竴璇㈤棶涓�锛屽�硅�稿��NOW() 鐨勫嚱鏁板�氭�¤�块棶鎬绘槸浼氬緱鍒板悓鏍风殑缁撴灉(鏈�杈惧埌鎴戜滑鐨勭洰鐨勶紝鍗曚竴璇㈤棶涔熷寘鎷�瀵瑰瓨鍌ㄧ▼搴忔垨瑙﹀彂鍣ㄥ拰琚�璇ョ▼搴�/瑙﹀彂鍣ㄨ皟鐢ㄧ殑鎵�鏈夊瓙绋嬪簭鐨勮皟鐢� )銆傝繖椤瑰師鍒欎篃閫傜敤浜� CURDATE()銆� CURTIME()銆� UTC_DATE()銆� UTC_TIME()銆�UTC_TIMESTAMP()锛屼互鍙婃墍鏈夊拰瀹冧滑鎰忎箟鐩稿悓鐨勫嚱鏁般��

CURRENT_TIMESTAMP()銆� CURRENT_TIME()銆� CURRENT_DATE()浠ュ強FROM_UNIXTIME()鍑芥暟杩斿洖杩炴帴褰撳墠鏃跺尯鍐呯殑鍊硷紝杩欎釜鍊煎彲鐢ㄤ綔time_zone绯荤粺鍙橀噺鐨勫�笺�傛�ゅ�栵紝 UNIX_TIMESTAMP() 鍋囪�惧叾鍙傛暟涓轰竴涓�褰撳墠鏃跺尯鐨勬椂闂存棩鏈熷�笺�傝�峰弬瑙�5.10.8鑺傦紝鈥淢ySQL鏈嶅姟鍣ㄦ椂鍖烘敮鎸佲��銆�

浠ヤ笅鍑芥暟鐨勮�鸿堪涓�杩斿洖鍊肩殑鑼冨洿浼氳�锋眰瀹屽叏鏃ユ湡銆� 鑻ヤ竴涓�鏃ユ湡涓衡�滈浂鈥� 鍊硷紝鎴栬�呮槸涓�涓�璇稿��'2001-11-00'涔嬬被鐨勪笉瀹屽叏鏃ユ湡锛� 鎻愬彇閮ㄥ垎鏃ユ湡鍊肩殑鍑芥暟鍙�鑳戒細杩斿�� 0銆� 渚嬪�傦�� DAYOFMONTH('2001-11-00') 浼氳繑鍥�0銆�

• ADDDATE(date,INTERVAL expr type) ADDDATE(expr,days)

褰撹��绗�浜屼釜鍙傛暟鐨�INTERVAL鏍煎紡婵�娲诲悗锛� ADDDATE()灏辨槸DATE_ADD()鐨勫悓涔夎瘝銆傜浉鍏冲嚱鏁�SUBDATE() 鍒欐槸DATE_SUB()鐨勫悓涔夎瘝銆傚�逛��INTERVAL鍙傛暟涓婄殑淇℃伅 锛岃�峰弬瑙佸叧浜�DATE_ADD()鐨勮�鸿堪銆�

mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);

        -> '1998-02-02'

mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);

        -> '1998-02-02'

鑻� days 鍙傛暟鍙�鏄�鏁存暟鍊硷紝鍒� MySQL 5.1灏嗗叾浣滀负澶╂暟鍊兼坊鍔犺嚦 expr銆�

mysql> SELECT ADDDATE('1998-01-02', 31);

        -> '1998-02-02'

• ADDTIME(expr,expr2)

ADDTIME()灏� expr2娣诲姞鑷�expr 鐒跺悗杩斿洖缁撴灉銆� expr 鏄�涓�涓�鏃堕棿鎴栨椂闂存棩鏈熻〃杈惧紡锛岃��expr2 鏄�涓�涓�鏃堕棿琛ㄨ揪寮忋��

mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999',

    ->                '1 1:1:1.000002');

        -> '1998-01-02 01:01:01.000001'

mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

        -> '03:00:01.999997'

• CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ() 灏嗘椂闂存棩鏈熷��dt 浠�from_tz 缁欏嚭鐨勬椂鍖鸿浆鍒�to_tz缁欏嚭鐨勬椂鍖猴紝鐒跺悗杩斿洖缁撴灉鍊笺�傚叧浜庡彲鑳芥寚瀹氱殑鏃跺尯鐨勮�︾粏璁鸿堪锛岃�峰弬瑙�5.10.8鑺傦紝鈥淢ySQL鏈嶅姟鍣ㄦ椂鍖烘敮鎸佲��銆傝嫢鑷�鍙橀噺鏃犳晥锛屽垯杩欎釜鍑芥暟浼氳繑鍥� NULL銆�

鍦ㄤ粠鑻�from_tz 鍒�UTC鐨勮浆鍖栬繃绋嬩腑锛岃�ュ�艰秴鍑� TIMESTAMP 绫诲瀷鐨勮��鏀�鎸佽寖鍥达紝閭ｄ箞杞�鍖栦笉浼氬彂鐢熴�傚叧浜� TIMESTAMP 鑼冨洿鐨勮�鸿堪锛岃�峰弬瑙�11.1.2鑺傦紝鈥滄棩鏈熷拰鏃堕棿绫诲瀷姒傝堪鈥�銆�

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

        -> '2004-01-01 13:00:00'

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

        -> '2004-01-01 22:00:00'

娉ㄩ噴锛�鑻ヨ�佷娇鐢ㄨ�稿�� 'MET'鎴� 'Europe/Moscow'涔嬬被鐨勬寚瀹氭椂闂村尯锛岄�栧厛瑕佽�剧疆姝ｇ‘鐨勬椂鍖鸿〃銆傝�︾粏璇存槑瑙�5.10.8鑺傦紝鈥淢ySQL鏈嶅姟鍣ㄦ椂鍖烘敮鎸佲��銆� 

• CURDATE()

灏嗗綋鍓嶆棩鏈熸寜鐓�'YYYY-MM-DD' 鎴�YYYYMMDD 鏍煎紡鐨勫�艰繑鍥烇紝鍏蜂綋鏍煎紡鏍规嵁鍑芥暟鐢ㄥ湪瀛楃�︿覆鎴栨槸鏁板瓧璇�澧冧腑鑰屽畾銆�

mysql> SELECT CURDATE();

        -> '1997-12-15'

mysql> SELECT CURDATE() + 0;

        -> 19971215

• CURRENT_DATE CURRENT_DATE()

CURRENT_DATE鍜�CURRENT_DATE()鏄�鐨勫悓涔夎��.

• CURTIME()

灏嗗綋鍓嶆椂闂翠互'HH:MM:SS'鎴� HHMMSS 鐨勬牸寮忚繑鍥烇紝 鍏蜂綋鏍煎紡鏍规嵁鍑芥暟鐢ㄥ湪瀛楃�︿覆鎴栨槸鏁板瓧璇�澧冧腑鑰屽畾銆� 

mysql> SELECT CURTIME();

        -> '23:50:26'

mysql> SELECT CURTIME() + 0;

        -> 235026

• CURRENT_TIME, CURRENT_TIME()

CURRENT_TIME 鍜�CURRENT_TIME() 鏄�CURTIME()鐨勫悓涔夎瘝銆�

• CURRENT_TIMESTAMP, CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP鍜� CURRENT_TIMESTAMP()鏄�NOW()鐨勫悓涔夎瘝銆�

• DATE(expr)

鎻愬彇鏃ユ湡鎴栨椂闂存棩鏈熻〃杈惧紡expr涓�鐨勬棩鏈熼儴鍒嗐��

mysql> SELECT DATE('2003-12-31 01:02:03');

        -> '2003-12-31'

• DATEDIFF(expr,expr2)

DATEDIFF() 杩斿洖璧峰�嬫椂闂� expr鍜岀粨鏉熸椂闂�expr2涔嬮棿鐨勫ぉ鏁般��Expr鍜�expr2 涓烘棩鏈熸垨 date-and-time 琛ㄨ揪寮忋�傝�＄畻涓�鍙�鐢ㄥ埌杩欎簺鍊肩殑鏃ユ湡閮ㄥ垎銆�

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');

        -> 1

mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');

        -> -31

• DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type)

杩欎簺鍑芥暟鎵ц�屾棩鏈熻繍绠椼�� date 鏄�涓�涓� DATETIME 鎴�DATE鍊硷紝鐢ㄦ潵鎸囧畾璧峰�嬫椂闂淬�� expr 鏄�涓�涓�琛ㄨ揪寮忥紝鐢ㄦ潵鎸囧畾浠庤捣濮嬫棩鏈熸坊鍔犳垨鍑忓幓鐨勬椂闂撮棿闅斿�笺��  Expr鏄�涓�涓�瀛楃�︿覆;瀵逛簬璐熷�肩殑鏃堕棿闂撮殧锛屽畠鍙�浠ヤ互涓�涓� 鈥�-鈥欏紑澶淬�� type 涓哄叧閿�璇嶏紝瀹冩寚绀轰簡琛ㄨ揪寮忚��瑙ｉ噴鐨勬柟寮忋�� 

鍏抽敭璇�INTERVA鍙� type 鍒嗙被绗﹀潎涓嶅尯鍒嗗ぇ灏忓啓銆�

浠ヤ笅琛ㄦ樉绀轰簡type 鍜�expr 鍙傛暟鐨勫叧绯伙細

MySQL 鍏佽�镐换浣�expr 鏍煎紡涓�鐨勬爣鐐瑰垎闅旂�︺�傝〃涓�鎵�鏄剧ず鐨勬槸寤鸿��鐨� 鍒嗛殧绗︺�傝嫢 date 鍙傛暟鏄�涓�涓� DATE 鍊硷紝鑰屼綘鐨勮�＄畻鍙�浼氬寘鎷� YEAR銆�MONTH鍜�DAY閮ㄥ垎(鍗�, 娌℃湁鏃堕棿閮ㄥ垎), 鍏剁粨鏋滄槸涓�涓�DATE 鍊笺�傚惁鍒欙紝缁撴灉灏嗘槸涓�涓� DATETIME鍊笺��

鑻ヤ綅浜庡彟涓�绔�鐨勮〃杈惧紡鏄�涓�涓�鏃ユ湡鎴栨棩鏈熸椂闂村�� 锛� 鍒�INTERVAL expr type鍙�鍏佽�稿湪 + 鎿嶄綔绗︾殑涓ょ��銆傚�逛�� 鈥撴搷浣滅�︼��  INTERVAL expr type 鍙�鍏佽�稿湪鍏跺彸绔�锛屽師鍥犳槸浠庝竴涓�鏃堕棿闂撮殧涓�鎻愬彇涓�涓�鏃ユ湡鎴栨棩鏈熸椂闂村�兼槸姣�鏃犳剰涔夌殑銆� (瑙佷笅闈㈢殑渚嬪瓙锛�銆�

mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;

        -> '1998-01-01 00:00:00'

mysql> SELECT INTERVAL 1 DAY + '1997-12-31';

        -> '1998-01-01'

mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND;

        -> '1997-12-31 23:59:59'

mysql> SELECT DATE_ADD('1997-12-31 23:59:59',

    ->                 INTERVAL 1 SECOND);

        -> '1998-01-01 00:00:00'

mysql> SELECT DATE_ADD('1997-12-31 23:59:59',

    ->                 INTERVAL 1 DAY);

        -> '1998-01-01 23:59:59'

mysql> SELECT DATE_ADD('1997-12-31 23:59:59',

    ->                 INTERVAL '1:1' MINUTE_SECOND);

        -> '1998-01-01 00:01:00'

mysql> SELECT DATE_SUB('1998-01-01 00:00:00',

    ->                 INTERVAL '1 1:1:1' DAY_SECOND);

        -> '1997-12-30 22:58:59'

mysql> SELECT DATE_ADD('1998-01-01 00:00:00',

    ->                 INTERVAL '-1 10' DAY_HOUR);

        -> '1997-12-30 14:00:00'

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

        -> '1997-12-02'

mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',

    ->            INTERVAL '1.999999' SECOND_MICROSECOND);

        -> '1993-01-01 00:00:01.000001'

鑻ヤ綘鎸囧畾浜嗕竴涓�杩囦簬鐭�鐨勬椂闂撮棿闅斿�� (涓嶅寘鎷�type 鍏抽敭璇嶆墍棰勬湡鐨勬墍鏈夋椂闂撮棿闅旈儴鍒�), MySQL 鍋囧畾浣犲凡缁忕渷鍘讳簡鏃堕棿闂撮殧鍊肩殑鏈�宸﹂儴鍒嗐�� 渚嬪�傦紝浣犳寚瀹氫簡涓�绉嶇被鍨嬬殑DAY_SECOND, expr 鐨勫�奸�勬湡搴斿綋鍏锋湁澶┿�� 灏忔椂銆佸垎閽熷拰绉掗儴鍒嗐�傝嫢浣犳寚瀹氫簡涓�涓�绫讳�� '1:10'鐨勫��, MySQL 鍋囧畾澶╁拰灏忔椂閮ㄥ垎涓嶅瓨鍦�锛岄偅涔堣繖涓�鍊间唬琛ㄥ垎鍜岀�掋�傛崲瑷�涔�, '1:10' DAY_SECOND 琚�瑙ｉ噴涓虹浉褰撲�� '1:10' MINUTE_SECOND銆傝繖鐩稿綋浜� MySQL灏�TIME 鍊艰В閲婁负鎵�鑰楄垂鐨勬椂闂磋�屼笉鏄�鏃ユ椂鐨勮В閲婃柟寮忋��             

鍋囧�備綘瀵逛竴涓�鏃ユ湡鍊兼坊鍔犳垨鍑忓幓涓�浜涘惈鏈夋椂闂撮儴鍒嗙殑鍐呭�癸紝鍒欑粨鏋滆嚜鍔ㄨ浆鍖栦负涓�涓�鏃ユ湡鏃堕棿鍊硷細

mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);

        -> '1999-01-02'

mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);

        -> '1999-01-01 01:00:00'

鍋囧�備綘浣跨敤浜嗘牸寮忎弗閲嶉敊璇�鐨勬棩鏈�,鍒欑粨鏋滀负 NULL銆傚亣濡備綘娣诲姞浜�  MONTH銆�YEAR_MONTH鎴�YEAR 锛岃�岀粨鏋滄棩鏈熶腑鏈変竴澶╃殑鏃ユ湡澶т簬娣诲姞鐨勬湀浠界殑鏃ユ湡鏈�澶ч檺搴︼紝鍒欒繖涓�鏃ユ湡鑷�鍔ㄨ��璋冩暣涓烘坊鍔犳湀浠界殑鏈�澶ф棩鏈燂細

mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);

        -> '1998-02-28'

• DATE_FORMAT(date,format)

鏍规嵁format 瀛楃�︿覆瀹夋��date 鍊肩殑鏍煎紡銆�

浠ヤ笅璇存槑绗﹀彲鐢ㄥ湪 format 瀛楃�︿覆涓�锛�

鎵�鏈夊叾瀹冨瓧绗﹂兘琚�澶嶅埗鍒扮粨鏋滀腑锛�鏃犻渶浣滃嚭瑙ｉ噴銆�

娉ㄦ剰锛� 鈥�%鈥欏瓧绗﹁�佹眰鍦ㄦ牸寮忔寚瀹氱�︿箣鍓嶃��

鏈堜唤鍜屾棩鏈熻�存槑绗︾殑鑼冨洿浠庨浂寮�濮嬶紝鍘熷洜鏄� MySQL鍏佽�稿瓨鍌ㄨ�稿�� '2004-00-00'鐨勪笉瀹屽叏鏃ユ湡.

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

        -> 'Saturday October 1997'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');

        -> '22:23:00'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

                          '%D %y %a %d %m %b %j');

        -> '4th 97 Sat 04 10 Oct 277'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

                          '%H %k %I %r %T %S %w');

        -> '22 22 10 10:23:00 PM 22:23:00 00 6'

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');

        -> '1998 52'

• DAY(date)

DAY() 鍜�DAYOFMONTH()鐨勬剰涔夌浉鍚屻��

• DAYNAME(date)

杩斿洖date 瀵瑰簲鐨勫伐浣滄棩鍚嶇О銆�

mysql> SELECT DAYNAME('1998-02-05');

        -> '鍛ㄥ洓'

• DAYOFMONTH(date)

杩斿洖date 瀵瑰簲鐨勮�ユ湀鏃ユ湡锛岃寖鍥存槸浠� 1鍒�31銆�

mysql> SELECT DAYOFMONTH('1998-02-03');

        -> 3

• DAYOFWEEK(date)

杩斿洖date (1 = 鍛ㄦ棩, 2 = 鍛ㄤ竴, ..., 7 = 鍛ㄥ叚)瀵瑰簲鐨勫伐浣滄棩绱㈠紩銆傝繖浜涚储寮曞�肩�﹀�� ODBC鏍囧噯銆�

mysql> SELECT DAYOFWEEK('1998-02-03');

        -> 3

• DAYOFYEAR(date)

杩斿洖date 瀵瑰簲鐨勪竴骞翠腑鐨勫ぉ鏁帮紝鑼冨洿鏄�浠� 1鍒�366銆�

mysql> SELECT DAYOFYEAR('1998-02-03');

        -> 34

• EXTRACT(type FROM date)

EXTRACT()鍑芥暟鎵�浣跨敤鐨勬椂闂撮棿闅旂被鍨嬭�存槑绗﹀�� DATE_ADD()鎴�DATE_SUB()鐨勭浉鍚�,浣嗗畠浠庢棩鏈熶腑鎻愬彇鍏堕儴鍒嗭紝鑰屼笉鏄�鎵ц�屾棩鏈熻繍绠椼�� 

mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');

       -> 1999

mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');

       -> 199907

mysql> SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');

       -> 20102

mysql> SELECT EXTRACT(MICROSECOND

    ->                FROM '2003-01-02 10:30:00.00123');

        -> 123

• FROM_DAYS(N)

缁欏畾涓�涓�澶╂��  N, 杩斿洖涓�涓�DATE鍊笺��

mysql> SELECT FROM_DAYS(729669);

        -> '1997-10-07'

浣跨敤 FROM_DAYS()澶勭悊鍙よ�佹棩鏈熸椂锛屽姟蹇呰皑鎱庛�備粬涓嶇敤浜庡�勭悊闃冲巻鍑虹幇鍓嶇殑鏃ユ��(1582)銆傝�峰弬瑙�12.6鑺傦紝鈥淢ySQL浣跨敤浠�涔堟棩鍘嗭紵鈥�銆�

• FROM_UNIXTIME(unix_timestamp) , FROM_UNIXTIME(unix_timestamp,format)

杩斿洖'YYYY-MM-DD HH:MM:SS'鎴�YYYYMMDDHHMMSS 鏍煎紡鍊肩殑unix_timestamp鍙傛暟琛ㄧず锛屽叿浣撴牸寮忓彇鍐充簬璇ュ嚱鏁版槸鍚︾敤鍦ㄥ瓧绗︿覆涓�鎴栨槸鏁板瓧璇�澧冧腑銆�

鑻�format 宸茬粡缁欏嚭锛屽垯缁撴灉鐨勬牸寮忔槸鏍规嵁format 瀛楃�︿覆鑰屽畾銆� format 鍙�浠ュ寘鍚�鍚�DATE_FORMAT() 鍑芥暟杈撳叆椤瑰垪琛ㄤ腑鐩稿悓鐨勮�存槑绗︺��

mysql> SELECT FROM_UNIXTIME(875996580);

        -> '1997-10-04 22:23:00'

mysql> SELECT FROM_UNIXTIME(875996580) + 0;

        -> 19971004222300

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),

    ->                      '%Y %D %M %h:%i:%s %x');

        -> '2003 6th August 06:22:58 2003'

• GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL')

杩斿洖涓�涓�鏍煎紡瀛楃�︿覆銆傝繖涓�鍑芥暟鍦ㄥ��DATE_FORMAT() 鍙�STR_TO_DATE()鍑芥暟缁撳悎鏃跺緢鏈夌敤銆�

绗�涓�涓�鍙傛暟鐨�3涓�鍙�鑳藉�煎拰绗�浜屼釜鍙傛暟鐨�5涓�鍙�鑳藉�间骇鐢� 15 涓�鍙�鑳芥牸寮忓瓧绗︿覆 (瀵逛簬浣跨敤鐨勮�存槑绗︼紝璇峰弬瑙�DATE_FORMAT()鍑芥暟璇存槑琛� )銆�

ISO 鏍煎紡涓�ISO 9075, 鑰岄潪ISO 8601.

涔熷彲浠ヤ娇鐢�TIMESTAMP, 杩欐椂GET_FORMAT()鐨勮繑鍥炲�煎拰DATETIME鐩稿悓銆�

mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));

        -> '03.10.2003'

mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));

        -> '2003-10-31'

鎴栬��13.5.3鑺傦紝鈥淪ET璇�娉曗��銆�

• HOUR(time)

杩斿洖time 瀵瑰簲鐨勫皬鏃舵暟銆傚�逛簬鏃ユ椂鍊肩殑杩斿洖鍊艰寖鍥存槸浠� 0 鍒� 23 銆�

mysql> SELECT HOUR('10:05:03');

        -> 10

鐒惰��,  TIME 鍊肩殑鑼冨洿瀹為檯涓婇潪甯稿ぇ, 鎵�浠�HOUR鍙�浠ヨ繑鍥炲ぇ浜�23鐨勫�笺��

mysql> SELECT HOUR('272:59:59');

        -> 272

• LAST_DAY(date)

鑾峰彇涓�涓�鏃ユ湡鎴栨棩鏈熸椂闂村�硷紝杩斿洖璇ユ湀鏈�鍚庝竴澶╁�瑰簲鐨勫�笺�傝嫢鍙傛暟鏃犳晥锛屽垯杩斿洖NULL銆�

mysql> SELECT LAST_DAY('2003-02-05');

        -> '2003-02-28'

mysql> SELECT LAST_DAY('2004-02-05');

        -> '2004-02-29'

mysql> SELECT LAST_DAY('2004-01-01 01:01:01');

        -> '2004-01-31'

mysql> SELECT LAST_DAY('2003-03-32');

        -> NULL

• LOCALTIME, LOCALTIME()

LOCALTIME 鍙� LOCALTIME()鍜�NOW()鍏锋湁鐩稿悓鎰忎箟銆�

• LOCALTIMESTAMP, LOCALTIMESTAMP()

LOCALTIMESTAMP鍜�LOCALTIMESTAMP()鍜�NOW()鍏锋湁鐩稿悓鎰忎箟銆�

• MAKEDATE(year,dayofyear)

缁欏嚭骞翠唤鍊煎拰涓�骞翠腑鐨勫ぉ鏁�鍊硷紝杩斿洖涓�涓�鏃ユ湡銆�dayofyear 蹇呴』澶т簬 0 锛屽惁鍒欑粨鏋滀负 NULL銆�

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);

        -> '2001-01-31', '2001-02-01'

mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);

        -> '2001-12-31', '2004-12-30'

mysql> SELECT MAKEDATE(2001,0);

        -> NULL

• MAKETIME(hour,minute,second)

杩斿洖鐢�hour銆� minute鍜�second 鍙傛暟璁＄畻寰楀嚭鐨勬椂闂村�笺��

mysql> SELECT MAKETIME(12,15,30);

        -> '12:15:30'

• MICROSECOND(expr)

浠庢椂闂存垨鏃ユ湡鏃堕棿琛ㄨ揪寮�expr杩斿洖寰�绉掑�硷紝鍏舵暟瀛楄寖鍥翠粠 0鍒� 999999銆�

mysql> SELECT MICROSECOND('12:00:00.123456');

        -> 123456

mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010');

        -> 10

• MINUTE(time)

杩斿洖 time 瀵瑰簲鐨勫垎閽熸暟,鑼冨洿鏄�浠� 0 鍒� 59銆�

mysql> SELECT MINUTE('98-02-03 10:05:03');

        -> 5

• MONTH(date)

杩斿洖date 瀵瑰簲鐨勬湀浠斤紝鑼冨洿鏃朵粠 1 鍒� 12銆�

mysql> SELECT MONTH('1998-02-03');

        -> 2

• MONTHNAME(date)

杩斿洖date 瀵瑰簲鏈堜唤鐨勫叏鍚嶃��

mysql> SELECT MONTHNAME('1998-02-05');

        -> 'February '

• NOW()

杩斿洖褰撳墠鏃ユ湡鍜屾椂闂村�硷紝鍏舵牸寮忎负 'YYYY-MM-DD HH:MM:SS' 鎴�YYYYMMDDHHMMSS 锛� 鍏蜂綋鏍煎紡鍙栧喅浜庤�ュ嚱鏁版槸鍚︾敤鍦ㄥ瓧绗︿覆涓�鎴栨暟瀛楄��澧冧腑銆�

mysql> SELECT NOW();

        -> '1997-12-15 23:50:26'

mysql> SELECT NOW() + 0;

        -> 19971215235026

鍦ㄤ竴涓�瀛樺偍绋嬪簭鎴栬Е鍙戝櫒鍐�, NOW() 杩斿洖涓�涓�甯告暟鏃堕棿锛岃�ュ父鏁版寚绀轰簡璇ョ▼搴忔垨瑙﹀彂璇�鍙ュ紑濮嬫墽琛岀殑鏃堕棿銆傝繖鍚�SYSDATE()鐨勮繍琛屾湁鎵�涓嶅悓銆�

• PERIOD_ADD(P,N)

娣诲姞 N 涓�鏈堣嚦鍛ㄦ��P (鏍煎紡涓�YYMM 鎴�YYYYMM)锛岃繑鍥炲�肩殑鏍煎紡涓� YYYYMM銆傛敞鎰忓懆鏈熷弬鏁� P 涓嶆槸鏃ユ湡鍊笺�� 

mysql> SELECT PERIOD_ADD(9801,2);

        -> 199803

• PERIOD_DIFF(P1,P2) 

杩斿洖鍛ㄦ湡P1鍜� P2 涔嬮棿鐨勬湀浠芥暟銆�P1 鍜�P2 鐨勬牸寮忓簲璇ヤ负YYMM鎴�YYYYMM銆傛敞鎰忓懆鏈熷弬鏁� P1鍜�P2 涓嶆槸鏃ユ湡鍊笺��

mysql> SELECT PERIOD_DIFF(9802,199703);

        -> 11

• QUARTER(date)

杩斿洖date 瀵瑰簲鐨勪竴骞翠腑鐨勫�ｅ害鍊硷紝鑼冨洿鏄�浠� 1鍒� 4銆�

mysql> SELECT QUARTER('98-04-01');

        -> 2

• SECOND(time)

杩斿洖time 瀵瑰簲鐨勭�掓��, 鑼冨洿鏄�浠� 0鍒�59銆�

mysql> SELECT SECOND('10:05:03');

        -> 3

• SEC_TO_TIME(seconds)

杩斿洖琚�杞�鍖栦负灏忔椂銆� 鍒嗛挓鍜岀�掓暟鐨�seconds鍙傛暟鍊�, 鍏舵牸寮忎负 'HH:MM:SS' 鎴�HHMMSS锛屽叿浣撴牸寮忔牴鎹�璇ュ嚱鏁版槸鍚︾敤鍦ㄥ瓧绗︿覆鎴栨暟瀛楄��澧冧腑鑰屽畾銆�

mysql> SELECT SEC_TO_TIME(2378);

        -> '00:39:38'

mysql> SELECT SEC_TO_TIME(2378) + 0;

        -> 3938

• STR_TO_DATE(str,format)

杩欐槸DATE_FORMAT() 鍑芥暟鐨勫�掕浆銆傚畠鑾峰彇涓�涓�瀛楃�︿覆 str 鍜屼竴涓�鏍煎紡瀛楃�︿覆format銆傝嫢鏍煎紡瀛楃�︿覆鍖呭惈鏃ユ湡鍜屾椂闂撮儴鍒嗭紝鍒� STR_TO_DATE()杩斿洖涓�涓� DATETIME 鍊硷紝 鑻ヨ�ュ瓧绗︿覆鍙�鍖呭惈鏃ユ湡閮ㄥ垎鎴栨椂闂撮儴鍒嗭紝鍒欒繑鍥炰竴涓� DATE 鎴�TIME鍊笺��

str鎵�鍖呭惈鐨勬棩鏈熴�佹椂闂存垨鏃ユ湡鏃堕棿鍊煎簲璇ュ湪format鎸囩ず鐨勬牸寮忎腑琚�缁欏畾銆傚�逛簬鍙�鐢ㄥ湪format涓�鐨勮�存槑绗︼紝璇峰弬瑙�DATE_FORMAT() 鍑芥暟璇存槑琛ㄣ�� 鎵�鏈夊叾瀹冪殑瀛楃�﹁��閫愬瓧鑾峰彇锛屽洜姝や笉浼氳��瑙ｉ噴銆傝嫢 str 鍖呭惈涓�涓�闈炴硶鏃ユ湡銆佹椂闂存垨鏃ユ湡鏃堕棿鍊硷紝鍒� STR_TO_DATE()杩斿洖NULL銆傚悓鏃讹紝涓�涓�闈炴硶鍊间細寮曡捣璀﹀憡銆�

瀵规棩鏈熷�奸儴鍒嗙殑鑼冨洿妫�鏌ュ湪11.3.1鑺傦紝鈥淒ATETIME銆丏ATE鍜孴IMESTAMP绫诲瀷鈥�鏈夎�︾粏璇�鏄庛�傚叾鎰忎箟鏄�,渚嬪��, 鍙�瑕佸叿浣撴棩鏈熼儴鍒嗙殑鑼冨洿鏃朵�� 1鍒� 31涔嬮棿锛屽垯鍏佽�镐竴涓�鏃ユ湡涓�鐨勫叿浣撴棩鏈熼儴鍒嗗ぇ浜庝竴涓�鏈堜腑澶╂暟鍊笺�傚苟涓旓紝鍏佽�糕�滈浂鈥濇棩鏈熸垨甯︽湁0鍊奸儴鍒嗙殑鏃ユ湡銆�

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');

        -> '0000-00-00'

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');

        -> '2004-04-31'

• SUBDATE(date,INTERVAL expr type) SUBDATE(expr,days)

褰撹��绗�浜屼釜鍙傛暟鐨� INTERVAL鍨嬪紡璋冪敤鏃�, SUBDATE()鍜�DATE_SUB()鐨勬剰涔夌浉鍚屻�傚�逛簬鏈夊��INTERVAL鍙傛暟鐨勪俊鎭�锛� 瑙佹湁鍏� DATE_ADD()鐨勮�ㄨ�恒��

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

        -> '1997-12-02'

mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);

        -> '1997-12-02'

绗�浜屼釜褰㈠紡鍏佽�稿��days浣跨敤鏁存暟鍊笺�傚湪杩欎簺鎯呭喌涓嬶紝瀹冭��绠椾綔鐢辨棩鏈熸垨鏃ユ湡鏃堕棿琛ㄨ揪寮� expr涓�鎻愬彇鐨勫ぉ鏁般��

mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31);

        -> '1997-12-02 12:00:00'

娉ㄦ剰涓嶈兘浣跨敤鏍煎紡 "%X%V" 鏉ュ皢涓�涓� year-week 瀛楃�︿覆杞�鍖栦负涓�涓�鏃ユ湡锛屽師鍥犳槸褰撲竴涓�鏄熸湡璺ㄨ秺涓�涓�鏈堜唤鐣岄檺鏃讹紝涓�涓�骞村拰鏄熸湡鐨勭粍鍚堜笉鑳芥爣绀轰竴涓�鍞�涓�鐨勫勾鍜屾湀浠姐�傝嫢瑕佸皢year-week杞�鍖栦负涓�涓�鏃ユ湡锛屽垯涔熷簲鎸囧畾鍏蜂綋宸ヤ綔鏃ワ��

mysql> select str_to_date('200442 Monday', '%X%V %W');

-> 2004-10-18

• SUBTIME(expr,expr2)

SUBTIME()浠�expr 涓�鎻愬��expr2 锛岀劧鍚庤繑鍥炵粨鏋溿��expr 鏄�涓�涓�鏃堕棿鎴栨棩鏈熸椂闂磋〃杈惧紡锛岃��xpr2 鏄�涓�涓�鏃堕棿琛ㄨ揪寮忋��

mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');

        -> '1997-12-30 22:58:58.999997'

mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');

        -> '-00:59:59.999999'

• SYSDATE()

杩斿洖褰撳墠鏃ユ湡鍜屾椂闂村�硷紝鏍煎紡涓�'YYYY-MM-DD HH:MM:SS' 鎴�YYYYMMDDHHMMSS锛� 鍏蜂綋鏍煎紡鏍规嵁鍑芥暟鏄�鍚︾敤鍦ㄥ瓧绗︿覆鎴栨暟瀛楄��澧冭�屽畾銆�

鍦ㄤ竴涓�瀛樺偍绋嬪簭鎴栬Е鍙戝櫒涓�, SYSDATE()杩斿洖鍏舵墽琛岀殑鏃堕棿, 鑰岄潪瀛樺偍鎴愰兘鎴栬Е鍙戣��鍙ュ紑濮嬫墽琛岀殑鏃堕棿銆傝繖涓�NOW()鐨勮繍浣滄湁鎵�涓嶅悓銆�

• TIME(expr)

鎻愬彇涓�涓�鏃堕棿鎴栨棩鏈熸椂闂磋〃杈惧紡鐨勬椂闂撮儴鍒嗭紝骞跺皢鍏朵互瀛楃�︿覆褰㈠紡杩斿洖銆�

mysql> SELECT TIME('2003-12-31 01:02:03');

        -> '01:02:03'

mysql> SELECT TIME('2003-12-31 01:02:03.000123');

        -> '01:02:03.000123'

• TIMEDIFF(expr,expr2)

TIMEDIFF() 杩斿洖璧峰�嬫椂闂� expr 鍜岀粨鏉熸椂闂�expr2 涔嬮棿鐨勬椂闂淬�� expr 鍜�expr2 涓烘椂闂存垨 date-and-time 琛ㄨ揪寮�,涓や釜鐨勭被鍨嬪繀椤讳竴鏍枫�� 

mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',

    ->                 '2000:01:01 00:00:00.000001');

        -> '-00:00:00.000001'

mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',

    ->                 '1997-12-30 01:01:01.000002');

        -> '46:58:57.999999'

• TIMESTAMP(expr) , TIMESTAMP(expr,expr2)

瀵逛簬涓�涓�鍗曞弬鏁�,璇ュ嚱鏁板皢鏃ユ湡鎴栨棩鏈熸椂闂磋〃杈惧紡 expr 浣滀负鏃ユ湡鏃堕棿鍊艰繑鍥�.瀵逛簬涓や釜鍙傛暟, 瀹冨皢鏃堕棿琛ㄨ揪寮� expr2 娣诲姞鍒版棩鏈熸垨鏃ユ湡鏃堕棿琛ㄨ揪寮� expr 涓�锛屽��theresult浣滀负鏃ユ湡鏃堕棿鍊艰繑鍥炪��

mysql> SELECT TIMESTAMP('2003-12-31');

        -> '2003-12-31 00:00:00'

mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');

        -> '2004-01-01 00:00:00'

• TIMESTAMPADD(interval,int_expr,datetime_expr)

灏嗘暣鍨嬭〃杈惧紡int_expr 娣诲姞鍒版棩鏈熸垨鏃ユ湡鏃堕棿琛ㄨ揪寮� datetime_expr涓�銆� int_expr 鐨勫崟浣嶈��鏃堕棿闂撮殧鍙傛暟缁欏畾锛岃�ュ弬鏁板繀椤绘槸浠ヤ笅鍊肩殑鍏朵腑涓�涓�锛� FRAC_SECOND銆�SECOND銆� MINUTE銆� HOUR銆� DAY銆� WEEK銆� MONTH銆� QUARTER鎴� YEAR銆�

鍙�浣跨敤鎵�鏄剧ず鐨勫叧閿�璇嶆寚瀹�Interval鍊硷紝鎴栦娇鐢�SQL_TSI_鍓嶇紑銆備緥濡�, DAY鎴�SQL_TSI_DAY 閮芥槸姝ｇ‘鐨勩��

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');

        -> '2003-01-02 00:01:00'

mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');

        -> '2003-01-09'

• TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)

杩斿洖鏃ユ湡鎴栨棩鏈熸椂闂磋〃杈惧紡datetime_expr1 鍜�datetime_expr2the 涔嬮棿鐨勬暣鏁板樊銆傚叾缁撴灉鐨勫崟浣嶇敱interval 鍙傛暟缁欏嚭銆�interval 鐨勬硶瀹氬�煎悓TIMESTAMPADD()鍑芥暟璇存槑涓�鎵�鍒楀嚭鐨勭浉鍚屻��

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');

        -> 3

mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');

        -> -1

• TIME_FORMAT(time,format)

鍏朵娇鐢ㄥ拰 DATE_FORMAT()鍑芥暟鐩稿悓, 鐒惰��format 瀛楃�︿覆鍙�鑳戒粎浼氬寘鍚�澶勭悊灏忔椂銆佸垎閽熷拰绉掔殑鏍煎紡璇存槑绗︺�傚叾瀹冭�存槑绗︿骇鐢熶竴涓�NULL鍊兼垨0銆�

鑻�time value鍖呭惈涓�涓�澶т��23鐨勫皬鏃堕儴鍒嗭紝鍒� %H 鍜� %k 灏忔椂鏍煎紡璇存槑绗︿細浜х敓涓�涓�澶т��0..23鐨勯�氬父鑼冨洿鐨勫�笺�傚彟涓�涓�灏忔椂鏍煎紡璇存槑绗︿骇鐢熷皬鏃跺�兼ā鏁�12銆�

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');

        -> '100 100 04 04 4'

• TIME_TO_SEC(time)

杩斿洖宸茶浆鍖栦负绉掔殑time鍙傛暟銆�

mysql> SELECT TIME_TO_SEC('22:23:00');

        -> 80580

mysql> SELECT TIME_TO_SEC('00:39:38');

        -> 2378

• TO_DAYS(date)

缁欏畾涓�涓�鏃ユ��date, 杩斿洖涓�涓�澶╂�� (浠庡勾浠�0寮�濮嬬殑澶╂暟 )銆�

mysql> SELECT TO_DAYS(950501);

        -> 728779

mysql> SELECT TO_DAYS('1997-10-07');

        -> 729669

TO_DAYS() 涓嶇敤浜庨槼鍘嗗嚭鐜�(1582)鍓嶇殑鍊硷紝鍘熷洜鏄�褰撴棩鍘嗘敼鍙樻椂锛岄仐澶辩殑鏃ユ湡涓嶄細琚�鑰冭檻鍦ㄥ唴銆傝�峰弬瑙�12.6鑺傦紝鈥淢ySQL浣跨敤浠�涔堟棩鍘嗭紵鈥�銆�

璇疯�颁綇锛� MySQL浣跨敤11.3鑺傦紝鈥滄棩鏈熷拰鏃堕棿绫诲瀷鈥�涓�鐨勮�勫垯灏嗘棩鏈熶腑鐨勪簩浣嶆暟骞翠唤鍊艰浆鍖栦负鍥涗綅銆備緥濡傦紝  '1997-10-07'鍜� '97-10-07' 琚�瑙嗕负鍚屾牱鐨勬棩鏈�:

mysql> SELECT TO_DAYS('1997-10-07'), TO_DAYS('97-10-07');

        -> 729669, 729669

瀵逛簬1582 骞翠箣鍓嶇殑鏃ユ湡(鎴栬�稿湪鍏跺畠鍦板尯涓轰笅涓�骞� ), 璇ュ嚱鏁扮殑缁撴灉瀹炰笉鍙�闈犵殑銆傝�﹁��12.6鑺傦紝鈥淢ySQL浣跨敤浠�涔堟棩鍘嗭紵鈥� 銆�

• UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

鑻ユ棤鍙傛暟璋冪敤锛屽垯杩斿洖涓�涓�Unix timestamp ('1970-01-01 00:00:00' GMT 涔嬪悗鐨勭�掓��) 浣滀负鏃犵�﹀彿鏁存暟銆傝嫢鐢�date 鏉ヨ皟鐢�UNIX_TIMESTAMP()锛屽畠浼氬皢鍙傛暟鍊间互'1970-01-01 00:00:00' GMT鍚庣殑绉掓暟鐨勫舰寮忚繑鍥炪��date 鍙�浠ユ槸涓�涓�DATE 瀛楃�︿覆銆佷竴涓� DATETIME瀛楃�︿覆銆佷竴涓� TIMESTAMP鎴栦竴涓�褰撳湴鏃堕棿鐨�YYMMDD 鎴�YYYMMDD鏍煎紡鐨勬暟瀛椼��

mysql> SELECT UNIX_TIMESTAMP();

        -> 882226357

mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');

        -> 875996580

褰� UNIX_TIMESTAMP琚�鐢ㄥ�� TIMESTAMP鍒楁椂, 鍑芥暟鐩存帴杩斿洖鍐呴儴鏃舵埑鍊硷紝  鑰屼笉杩涜�屼换浣曢殣鍚�鐨� 鈥�string-to-Unix-timestamp鈥濊浆鍖栥�傚亣濡備綘鍚�UNIX_TIMESTAMP()浼犻�掍竴涓�婧㈠嚭鏃ユ湡锛屽畠浼氳繑鍥� 0,浣嗚�锋敞鎰忓彧鏈夊熀鏈�鑼冨洿妫�鏌ヤ細琚�灞ヨ�� (骞翠唤浠�1970 鍒� 2037锛� 鏈堜唤浠�01鍒�12,鏃ユ湡浠�  01 鍒�31)銆�

鍋囧�備綘鎯宠�佸噺鍘� UNIX_TIMESTAMP() 鍒�, 浣犳垨璁稿笇鏈涘垹鍘诲甫绗﹀彿鏁存暟鐨勭粨鏋溿�傝�峰弬瑙�12.8鑺傦紝鈥淐ast鍑芥暟鍜屾搷浣滅�︹��銆�

• UTC_DATE, UTC_DATE()

杩斿洖褰撳墠 UTC鏃ユ湡鍊硷紝鍏舵牸寮忎负 'YYYY-MM-DD' 鎴� YYYYMMDD锛屽叿浣撴牸寮忓彇鍐充簬鍑芥暟鏄�鍚︾敤鍦ㄥ瓧绗︿覆鎴栨暟瀛楄��澧冧腑銆� 

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;

        -> '2003-08-14', 20030814

• UTC_TIME, UTC_TIME()

杩斿洖褰撳墠 UTC 鍊硷紝鍏舵牸寮忎负  'HH:MM:SS' 鎴�HHMMSS锛屽叿浣撴牸寮忔牴鎹�璇ュ嚱鏁版槸鍚︾敤鍦ㄥ瓧绗︿覆鎴栨暟瀛楄��澧冭�屽畾銆�

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;

        -> '18:07:53', 180753

• UTC_TIMESTAMP, UTC_TIMESTAMP()

杩斿洖褰撳墠UTC鏃ユ湡鍙婃椂闂村�硷紝鏍煎紡涓� 'YYYY-MM-DD HH:MM:SS' 鎴�YYYYMMDDHHMMSS锛屽叿浣撴牸寮忔牴鎹�璇ュ嚱鏁版槸鍚︾敤鍦ㄥ瓧绗︿覆鎴栨暟瀛楄��澧冭�屽畾銆�

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;

        -> '2003-08-14 18:08:04', 20030814180804

• WEEK(date[,mode])

璇ュ嚱鏁拌繑鍥�date 瀵瑰簲鐨勬槦鏈熸暟銆�WEEK() 鐨勫弻鍙傛暟褰㈠紡鍏佽�镐綘鎸囧畾璇ユ槦鏈熸槸鍚﹁捣濮嬩簬鍛ㄦ棩鎴栧懆涓�锛� 浠ュ強杩斿洖鍊肩殑鑼冨洿鏄�鍚︿负浠�0 鍒�53 鎴栦粠1 鍒�53銆傝嫢 mode鍙傛暟琚�鐪佺暐锛屽垯浣跨��default_week_format绯荤粺鑷�鍙橀噺鐨勫�笺�傝�峰弬瑙�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�銆�

浠ヤ笅琛ㄨ�存槑浜�mode 鍙傛暟鐨勫伐浣滆繃绋嬶細d

mysql> SELECT WEEK('1998-02-20');

        -> 7

mysql> SELECT WEEK('1998-02-20',0);

        -> 7

mysql> SELECT WEEK('1998-02-20',1);

        -> 8

mysql> SELECT WEEK('1998-12-31',1);

        -> 53

娉ㄦ剰锛屽亣濡傛湁涓�涓�鏃ユ湡浣嶄簬鍓嶄竴骞寸殑鏈�鍚庝竴鍛�锛� 鑻ヤ綘涓嶄娇鐢�2銆�3銆�6鎴�7浣滀负mode 鍙傛暟閫夋嫨锛屽垯MySQL杩斿洖 0锛�

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);

        -> 2000, 0

鏈変汉鎴栬�镐細鎻愬嚭鎰忚�侊紝璁や负 MySQL 瀵逛簬WEEK() 鍑芥暟搴旇�ヨ繑鍥� 52 锛屽師鍥犳槸缁欏畾鐨勬棩鏈熷疄闄呬笂鍙戠敓鍦�1999骞寸殑绗�52鍛ㄣ�傛垜浠�鍐冲畾杩斿��0浣滀负浠ｆ浛鐨勫師鍥犳槸鎴戜滑甯屾湜璇ュ嚱鏁拌兘杩斿洖鈥滅粰瀹氬勾浠界殑鏄熸湡鏁扳�濄�傝繖浣垮緱WEEK() 鍑芥暟鍦ㄥ悓鍏跺畠浠庢棩鏈熶腑鎶藉彇鏃ユ湡閮ㄥ垎鐨勫嚱鏁扮粨鍚堟椂鐨勪娇鐢ㄦ洿鍔犲彲闈犮��

鍋囧�備綘鏇村笇鏈涙墍璁＄畻鐨勫叧浜庡勾浠界殑缁撴灉鍖呮嫭缁欏畾鏃ユ湡鎵�鍦ㄥ懆鐨勭��涓�澶╋紝鍒欏簲浣跨敤 0銆�2銆�5鎴� 7 浣滀负mode鍙傛暟閫夋嫨銆�

mysql> SELECT WEEK('2000-01-01',2);

        -> 52

浣滀负閫夋嫨锛屽彲浣跨敤 YEARWEEK()鍑芥暟:

mysql> SELECT YEARWEEK('2000-01-01');

        -> 199952

mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);

        -> '52'

• WEEKDAY(date)

杩斿洖date (0 = 鍛ㄤ竴, 1 = 鍛ㄤ簩, ... 6 = 鍛ㄦ棩)瀵瑰簲鐨勫伐浣滄棩绱㈠紩  weekday index for

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');

        -> 1

mysql> SELECT WEEKDAY('1997-11-05');

        -> 2

• WEEKOFYEAR(date)

灏嗚�ユ棩鏈熺殑闃冲巻鍛ㄤ互鏁板瓧褰㈠紡杩斿洖锛岃寖鍥存槸浠�1鍒�53銆傚畠鏄�涓�涓�鍏煎�瑰害鍑芥暟锛岀浉褰撲簬WEEK(date,3)銆�

mysql> SELECT WEEKOFYEAR('1998-02-20');

        -> 8

• YEAR(date)

杩斿洖date 瀵瑰簲鐨勫勾浠�,鑼冨洿鏄�浠�1000鍒�9999銆�

mysql> SELECT YEAR('98-02-03');

        -> 1998

• YEARWEEK(date), YEARWEEK(date,start)

杩斿洖涓�涓�鏃ユ湡瀵瑰簲鐨勫勾鎴栧懆銆�start鍙傛暟鐨勫伐浣滃悓 start鍙傛暟瀵� WEEK()鐨勫伐浣滅浉鍚屻�傜粨鏋滀腑鐨勫勾浠藉彲浠ュ拰璇ュ勾鐨勭��涓�鍛ㄥ拰鏈�鍚庝竴鍛ㄥ�瑰簲鐨勬棩鏈熷弬鏁版湁鎵�涓嶅悓銆�

mysql> SELECT YEARWEEK('1987-01-01');

        -> 198653

娉ㄦ剰锛屽懆鏁板拰WEEK()鍑芥暟闃熷彲閫夊弬鏁�0鎴� 1鍙�鑳戒細杩斿洖鐨�(0) w鏈夋墍涓嶅悓锛屽師鍥犳槸姝ゆ椂 WEEK() 杩斿洖缁欏畾骞翠唤鐨勮��澧冧腑鐨勫懆銆�

MySQL 浣跨敤閫氬父鎵�璇寸殑 proleptic 闃冲巻銆�

姣忎釜灏嗘棩鍘嗙敱鏈卞埄瀹�鏀逛负闃冲巻鐨勫浗瀹跺湪鏀瑰彉鏃ュ巻鏈熼棿閮戒笉寰椾笉鍒犻櫎鑷冲皯10澶┿�� 涓轰簡浜嗚В鍏惰繍浣滐紝璁╂垜浠�鐪嬬��1582骞�10鏈堬紝杩欐槸鐢�鏈卞埄瀹�鏃ュ巻杞�鎹�涓洪槼鍘嗙殑绗�涓�娆�:

鍦�10鏈�4 鏃ュ埌10鏈�15鏃ヤ箣闂寸殑鏃ユ湡涓虹┖鐧姐�傝繖涓�涓�鏂�琚�绉颁负鎺ュ叆銆傛帴鍏ュ墠鐨勬棩鏈熷潎浣跨敤鏈卞埄瀹�鏃ュ巻, 鑰屾帴鍏ュ悗鐨勬棩鏈熷潎浣跨敤闃冲巻銆傛帴鍏ユ湡闂寸殑鏃ユ湡鏄�涓嶅瓨鍦ㄧ殑銆�

褰撲竴涓�鐢ㄤ簬鏃ユ湡鐨勬棩鍘嗗苟涓哄緱鍒板疄闄呬娇鐢ㄦ椂琚�绉颁负 proleptic銆傚洜姝�, 鑻ユ垜浠�鍋囪�句粠鏉ユ病鏈夋帴鍏ユ湡鐨勫瓨鍦�锛岃�岄槼鍘嗗巻娉曞垯濮嬬粓琚�浣跨敤锛屾垜浠�浼氭湁涓�涓�棰勬湡鐨勯槼鍘� 銆傝繖灏辨槸MySQL 鎵�浣跨敤鐨勶紝姝ｅ�傛爣鍑�SQL鎵�瑕佹眰鐨勩�� 閴翠簬杩欎釜鍘熷洜锛屼綔涓�MySQL DATE 鎴� DATETIME鍊艰�岃��鍌ㄥ瓨鐨勬帴鍏ュ墠鐨勬棩鏈熷繀椤昏皟鏁磋繖涓�宸�寮傘�傛垜浠�蹇呴』鏄庣櫧锛屾帴鍏ョ殑鍙戠敓鏃堕棿鍦ㄤ笉鍚岀殑鍥藉�舵湁鎵�涓嶅悓锛岃�屾帴鍏ョ殑鏃堕棿瓒婃櫄锛岄仐澶辩殑鏃ユ湡瓒婂�氥�備緥濡傦紝鍦ㄥぇ涓嶅垪棰�, 鎺ュ叆鍙戠敓鍦� 1752骞�,杩欐椂9鏈�2鏃ワ紝鍛ㄤ笁鍚庣殑绗�浜屽ぉ涓�9鏈�14鏃ワ紝鍛ㄤ簩锛� 淇勭綏鏂�缁撴潫浣跨��鏈卞埄瀹�鏃ュ巻鐨勬椂闂翠负1918骞�,鎺ュ叆杩囩▼涓�閬楀け澶╂暟涓� 13澶�, 鏍规嵁闃冲巻锛屽叾鏅�閬嶈��绉颁负鈥�10鏈堥潻鍛解�濈殑鍙戠敓鏃堕棿瀹為檯涓婃槸11鏈堛��

mysql> CREATE TABLE articles (

    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

    ->   title VARCHAR(200),

    ->   body TEXT,

    ->   FULLTEXT (title,body)

    -> );

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES

    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),

    -> ('How To Use MySQL Well','After you went through a ...'),

    -> ('Optimizing MySQL','In this tutorial we will show ...'),

    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

    -> ('MySQL vs. YourSQL','In the following database comparison ...'),

    -> ('MySQL Security','When configured properly, MySQL ...');

Query OK, 6 rows affected (0.00 sec)

Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles

    -> WHERE MATCH (title,body) AGAINST ('database');

+----+-------------------+------------------------------------------+

| id | title             | body                                     |

+----+-------------------+------------------------------------------+

|  5 | MySQL vs. YourSQL | In the following database comparison ... |

|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |

+----+-------------------+------------------------------------------+

2 rows in set (0.00 sec)

MATCH()鍑芥暟瀵逛簬涓�涓�瀛楃�︿覆鎵ц�岃祫鏂欏簱鍐呯殑鑷�鐒惰��瑷�鎼滅储銆備竴涓�璧勬枡搴撳氨鏄�1濂�1涓�鎴�2涓�鍖呭惈鍦�FULLTEXT鍐呯殑鍒椼�傛悳绱㈠瓧绗︿覆浣滀负瀵�AGAINST()鐨勫弬鏁拌�岃��缁欏畾銆傚�逛簬琛ㄤ腑鐨勬瘡涓�琛�, MATCH() 杩斿洖涓�涓�鐩稿叧鍊硷紝鍗�, 鎼滅储瀛楃�︿覆鍜� MATCH()琛ㄤ腑鎸囧畾鍒椾腑璇ヨ�屾枃瀛椾箣闂寸殑涓�涓�鐩镐技鎬у害閲忋��

鍦ㄩ粯璁ょ姸鎬佷笅, 鎼滅储鐨勬墽琛屾柟寮忎负涓嶅尯鍒嗗ぇ灏忓啓鏂瑰紡銆傜劧鑰�,浣犲彲浠ラ�氳繃瀵圭紪鍏ョ储寮曠殑鍒椾娇鐢ㄤ簩杩涘埗鎺掑簭鏂瑰紡鎵ц�屽尯鍒嗗ぇ灏忓啓鐨勫叏鏂囨悳绱�銆� 渚嬪�傦紝鍙�浠ュ悜涓�涓�浣跨��latin1瀛楃�﹂泦鐨勫垪缁欏��latin1_bin 鐨勬帓搴忔柟寮忥紝瀵逛簬鍏ㄦ枃鎼滅储鍖哄垎澶у皬鍐欍��

濡備笂杩版墍涓句緥瀛愶紝褰�MATCH()琚�鐢ㄥ湪涓�涓� WHERE 璇�鍙ヤ腑鏃讹紝鐩稿叧鍊兼槸闈炶礋娴�鐐规暟銆傞浂鐩稿叧鐨勬剰鎬濇槸娌℃湁鐩镐技鎬с�傜浉鍏虫�х殑璁＄畻鏄�鍩轰簬璇ヨ�屼腑鍗曡瘝鐨勬暟鐩�, 璇ヨ�屼腑鐙�鐗瑰瓙鐨勬暟鐩�锛岃祫鏂欏簱涓�鍗曡瘝鐨勬�绘暟锛屼互鍙婂寘鍚�鐗规畩璇嶇殑鏂囦��(琛�)鏁扮洰銆�

瀵逛簬鑷�鐒惰��瑷�鍏ㄦ枃鎼滅储锛岃�佹��MATCH() 鍑芥暟涓�鍛藉悕鐨勫垪鍜屼綘鐨勮〃涓�涓�浜�FULLTEXT绱㈠紩涓�鍖呭惈鐨勫垪鐩稿悓銆傚�逛簬鍓嶈堪闂�璁�锛� 娉ㄦ剰锛�MATCH()鍑芥暟(棰樼洰鍙婂叏鏂�)涓�鎵�鍛藉悕鐨勫垪鍜屾枃绔犺〃鐨�FULLTEXT绱㈠紩涓�鐨勫垪鐩稿悓銆傝嫢瑕佸垎鍒�鎼滅储棰樼洰鍜屽叏鏂囷紝搴旇�ュ�规瘡涓�鍒楀垱寤�FULLTEXT绱㈠紩銆�

鎴栬�呬篃鍙�浠ヨ繍琛屽竷灏旀悳绱㈡垨浣跨敤鏌ヨ�㈡墿灞曡繘琛屾悳绱��傚叧浜庤繖浜涙悳绱㈢被鍨嬬殑璇存槑瑙�12.7.1鑺傦紝鈥滃竷灏斿叏鏂囨悳绱⑩��鍜�12.7.2鑺傦紝鈥滃叏鏂囨悳绱㈠甫鏌ヨ�㈡墿灞曗��銆�

涓婇潰鐨勪緥瀛愬熀鏈�涓婂睍绀轰簡鎬庢牱浣跨敤杩斿洖琛岀殑鐩稿叧鎬ч『搴忔笎寮辩殑MATCH()鍑芥暟銆傝�屼笅闈㈢殑渚嬪瓙鍒欏睍绀轰簡鎬庢牱鏄庣‘鍦版��绱㈢浉鍏冲�笺�傝繑鍥炶�岀殑椤哄簭鏄�涓嶅畾鐨勶紝鍘熷洜鏄�  SELECT 璇�鍙ヤ笉鍖呭�� WHERE鎴�ORDER BY 瀛愬彞锛�

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')

    -> FROM articles;

+----+-----------------------------------------+

| id | MATCH (title,body) AGAINST ('Tutorial') |

+----+-----------------------------------------+

|  1 |                        0.65545833110809 |

|  2 |                                       0 |

|  3 |                        0.66266459226608 |

|  4 |                                       0 |

|  5 |                                       0 |

|  6 |                                       0 |

+----+-----------------------------------------+

6 rows in set (0.00 sec)

涓嬮潰鐨勪緥瀛愬垯鏇村姞澶嶆潅銆傝�㈤棶杩斿洖鐩稿叧鍊硷紝鍚屾椂瀵硅�屾寜鐓х浉鍏虫�ф笎寮辩殑椤哄簭杩涜�屾帓搴忋�備负瀹炵幇杩欎釜缁撴灉锛屼綘搴旇�ヤ袱娆℃寚瀹� MATCH()锛� 涓�娆″湪 SELECT 鍒楄〃涓�鑰屽彟涓�娆″湪 WHERE瀛愬彞涓�銆傝繖涓嶄細寮曡捣棰濆�栫殑鍐呭姟鎿嶄綔锛屽師鍥犳��MySQL 浼樺寲绋嬪簭娉ㄦ剰鍒颁袱涓�MATCH()璋冪敤鏄�鐩稿悓鐨勶紝浠庤�屽彧浼氭縺娲讳竴娆″叏鏂囨悳绱�浠ｇ爜銆� 

mysql> SELECT id, body, MATCH (title,body) AGAINST

    -> ('Security implications of running MySQL as root') AS score

    -> FROM articles WHERE MATCH (title,body) AGAINST

    -> ('Security implications of running MySQL as root');

+----+-------------------------------------+-----------------+

| id | body                                | score           |

+----+-------------------------------------+-----------------+

|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |

|  6 | When configured properly, MySQL ... | 1.3114095926285 |

+----+-------------------------------------+-----------------+

2 rows in set (0.00 sec)

琛ㄤ腑鏈�2琛�(0.00 绉�)

MySQL FULLTEXT 鎵ц�屽皢浠讳綍鍗曞瓧瀛楃�﹀師褰� (瀛楁瘝銆佹暟瀛楀拰涓嬪垝绾块儴鍒�)鐨勫簭鍒楄�嗕负涓�涓�鍗曡瘝銆傝繖涓�搴忓垪鎴栬�镐篃鍖呭惈鍗曞紩鍙� ('),浣嗗湪涓�琛屼腑涓嶄細瓒呰繃涓�涓�銆� 杩欐剰鍛崇潃 aaa'bbb 浼氳��瑙嗕负涓�涓�鍗曡瘝锛岃�� aaa''bbb鍒欒��瑙嗕负2涓�鍗曡瘝銆備綅浜庡崟璇嶄箣鍓嶆垨鍏跺悗鐨勫崟寮曞彿浼氳��FULLTEXT鍒嗘瀽绋嬪簭鍘绘帀锛� 'aaa'bbb' 浼氬彉鎴�   aaa'bbb銆�

FULLTEXT鍒嗘瀽绋嬪簭浼氶�氳繃瀵绘壘鏌愪簺鍒嗛殧绗︽潵纭�瀹氬崟璇嶇殑璧峰�嬩綅缃�鍜岀粨鏉熶綅缃�锛屼緥濡�' ' (闂撮殧绗﹀彿)銆� , (閫楀彿)浠ュ強 . (鍙ュ彿 )銆傚亣濡傚崟璇嶆病鏈夎��鍒嗛殧绗﹀垎寮�锛�(渚嬪�傚湪涓�鏂囬噷 ), 鍒� FULLTEXT 鍒嗘瀽绋嬪簭涓嶈兘纭�瀹氫竴涓�璇嶇殑璧峰�嬩綅缃�鍜岀粨鏉熶綅缃�銆備负浜嗚兘澶熷湪杩欐牱鐨勮��瑷�涓�鍚�FULLTEXT 绱㈠紩娣诲姞鍗曡瘝鎴栧叾瀹冪紪鍏ョ储寮曠殑鏈�璇�锛屼綘蹇呴』瀵瑰畠浠�杩涜�岄�勫�勭悊锛屼娇鍏惰��涓�浜涜�稿��"涔嬬被鐨勪换鎰忓垎闅旂�﹀垎闅斿紑銆�

涓�浜涜瘝鍦ㄥ叏鏂囨悳绱�涓�浼氳��蹇界暐锛� 

• 浠讳綍杩囦簬鐭�鐨勮瘝閮戒細琚�蹇界暐銆� 鍏ㄦ枃鎼滅储鎵�鑳芥壘鍒扮殑璇嶇殑榛樿�ゆ渶灏忛暱搴︿负 4涓�瀛楃�︺��
• 鍋滄�㈠瓧涓�鐨勮瘝浼氳��蹇界暐銆傜�佺敤璇嶅氨鏄�涓�涓�鍍忊��the鈥� 鎴栤��some鈥� 杩欐牱杩囦簬骞冲父鑰岃��璁や负鏄�涓嶅叿璇�涔夌殑璇嶃�傚瓨鍦ㄤ竴涓�鍐呯疆鐨勫仠姝㈠瓧锛� 浣嗗畠鍙�浠ラ�氳繃鐢ㄦ埛鑷�瀹氫箟鍒楄〃琚�鏀瑰啓銆傝�峰弬瑙�12.7.5鑺傦紝鈥滃井璋僊ySQL鍏ㄦ枃鎼滅储鈥�銆�

榛樿�ょ殑鍋滄�㈠瓧鍦�12.7.3鑺傦紝鈥滃叏鏂囧仠姝㈠瓧鈥�涓�琚�缁欏嚭銆傞粯璁ょ殑鏈�灏忓崟璇嶉暱搴﹀拰 鍋滄�㈠瓧鍙�浠ヨ��鏀瑰彉锛屽��12.7.5鑺傦紝鈥滃井璋僊ySQL鍏ㄦ枃鎼滅储鈥�涓�鎵�杩般��

璇嶅簱鍜岃�㈤棶涓�姣忎竴涓�姝ｇ‘鐨勫崟璇嶆牴鎹�鍏跺湪璇嶅簱鍜岃�㈤棶涓�鐨勯噸瑕佹�ц�岃��琛￠噺銆�  閫氳繃杩欑�嶆柟寮忥紝涓�涓�鍑虹幇鍦ㄨ�稿�氭枃浠朵腑鐨勫崟璇嶅叿鏈夎緝浣庣殑閲嶈�佹��(鑰屼笖鐢氳嚦寰堝�氬崟璇嶇殑閲嶈�佹�т负闆�),鍘熷洜鏄�鍦ㄨ繖涓�鐗瑰埆璇嶅簱涓�鍏惰��涔変环鍊艰緝浣庛�傚弽涔嬶紝鍋囧�傝繖涓�鍗曡瘝姣旇緝灏戣�侊紝閭ｄ箞瀹冧細寰楀埌涓�涓�杈冮珮鐨勯噸瑕佹�с�傜劧鍚庡崟璇嶇殑閲嶈�佹�ц��缁勫悎锛屼粠鑰岀敤鏉ヨ�＄畻璇ヨ�岀殑鐩稿叧鎬с�� 

杩欓」鎶�鏈�鏈�閫傚悎鍚屽ぇ鍨嬭瘝搴撲竴璧蜂娇鐢� (浜嬪疄涓�, 姝ゆ椂瀹冪粡杩囦粩缁嗙殑璋冩暣 )銆傚�逛簬寰堝皬鐨勮〃锛屽崟璇嶅垎甯冨苟涓嶈兘鍏呭垎鍙嶆槧瀹冧滑鐨勮��涔変环鍊硷紝 鑰岃繖涓�妯″紡鏈夋椂鍙�鑳戒細浜х敓濂囩壒鐨勭粨鏋溿�備緥濡�, 铏界劧鍗曡瘝 鈥�MySQL鈥� 鍑虹幇鍦ㄦ枃绔犺〃涓�鐨勬瘡涓�琛岋紝浣嗗�硅繖涓�璇嶇殑鎼滅储鍙�鑳藉緱涓嶅埌浠讳綍缁撴灉锛�

mysql> SELECT * FROM articles

    -> WHERE MATCH (title,body) AGAINST ('MySQL');

鎵句笉鍒版悳绱㈢殑璇�(0.00 绉�)

杩欎釜鎼滅储鐨勭粨鏋滀负绌猴紝鍘熷洜鏄�鍗曡�� 鈥�MySQL鈥� 鍑虹幇鍦ㄨ嚦灏戝叏鏂囩殑50%鐨勮�屼腑銆� 鍥犳��, 瀹冭��鍒楀叆鍋滄�㈠瓧銆傚�逛簬澶у瀷鏁版嵁闆嗭紝浣跨敤杩欎釜鎿嶄綔鏈�鍚堥�備笉杩囦簡----涓�涓�鑷�鐒惰��瑷�闂�璇�涓嶄細浠庝竴涓�1GB 鐨勮〃姣忛殧涓�琛岃繑鍥炰竴娆°�傚�逛簬灏忓瀷鏁版嵁闆嗭紝瀹冪殑鐢ㄥ�勫彲鑳芥瘮杈冨皬銆�

涓�涓�绗﹀悎琛ㄤ腑鎵�鏈夎�岀殑鍐呭�圭殑涓�鍗婄殑鍗曡瘝鏌ユ壘鐩稿叧鏂囨。鐨勫彲鑳芥�ц緝灏忋�備簨瀹炰笂, 瀹冩洿瀹规槗鎵惧埌寰堝�氫笉鐩稿叧鐨勫唴瀹广�傛垜浠�閮界煡閬擄紝褰撴垜浠�鍦ㄥ洜鐗圭綉涓婅瘯鍥句娇鐢ㄦ悳绱㈠紩鎿庡�绘壘璧勬枡鐨勬椂鍊欙紝杩欑�嶆儏鍐靛彂鐢熺殑棰戠巼棰囬珮銆傚彲浠ユ帹璁猴紝鍖呭惈璇ュ崟璇嶇殑琛屽洜鍏舵墍鍦ㄧ壒鍒�鏁版嵁闆� 鑰岃��璧嬩簣杈冧綆鐨勮��涔変环鍊笺�� 涓�涓�缁欏畾鐨勮瘝鏈夊彲鑳藉湪涓�涓�鏁版嵁闆嗕腑鎷ユ湁瓒呰繃鍏�50%鐨勫煙鍊硷紝鑰屽湪鍙︿竴涓�鏁版嵁闆嗗嵈涓嶇劧銆� 

褰撲綘绗�涓�娆″皾璇曚娇鐢ㄥ叏鏂囨悳绱�浠ヤ簡瑙ｅ叾宸ヤ綔杩囩▼鏃讹紝杩欎��50% 鐨勫煙鍊兼彁渚涢噸瑕佺殑钑存兜鎿嶄綔锛氳嫢浣犲垱寤轰簡涓�涓�琛�锛屽苟涓斿彧灏嗘枃绔犵殑1銆�2琛屾彃鍏ュ叾涓�锛� 鑰屾枃涓�鐨勬瘡涓�鍗曡瘝鍦ㄦ墍鏈夎�屼腑鍑虹幇鐨勬満鐜囪嚦灏戜��  50% 銆傞偅涔堢粨鏋滄槸浣犱粈涔堜篃涓嶄細鎼滅储鍒般�備竴瀹氳�佹彃鍏ヨ嚦灏�3琛岋紝骞朵笖澶氬�氱泭鍠勩�傞渶瑕佺粫杩囪��50% 闄愬埗鐨勭敤鎴峰彲浣跨敤甯冨皵鎼滅储浠ｇ爜锛涜��12.7.1鑺傦紝鈥滃竷灏斿叏鏂囨悳绱⑩��銆�

mysql> SELECT * FROM articles WHERE MATCH (title,body)

    -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

+----+-----------------------+-------------------------------------+

| id | title                 | body                                |

+----+-----------------------+-------------------------------------+

|  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |

|  2 | How To Use MySQL Well | After you went through a ...        |

|  3 | Optimizing MySQL      | In this tutorial we will show ...   |

|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |

|  6 | MySQL Security        | When configured properly, MySQL ... |

+----+-----------------------+-------------------------------------+

mysql> SELECT * FROM articles

    -> WHERE MATCH (title,body) AGAINST ('database');

+----+-------------------+------------------------------------------+

| id | title             | body                                     |

+----+-------------------+------------------------------------------+

|  5 | MySQL vs. YourSQL | In the following database comparison ... |

|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |

+----+-------------------+------------------------------------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM articles

    -> WHERE MATCH (title,body)

    -> AGAINST ('database' WITH QUERY EXPANSION);

+----+-------------------+------------------------------------------+

| id | title             | body                                     |

+----+-------------------+------------------------------------------+

|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |

|  5 | MySQL vs. YourSQL | In the following database comparison ... |

|  3 | Optimizing MySQL  | In this tutorial we will show ...        |

+----+-------------------+------------------------------------------+

3 rows in set (0.00 sec)