{"id":985,"date":"2017-10-18T16:19:14","date_gmt":"2017-10-18T08:19:14","guid":{"rendered":"http:\/\/www.5x44.cn\/?p=985"},"modified":"2017-10-19T14:09:08","modified_gmt":"2017-10-19T06:09:08","slug":"mysql5-7%e6%97%a5%e6%9c%9f%e5%92%8c%e6%97%b6%e9%97%b4%e7%b1%bb%e5%9e%8b%e6%80%bb%e8%a7%88","status":"publish","type":"post","link":"https:\/\/www.5x44.cn\/?p=985","title":{"rendered":"MYSQL5.7\u65e5\u671f\u548c\u65f6\u95f4\u7c7b\u578b\u603b\u89c8"},"content":{"rendered":"<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/date-and-time-type-overview.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/date-and-time-type-overview.html<\/a><\/p>\n<p>\u5bf9\u4e8eDATE\u548cDATETIME\u8303\u56f4\u7684\u63cf\u8ff0\uff0c\u201cSUPPORTED\u201d\u4e00\u8bcd\u4ec5\u4ec5\u4ee3\u8868\u53ef\u4ee5\u5de5\u4f5c\uff0c\u5e76\u4e0d\u505a\u5176\u5b83\u4fdd\u8bc1\u3002<\/p>\n<p>MYSQL\u5141\u8bb8\u5728TIME, DATETIME\u548cTIMESTAMP\u4e0a\u4f7f\u7528\u79d2\u7247\u6bb5\uff08fractional seconds\uff09\u503c\uff0c\u7cbe\u5ea6\u53ef\u4ee5\u5230\u5fae\u79d2\uff086\u4f4d\u6709\u6548\u6570\u5b57\uff09\u3002\u5b9a\u4e49\u4e00\u4e2a\u5305\u542b\u79d2\u7247\u6bb5\u7684\u5b57\u6bb5\uff0c\u4f7f\u7528\u7684\u8bed\u6cd5\u662ftype_name(fsp)\uff0c\u8fd9\u91cctype_name\u662fTIME,DATETIME, TIMESTAMP\uff0cfsp\u6307\u5b9a\u4e86\u79d2\u7247\u6bb5\u7684\u7cbe\u5ea6\u3002\u4f8b\u5982\uff1a<\/p>\n<pre>CREATE TABLE t1 (t TIME(3), dt DATETIME(6));\r\n<\/pre>\n<p>\u5982\u679c\u6307\u5b9a\u4e86fsp\u503c\uff0c\u53d6\u503c\u8303\u56f4\u5fc5\u987b\u662f0~6\u3002\u5982\u679c\u6307\u5b9a\u7684\u662f0\uff0c\u90a3\u4e48\u6ca1\u6709\u79d2\u7247\u6bb5\u3002\u5982\u679c\u5ffd\u7565\u8fd9\u4e2a\u8bbe\u7f6e\uff0c\u9ed8\u8ba4\u503c\u662f0\u3002\uff08\u8fd9\u4e0d\u540c\u4e8eSQL\u6807\u51c6\uff0cSQL\u6807\u51c6\u662f6\uff0c\u8fd9\u4e48\u505a\u7684\u539f\u56e0\u662f\u4e3a\u4e86\u517c\u5bb9\u4e4b\u524dMYSQL\u7248\u672c\uff09<\/p>\n<p>\u4efb\u4f55TIMESTAMP\u6216DATETIME\u5217\uff0c\u90fd\u6709\u81ea\u52a8\u521d\u59cb\u5316\u548c\u66f4\u65b0\u7684\u5c5e\u6027\u3002<\/p>\n<ul>\n<li>DATE\uff1a\u65e5\u671f\u7c7b\u578b\u3002\u652f\u6301\u7684\u8303\u56f4\u662f &#8216;1000-01-01&#8217; to &#8216;9999-12-31&#8217;\u3002MYSQL\u663e\u793aDATE\u503c\u7684\u683c\u5f0f\u662f&#8217;YYYY-MM-DD&#8217;\uff0c\u4f46\u662f\u4e5f\u5141\u8bb8\u5206\u914d\u5b57\u7b26\u4e32\u6216\u6570\u5b57\u5230DATE\u5b57\u6bb5\u5217\u4e0a\u3002<\/li>\n<li>DATETIME[(FSP)]\uff1a\u65e5\u671f\u65f6\u95f4\u7684\u7ec4\u5408\u3002\u652f\u6301\u7684\u8303\u56f4\u662f&#8217;1000-01-01 00:00:00.000000&#8242; to &#8216;9999-12-31 23:59:59.999999&#8217;\u3002MYSQL\u663e\u793a\u7684DATETIM\u503c\u7684\u683c\u5f0f\u662f &#8216;YYYY-MM-DD HH:MM:SS[.fraction]&#8217;\uff0c\u4f46\u4e5f\u5141\u8bb8\u5206\u914d\u5b57\u7b26\u4e32\u6216\u6570\u5b57\u5230\u8be5\u7c7b\u578b\u4e0a\u3002\u53ef\u9009\u7684FSP\u503c\u7684\u8303\u56f4\u662f0~6\uff0c\u7528\u6765\u6307\u5b9a\u79d2\u7247\u6bb5\u7684\u7cbe\u5ea6\u3002\u9ed8\u8ba4\u503c\u662f0\uff0c\u6307\u5b9a\u4e860\u4ee3\u8868\u6ca1\u6709\u79d2\u7247\u6bb5\u3002\u81ea\u52a8\u521d\u59cb\u5316\u548c\u66f4\u65b0\u5230\u5f53\u524d\u65e5\u671f\u548c\u65f6\u95f4\u7684\u5c5e\u6027\u53ef\u4ee5\u901a\u8fc7\u4f7f\u7528DEFAULT\u548cON UPDATE\u5b50\u53e5\u5b9a\u4e49\u3002as described in <a class=\"xref\" title=\"11.3.5\u00a0Automatic Initialization and Updating for TIMESTAMP and DATETIME\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/timestamp-initialization.html\">Section\u00a011.3.5, \u201cAutomatic Initialization and Updating for TIMESTAMP and DATETIME\u201d<\/a>.<\/li>\n<li>TIMESTAMP[(FSP)]\uff1a\u65f6\u95f4\u6233\u7c7b\u578b\u3002\u8303\u56f4\u662f <code class=\"literal\">'1970-01-01<br \/>\n00:00:01.000000'<\/code> UTC\u5230 <code class=\"literal\">'2038-01-19<br \/>\n03:14:07.999999'<\/code>\u00a0UTC\u3002TIMESTAMP\u7684\u503c\u662f\u4ece<code class=\"literal\">'1970-01-01 00:00:00'<\/code> UTC\u5f00\u59cb\u4ee5\u79d2\u6765\u5b58\u50a8\u3002TIMESTAMP\u4e0d\u80fd\u7528\u6765\u63cf\u8ff0 <code class=\"literal\">'1970-01-01 00:00:00'<\/code>\u8fd9\u4e2a\u65f6\u95f4\uff0c\u56e0\u4e3a\u5b83\u7684\u503c\u662f0\u3002\u5e76\u4e140\u503c\u662f\u88abTIMESTAMPE\u9884\u7559\u7684\u201c\u96f6\u201d\u503c\u2014\u2014<code class=\"literal\">'0000-00-00<br \/>\n00:00:00'<\/code>\u3002\u53ef\u9009\u7684FSP\u503c\u7684\u8303\u56f4\u662f0~6\uff0c\u7528\u6765\u6307\u5b9a\u79d2\u7247\u6bb5\u7684\u7cbe\u5ea6\u3002\u9ed8\u8ba4\u503c\u662f0\uff0c\u6307\u5b9a\u4e860\u4ee3\u8868\u6ca1\u6709\u79d2\u7247\u6bb5\u3002\u670d\u52a1\u5668\u5904\u7406TIMESTAMP\u5b9a\u4e49\u4f9d\u8d56\u4e8e <a class=\"link\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/server-system-variables.html#sysvar_explicit_defaults_for_timestamp\"><code class=\"literal\">explicit_defaults_for_timestamp<\/code><\/a>\u7cfb\u7edf\u53d8\u91cf\u3002\u5982\u679c <a class=\"link\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/server-system-variables.html#sysvar_explicit_defaults_for_timestamp\"><code class=\"literal\">explicit_defaults_for_timestamp<\/code><\/a>\u5f00\u542f\uff0c\u5c31\u4e0d\u4f1a\u5141\u8bb8\u4f7f\u7528 <code class=\"literal\">DEFAULT CURRENT_TIMESTAMP<\/code> \u6216<code class=\"literal\">ON<br \/>\nUPDATE CURRENT_TIMESTAMP<\/code>\u5c5e\u6027\u3002\u5b83\u4eec\u7684\u503c\u5fc5\u987b\u88ab\u663e\u793a\u6307\u5b9a\u5728\u5217\u7684\u5b9a\u4e49\u4e2d\u3002\u540c\u65f6TIMESTAMP\u5217\u4e5f\u4e0d\u80fd\u88ab\u5b9a\u4e49\u4e3aNOT NULL\u800c\u5141\u8bb8NULL\u503c\u3002\u5982\u679c <a class=\"link\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/server-system-variables.html#sysvar_explicit_defaults_for_timestamp\"><code class=\"literal\">explicit_defaults_for_timestamp<\/code><\/a>\u5173\u95ed\uff0c\u670d\u52a1\u5668\u5904\u7406TIMESTAMP\u7684\u65b9\u5f0f\u5982\u4e0b\uff1a<\/p>\n<pre>\u9664\u975e\u6307\u5b9a\u4e86\u5176\u5b83\u5185\u5bb9\uff0c\u8868\u4e2d\u7684\u7b2c\u4e00\u4e2aTIMESTAMP\u5217\u5982\u679c\u6ca1\u6709\u88ab\u663e\u793a\u7684\u5206\u914d\u4e00\u4e2a\u503c\u5219\u81ea\u52a8\u66f4\u65b0\u6210\u6700\u8fd1\u4fee\u6539\u7684\u65e5\u671f\u548c\u65f6\u95f4\u3002\u8fd9\u4f7f\u7528TIMESTAMPE\u5217\u5728\u63d2\u5165\u548c\u66f4\u65b0\u65f6\u53d8\u7684\u975e\u5e38\u6709\u7528\u3002\u4f60\u540c\u6837\u4e5f\u53ef\u4ee5\u901a\u8fc7\u5206\u914dNULL\u503c\u6765\u8bbe\u7f6eTIMESTAMP\u5217\u4e3a\u5f53\u524d\u7684\u65e5\u671f\u548c\u65f6\u95f4\uff0c\u9664\u975e\u5b83\u88ab\u5b9a\u4e49\u4e3a\u5141\u8bb8\u8bbe\u7f6eNULL\u503c\u3002\r\n\u81ea\u52a8\u521d\u59cb\u5316\u548c\u66f4\u65b0\u5f53\u524d\u7684\u65e5\u671f\u548c\u65f6\u95f4\uff0c\u53ef\u4ee5\u901a\u8fc7\u4f7f\u7528DEFAULT CURRENT_TIMESTAMP\u548cON UPDATE CURRENT_TIMESTAMP\u7684\u5b50\u53e5\u6765\u5b9e\u73b0\u3002\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u7b2c\u4e00\u4e2aTIMESTAMP\u5217\u5df2\u7ecf\u6709\u8fd9\u4e9b\u5c5e\u6027\u3002\u4f46\u662f\u4e5f\u53ef\u4ee5\u901a\u8fc7\u663e\u793a\u8bbe\u7f6e\u8fd9\u4e2a\u5c5e\u6027\u3002<\/pre>\n<\/li>\n<li>TIME[(FSP)]\uff1a\u65f6\u95f4\u7c7b\u578b\u3002\u5b83\u7684\u8303\u56f4\u662f <code class=\"literal\">'-838:59:59.000000'<\/code> to <code class=\"literal\">'838:59:59.000000'<\/code>\u3002MYSQL\u663e\u793aTIME\u503c\u7684\u683c\u5f0f\u662f<code class=\"literal\">'HH:MM:SS[.fraction]'<\/code>\uff0c\u4f46\u662f\u4e5f\u5141\u8bb8\u5206\u914d\u5b57\u7b26\u4e32\u6216\u6570\u5b57\u5230\u8be5\u5217\u4e0a\u3002\u53ef\u9009\u7684FSP\u503c\u7684\u8303\u56f4\u662f0~6\uff0c\u7528\u6765\u6307\u5b9a\u79d2\u7247\u6bb5\u7684\u7cbe\u5ea6\u3002\u9ed8\u8ba4\u503c\u662f0\uff0c\u6307\u5b9a\u4e860\u4ee3\u8868\u6ca1\u6709\u79d2\u7247\u6bb5\u3002<\/li>\n<li>YEAR[(4)]\uff1a4\u4f4d\u5341\u8fdb\u5236\u6570\u7ec4\u6210\u7684\u683c\u5f0f\u3002MYSQL\u663e\u793aYEAR\u7684\u683c\u5f0f\u662fYYYY\uff0c\u4f46\u662f\u540c\u6837\u5141\u8bb8\u4f7f\u7528\u5b57\u7b26\u4e32\u6216\u6570\u5b57\u5206\u914d\u503c\u3002\u503c\u663e\u793a\u4ece1901\u52302155\u548c0000\u3002<\/li>\n<\/ul>\n<p>SUM()\u548cAVG()\u8fd9\u4e24\u4e2a\u805a\u5408\u51fd\u6570\u4e0d\u80fd\u7528\u4e8e\u65f6\u95f4\u503c\u3002\uff08\u5b83\u4eec\u628a\u7b2c\u4e00\u4e2a\u975e\u6570\u5b57\u5b57\u7b26\u540e\u9762\u7684\u4e00\u5207\u8f6c\u6362\u6210\u6570\u503c\u578b\u3002\uff09\u4e3a\u4e86\u7ed5\u5f00\u8fd9\u4e2a\u95ee\u9898\uff0c\u8981\u5148\u8f6c\u6362\u6570\u503c\u7684\u5355\u5143\uff0c\u7136\u540e\u6267\u884c\u805a\u5408\uff0c\u7136\u540e\u518d\u8f6c\u6362\u56de\u65f6\u95f4\u503c\uff08temporal value\uff09\u3002\u4f8b\u5982\uff1a<\/p>\n<pre class=\"programlisting line-numbers  language-sql\"><code class=\" language-sql\"><span class=\"token keyword\">SELECT<\/span> <span class=\"token function\">SEC_TO_TIME<\/span><span class=\"token punctuation\">(<\/span><span class=\"token function\">SUM<\/span><span class=\"token punctuation\">(<\/span><span class=\"token function\">TIME_TO_SEC<\/span><span class=\"token punctuation\">(<\/span><em class=\"replaceable\">time_col<\/em><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">FROM<\/span> <em class=\"replaceable\">tbl_name<\/em><span class=\"token punctuation\">;<\/span>\r\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token function\">FROM_DAYS<\/span><span class=\"token punctuation\">(<\/span><span class=\"token function\">SUM<\/span><span class=\"token punctuation\">(<\/span><span class=\"token function\">TO_DAYS<\/span><span class=\"token punctuation\">(<\/span><em class=\"replaceable\">date_col<\/em><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">FROM<\/span> <em class=\"replaceable\">tbl_name<\/em><span class=\"token punctuation\">;<\/span><\/code><\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/date-and-time-type-overview.html \u5bf9\u4e8eDATE\u548c&#8230;<\/p>\n<p class=\"read-more\"><a class=\"btn btn-default\" href=\"https:\/\/www.5x44.cn\/?p=985\"> Read More<span class=\"screen-reader-text\">  Read More<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37],"tags":[],"class_list":["post-985","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/posts\/985","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=985"}],"version-history":[{"count":5,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/posts\/985\/revisions"}],"predecessor-version":[{"id":990,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/posts\/985\/revisions\/990"}],"wp:attachment":[{"href":"https:\/\/www.5x44.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=985"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=985"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=985"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}