{"id":513,"date":"2024-12-31T15:01:41","date_gmt":"2024-12-31T07:01:41","guid":{"rendered":"https:\/\/znl.pub\/?p=513"},"modified":"2024-12-31T15:04:44","modified_gmt":"2024-12-31T07:04:44","slug":"mysql%e5%9c%a8-varchar-%e7%b1%bb%e5%9e%8b%e7%9a%84%e5%ad%97%e6%ae%b5%e4%b8%ad%e6%9f%a5%e6%89%be%e5%8c%85%e5%90%ab%e7%89%b9%e5%ae%9a-json-%e9%94%ae%e5%80%bc%e5%af%b9%e7%9a%84%e8%ae%b0%e5%bd%95","status":"publish","type":"post","link":"https:\/\/znl.pub\/?p=513","title":{"rendered":"MySQL\u5728 VARCHAR \u7c7b\u578b\u7684\u5b57\u6bb5\u4e2d\u67e5\u627e\u5305\u542b\u7279\u5b9a JSON \u952e\u503c\u5bf9\u7684\u8bb0\u5f55"},"content":{"rendered":"\n<p>\u4f7f\u7528MySQL \u7684 JSON \u51fd\u6570\u3002\u867d\u7136\u8be5\u5b57\u6bb5\u4e0d\u662f\u6b63\u5f0f\u7684 JSON \u7c7b\u578b\uff0c\u4f46\u53ea\u8981\u5b58\u50a8\u5728\u5176\u4e2d\u7684\u6570\u636e\u662f\u6709\u6548\u7684 JSON \u683c\u5f0f\u5b57\u7b26\u4e32\uff0c\u4f60\u5c31\u53ef\u4ee5\u4f7f\u7528\u8fd9\u4e9b\u51fd\u6570\u6765\u89e3\u6790\u548c\u67e5\u8be2\u6570\u636e\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\u4f8b\uff1a<\/h2>\n\n\n\n<p>\u8868\u540d\u4e3a my_table\uff0c\u5b57\u6bb5\u6709 type\uff0cname\uff0cinfo\uff0ccontent\uff0c\u5176\u4e2dinfo\u5b57\u6bb5\u5b58\u50a8\u7684\u662fjson\u5b57\u7b26\u4e32\uff0c\u4f46\u662f\u8868\u7ed3\u6784\u8fd9\u4e2a\u5b57\u6bb5\u4e3avarchar(700)\u3002\u5982\u56fe\uff1a<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"356\" height=\"126\" src=\"https:\/\/znl.pub\/wp-content\/uploads\/2024\/12\/\u56fe\u7247-20.png\" alt=\"\" class=\"wp-image-514\" srcset=\"https:\/\/znl.pub\/wp-content\/uploads\/2024\/12\/\u56fe\u7247-20.png 356w, https:\/\/znl.pub\/wp-content\/uploads\/2024\/12\/\u56fe\u7247-20-300x106.png 300w\" sizes=\"auto, (max-width: 356px) 100vw, 356px\" \/><\/figure>\n\n\n\n<p>\u5b58\u50a8\u7684\u6570\u636e\u6709\u4e2a\u7279\u70b9\u5c31\u662f\uff08\u4e0d\u540ctype\u7c7b\u578b\uff09\u6bcf\u884cinfo\u7684json\u5b57\u6bb5\u90fd\u4e0d\u4e00\u6837\u3002\u73b0\u5728\u9700\u8981\u67e5\u8be2 info.dsn_type=&#8221;native&#8221; \u7684\u8bb0\u5f55\u7684name\u503c\u3002<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"722\" height=\"139\" src=\"https:\/\/znl.pub\/wp-content\/uploads\/2024\/12\/\u56fe\u7247-21.png\" alt=\"\" class=\"wp-image-515\" srcset=\"https:\/\/znl.pub\/wp-content\/uploads\/2024\/12\/\u56fe\u7247-21.png 722w, https:\/\/znl.pub\/wp-content\/uploads\/2024\/12\/\u56fe\u7247-21-300x58.png 300w\" sizes=\"auto, (max-width: 722px) 100vw, 722px\" \/><\/figure>\n\n\n\n<p>\u76f4\u63a5\u5148\u4e0a\u67e5\u8be2\u8bed\u53e5\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name FROM my_table WHERE \nJSON_VALID(info) \nAND JSON_CONTAINS_PATH(info, 'one', '$.dsn_type')\nAND info-&gt;&gt;'$.dsn_type' = 'native' \nLIMIT 0,1;<\/code><\/pre>\n\n\n\n<p>&#8211; JSON_VALID(info) \u786e\u4fdd\u53ea\u6709\u6709\u6548\u7684 JSON \u5b57\u7b26\u4e32\u88ab\u5904\u7406<br> &#8211; JSON_CONTAINS_PATH(info, &#8216;one&#8217;, &#8216;$.dsn_type&#8217;) \u786e\u4fdd\u53ea\u6709\u6709.dsn_type\u5b57\u6bb5\u624d\u88ab\u63d0\u53d6\u5904\u7406<br> &#8211; info->>&#8217;$.dsn_type&#8217; \u5c31\u662f\u7b49\u540c\u4e8e JSON_UNQUOTE(JSON_EXTRACT(info,&#8217;$.dsn_type&#8217;))<br> &#8211; $.dsn_type \u662f JSON \u8def\u5f84\u8868\u8fbe\u5f0f<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4f7f\u7528MySQL \u7684 JSON \u51fd\u6570\u3002\u867d\u7136\u8be5\u5b57\u6bb5\u4e0d\u662f\u6b63\u5f0f\u7684 JSON \u7c7b\u578b\uff0c\u4f46\u53ea\u8981\u5b58\u50a8\u5728\u5176\u4e2d\u7684\u6570\u636e\u662f\u6709\u6548\u7684 JS&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[],"class_list":["post-513","post","type-post","status-publish","format-standard","hentry","category-28"],"_links":{"self":[{"href":"https:\/\/znl.pub\/index.php?rest_route=\/wp\/v2\/posts\/513","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/znl.pub\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/znl.pub\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/znl.pub\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/znl.pub\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=513"}],"version-history":[{"count":5,"href":"https:\/\/znl.pub\/index.php?rest_route=\/wp\/v2\/posts\/513\/revisions"}],"predecessor-version":[{"id":520,"href":"https:\/\/znl.pub\/index.php?rest_route=\/wp\/v2\/posts\/513\/revisions\/520"}],"wp:attachment":[{"href":"https:\/\/znl.pub\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/znl.pub\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=513"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/znl.pub\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}