{"id":1272,"date":"2022-08-07T18:04:39","date_gmt":"2022-08-07T10:04:39","guid":{"rendered":"http:\/\/175.24.184.226\/?p=1272"},"modified":"2022-08-07T18:04:39","modified_gmt":"2022-08-07T10:04:39","slug":"mysql%e4%b8%addatetime%e7%b1%bb%e5%9e%8b%e5%90%8e%e8%be%b9%e8%b7%9f%e7%9a%84%e6%95%b0%e5%ad%97","status":"publish","type":"post","link":"\/?p=1272","title":{"rendered":"MySQL\u4e2ddatetime\u7c7b\u578b\u540e\u8fb9\u8ddf\u7684\u6570\u5b57"},"content":{"rendered":"<h2>\u95ee\u9898\u63cf\u8ff0<\/h2>\n<p>\u6570\u636e\u5e93\u4e2d\u65b0\u5efa\u8868\u7684\u65f6\u5019\uff0c\u9700\u8981\u7528\u5230datetime\u7c7b\u578b\uff0c\u8d77\u521dsql\u8bed\u53e5\u90fd\u662f\u5f00\u53d1\u4eba\u5458\u8bbe\u8ba1\u597d\u8868\u683c\u4e4b\u540e\uff0c\u63d0\u4ea4\u4e13\u95e8\u7684\u6570\u636e\u5e93\u4eba\u5458\u6765\u8fdb\u884c\u683c\u5f0f\u5316\uff0c\u5305\u62ec\u589e\u52a0\u7d22\u5f15\u7b49\u3002\u76f4\u5230\u6709\u4e00\u5929\uff0c\u6570\u636e\u5e93\u4eba\u5458\u4e0d\u8d1f\u8d23\u683c\u5f0f\u5316\u4e86\uff0c\u5ffd\u7136\u53d1\u73b0datetime\u540e\u8fb9\u4e5f\u8ddf\u7684\u6709\u4e00\u4e2a\u540e\u7f00\uff1a\uff083\uff09\uff0c\u53bb\u7f51\u4e0a\u641c\u7d22\u4e86\u4e0b\u4e00\u65f6\u7adf\u4e5f\u627e\u4e0d\u5230\u4ee4\u4eba\u6ee1\u610f\u7684\u89e3\u91ca\u3002\u5982\u6b64\u8fc7\u53bb\u4e86\u534a\u5e74\uff0c\u6709\u4e00\u5929\u9886\u5bfc\u95ee\u5230\u8fd9\u4e2a\u540e\u7f00\u662f\u5e72\u561b\u7684\uff0c\u652f\u652f\u543e\u543e\u96be\u4ee5\u63cf\u8ff0\uff0c\u4e8e\u662f\u518d\u6765\u7814\u7a76\u4e0bdatetime\u3002<\/p>\n<h2>\u95ee\u9898\u89e3\u91ca<\/h2>\n<p>\u82e6\u82e6\u5bfb\u89c5\uff0c\u7ec8\u4e8e\u627e\u5230\u4e00\u6bb5\u82f1\u6587\uff0c\u6709\u4e86\u4e00\u4e2a\u8be6\u7ec6\u7684\u89e3\u91ca\uff0c\u5927\u6982\u610f\u601d\u5c31\u662f\u8bf4\u8fd9\u4e2a\u540e\u7f00\u662f\u7528\u6765\u5b9a\u4e49\u6beb\u79d2\u7684\u7cbe\u5ea6\u7684\u3002  <\/p>\n<pre><code>A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is \u2018YYYY-MM-DD HH:MM:SS[.fraction]\u2019, the range for DATETIME values is \u20181000-01-01 00:00:00.000000\u2019 to \u20189999-12-31 23:59:59.999999\u2019, and the range for TIMESTAMP values is \u20181970-01-01 00:00:01.000000\u2019 to \u20182038-01-19 03:14:07.999999\u2019. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 11.3.6, \u201cFractional Seconds in Time Values\u201d<\/code><\/pre>\n<p>\u5185\u5fc3\u6fc0\u52a8\u5440\uff0c\u5fc5\u987b\u7801\u51e0\u884c\uff01<\/p>\n<p>\u6570\u636e\u5e93\u5b9e\u8df5<\/p>\n<h3>1.\u5efa\u8868<\/h3>\n<pre><code>CREATE TABLE test_datetime_suffix\n(\n   ID BIGINT NOT NULL,\n   TIME1 DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT &#039;\u65f6\u95f41&#039;,\n   TIME2 DATETIME (3) NOT NULL DEFAULT CURRENT_TIMESTAMP (3) ON UPDATE CURRENT_TIMESTAMP (3) COMMENT &#039;\u65f6\u95f42&#039;,\n   TIME3 DATETIME (6) NOT NULL DEFAULT CURRENT_TIMESTAMP (6) ON UPDATE CURRENT_TIMESTAMP (6)COMMENT &#039;\u65f6\u95f43&#039;,\n   TIME4 DATETIME (8) NOT NULL DEFAULT CURRENT_TIMESTAMP (8) ON UPDATE CURRENT_TIMESTAMP (8) COMMENT &#039;\u65f6\u95f44&#039;,\n   PRIMARY KEY (ID)\n)COMMENT &#039;\u6d4b\u8bd5datetime\u7c7b\u578b\u540e\u7f00\u6570\u5b57\u8868&#039;;\n<\/code><\/pre>\n<p>\u4ece\u56fe\u4e2d\u53ef\u4ee5\u770b\u5230\uff0c\u540e\u7f00\u6700\u5927\u7cbe\u5ea6\u662f6\uff0c\u8d85\u8fc7\u5c31\u4f1a\u62a5\u9519\u3002\u628aTIME4\u8fd9\u4e2a\u5b57\u6bb5\u5220\u4e86\uff0c\u5c31\u53ef\u4ee5\u6210\u529f\u5efa\u8868\u4e86\u3002<br \/>\n<img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/1729990-20210716161014207-1986248445.png\" alt=\"\" \/><\/p>\n<h3>2.\u63d2\u5165\u51e0\u6761\u8bb0\u5f55<\/h3>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/1729990-20210716161048225-1375659670.png\" alt=\"\" \/><br \/>\n\u4ece\u56fe\u4e2d\u53ef\u4ee5\u770b\u5230\uff0cTIME1\u6ca1\u6709\u6beb\u79d2\u503c\uff0cTIME2\u6beb\u79d2\u503c\u67093\u4f4d\uff0cTIEM3\u67096\u4f4d\uff0c\u6070\u597d\u5bf9\u5e94\u5efa\u8868\u65f6datetime\u540e\u7f00\u4e2d\u7684\u6570\u5b57\u3002<\/p>\n<h2>\u540e\u8bb0<\/h2>\n<p>\u539f\u6765\u5efa\u8868\u65f6\u6e05\u4e00\u8272\u7684\uff1adatetime (3)\uff0c\u5bfc\u81f4\u6570\u636e\u5e93\u4e2d\u5b58\u50a8\u7684\u65f6\u95f4\u67093\u4f4d\u6beb\u79d2\u503c\uff0c\u5c55\u793a\u5230\u9875\u9762\u4e0a\u65f6\u5c31\u4f1a\u88ab\u6d4b\u8bd5\u4eba\u5458\u603c\u56de\u6765\u8981\u4f18\u5316\uff0c\u90fd\u4f1a\u8c03\u7528MYSQL\u81ea\u5e26\u7684DATEFORMAT\u51fd\u6570\u8fdb\u884c\u683c\u5f0f\u5316\uff0c\u73b0\u5728\u624d\u53d1\u73b0\u539f\u6765\u662f\u8fd9\u4e2a\u6beb\u79d2\u503c\u5728\u641e\u9b3c\uff0c\u5982\u679c\u4e0d\u662f\u5bf9\u65f6\u95f4\u7cbe\u5ea6\u8981\u6c42\u7279\u522b\u9ad8\uff0c\u8fd8\u662f\u4f7f\u7528datetime\u4e0d\u52a0\u4efb\u4f55\u540e\u7f00\u6765\u5f97\u66f4\u723d\u4e9b\u3002<\/p>\n<p>\u89e3\u51b3\u65b9\u5f0f\uff1a<br \/>\n\u53ef\u4ee5\u7528navcat\u94fe\u63a5\u6570\u636e\u5e93\uff0c\u7136\u540e\u627e\u5230\u5bf9\u5e94\u8868\uff0c\u7136\u540e\u53f3\u952e\u70b9\u51fb\u8bbe\u8ba1\u8868\uff0c\u5982\u4e0b\u56fe<br \/>\n<img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/1729990-20210716161439499-331358945.png\" alt=\"\" \/><br \/>\n\u5c06datatime\u540e\u7684\u957f\u5ea6\u4fee\u6539\u4e3a0\uff0c\u7136\u540e\u4fdd\u5b58\u8bbe\u7f6e\u5373\u53ef<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u95ee\u9898\u63cf\u8ff0 \u6570\u636e\u5e93\u4e2d\u65b0\u5efa\u8868\u7684\u65f6\u5019\uff0c\u9700\u8981\u7528\u5230datetime\u7c7b\u578b\uff0c\u8d77\u521dsql\u8bed\u53e5\u90fd\u662f\u5f00\u53d1\u4eba\u5458\u8bbe\u8ba1\u597d\u8868\u683c\u4e4b\u540e\uff0c\u63d0\u4ea4\u4e13\u95e8\u7684\u6570\u636e\u5e93\u4eba\u5458\u6765\u8fdb\u884c\u683c\u5f0f\u5316   \u2026 &#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0},"categories":[23],"tags":[],"_links":{"self":[{"href":"\/index.php?rest_route=\/wp\/v2\/posts\/1272"}],"collection":[{"href":"\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1272"}],"version-history":[{"count":1,"href":"\/index.php?rest_route=\/wp\/v2\/posts\/1272\/revisions"}],"predecessor-version":[{"id":1273,"href":"\/index.php?rest_route=\/wp\/v2\/posts\/1272\/revisions\/1273"}],"wp:attachment":[{"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1272"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1272"},{"taxonomy":"post_tag","embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1272"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}