{"id":1486,"date":"2014-10-16T15:25:08","date_gmt":"2014-10-16T15:25:08","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=1486"},"modified":"2014-10-16T15:25:08","modified_gmt":"2014-10-16T15:25:08","slug":"mysql%e7%9a%84%e4%b8%80%e4%ba%9btips%e6%80%bb%e7%bb%93","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/1486.html","title":{"rendered":"MySQL\u7684\u4e00\u4e9btips\u603b\u7ed3"},"content":{"rendered":"<p>\u603b\u7ed3\u4e00\u4e0b\u6700\u8fd1\u5b66\u5230\u7684MySQL\u7684tips\uff0c\u505a\u4e2a\u5907\u5fd8\uff1a<\/p>\n<h6><strong>INSERT IGNORE <\/strong><strong>\u4e0e INSERT INTO\u7684\u533a\u522b<\/strong><\/h6>\n<p>INSERT IGNORE \u4e0e INSERT INTO\u7684\u533a\u522b\u5c31\u662f\uff1a<strong>INSERT IGNORE<\/strong><strong>\u4f1a\u5ffd\u7565\u6570\u636e\u5e93\u4e2d\u5df2\u7ecf\u5b58\u5728<\/strong> <strong>\u7684\u6570\u636e\uff0c\u5982\u679c\u6570\u636e\u5e93\u6ca1\u6709\u6570\u636e\uff0c\u5c31\u63d2\u5165\u65b0\u7684\u6570\u636e\uff0c\u5982\u679c\u6709\u6570\u636e\u7684\u8bdd\u5c31\u8df3\u8fc7\u8fd9\u6761\u6570\u636e\u3002<\/strong>INSERT INTO\u6709\u65e0\u6570\u636e\u90fd\u63d2\u5165\uff0c\u5982\u679c\u4e3b\u952e\u5219\u4e0d\u63d2\u5165(\u800c\u4e14\u62a5\u9519)<\/p>\n<p>insert\u8bed\u53e5\u4e00\u6b21\u53ef\u4ee5\u63d2\u5165\u591a\u7ec4\u503c\uff0c\u6bcf\u7ec4\u503c\u7528\u4e00\u5bf9\u5706\u62ec\u53f7\u62ec\u8d77\u6765\uff0c\u7528\u9017\u53f7\u5206\u9694\uff0c\u5982\u4e0b\uff1a<\/p>\n<pre class=\"lang:default decode:true\">insert into `news`(title, body, time) values('helloworld', 'body 1', now()), ('title 2', 'body 2', now());<\/pre>\n<p>\u4e0b\u9762\u901a\u8fc7\u4ee3\u7801\u8bf4\u660e\u4e4b\u95f4\u7684\u533a\u522b\uff0c\u5982\u4e0b\uff1a<\/p>\n<pre class=\"lang:default decode:true \">create table testtb(\nid int not null primary key,\nname varchar(50),\nage int\n);\n\ninsert into testtb(id, name, age) values(1, \"helloworld\", 13);\u00a0 \/\/\u62a5\u9519\u201cERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'\u201d\nselect * from testtb;\n\ninsert into testtb(id, name, age) values(1, \"helloworld\", 13);\nselect * from testtb;\n\ninsert ignore into testtb(id, name, age) values(1, \"aa\", 13);\nselect * from testtb;\u00a0\u00a0 \/\/\u4ecd\u662f 1 helloworld 13\uff0c\u56e0\u4e3aid\u662f\u4e3b\u952e\uff0c\u51fa\u73b0\u4e3b\u952e\u91cd\u590d\u4f46\u4f7f\u7528\u4e86ignore\u5219\u9519\u8bef\u88ab\u5ffd\u7565\n\nreplace into testtb(id, name, age) values(1, \"aa\", 12);\nselect * from testtb;\u00a0\u00a0 \/\/\u6570\u636e\u53d8\u4e3a1 aa 12<\/pre>\n<hr \/>\n<h6><strong>MySQL<\/strong><strong>\u591a\u8868\u67e5\u8be2<\/strong><\/h6>\n<pre class=\"lang:default decode:true\">SELECT * FROM table1,table2 WHERE table1.id=table2.id;\n\nSELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;\n\nSELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;\n\n\u7b1b\u5361\u5c14\u79ef(\u4ea4\u53c9\u8fde\u63a5) \u5728MySQL\u4e2d\u53ef\u4ee5\u4e3aCROSS JOIN\u6216\u8005\u7701\u7565CROSS\u5373JOIN\uff0c\u6216\u8005\u4f7f\u7528','  \u5982\uff1a\nSELECT * FROM table1 CROSS JOIN table2\nSELECT * FROM table1 JOIN table2\nSELECT * FROM table1,table2\n\u7531\u4e8e\u5176\u8fd4\u56de\u7684\u7ed3\u679c\u4e3a\u88ab\u8fde\u63a5\u7684\u4e24\u4e2a\u6570\u636e\u8868\u7684\u4e58\u79ef\uff0c\u56e0\u6b64\u5f53\u6709WHERE, ON\u6216USING\u6761\u4ef6\u7684\u65f6\u5019\u4e00\u822c\u4e0d\u5efa\u8bae\u4f7f\u7528\uff0c\u56e0\u4e3a\u5f53\u6570\u636e\u8868\u9879\u76ee\u592a\u591a\u7684\u65f6\u5019\uff0c\u4f1a\u975e\u5e38\u6162\u3002\u4e00\u822c\u4f7f\u7528LEFT [OUTER] JOIN\u6216\u8005RIGHT [OUTER] JOIN<\/pre>\n<ul>\n<li><a href=\"http:\/\/blog.csdn.net\/hguisu\/article\/details\/5731880\" target=\"_blank\">Mysql \u591a\u8868\u8054\u5408\u67e5\u8be2\u6548\u7387\u5206\u6790\u53ca\u4f18\u5316<\/a><\/li>\n<li><a href=\"http:\/\/blog.sina.com.cn\/s\/blog_6ad62438010168lg.html\" target=\"_blank\">MySQL\u591a\u8868\u67e5\u8be2<\/a><\/li>\n<\/ul>\n<hr \/>\n<h6><strong>IP<\/strong><strong>\u5730\u5740\u5728<\/strong><strong>MySQL<\/strong><strong>\u6570\u636e\u5e93\u4e2d\u7684\u5b58\u50a8<\/strong><\/h6>\n<pre class=\"lang:default decode:true\">echo ip2long('192.168.1.38');\n\nC:&gt;php -r \"echo ip2long('219.143.8.242');\"\n-611383054\n\nC:&gt;php -r \"echo ip2long('192.168.1.38');\"\n-1062731482\n\nC:&gt;php -r \"echo ip2long('127.0.0.1');\"\n2130706433\n==========\n\u572832\u4f4d\u7684\u673a\u5b50\u4e0a\uff0cecho ip2long('192.168.1.38');\u7531\u4e8e\u8d85\u8fc732\u4f4d\u7684\u6700\u5927\u6570\uff0c\u5bfc\u81f4\u8f93\u51fa\u8d1f\u6570-1062731482\u3002\n\n\u6709\u4e24\u79cd\u65b9\u6cd5\u66f4\u65b0\u4e3a\u6b63\u6570\uff1a\n$ip_long = bindec(decbin(ip2long($ip))); #\u9a8c\u8bc1OK\uff01\n\u6216\n$ip_long = = sprintf(\"%u\", ip2long($ip));<\/pre>\n<pre class=\"lang:default decode:true\">mysql&gt; select inet_aton('219.143.8.242');\n+----------------------------+\n| inet_aton('219.143.8.242') |\n+----------------------------+\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3683584242 |\n+----------------------------+\n1 row in set (0.00 sec)\n\nmysql&gt; select inet_ntoa('3683584242');\n+-------------------------+\n| inet_ntoa('3683584242') |\n+-------------------------+\n| 219.143.8.242\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\n+-------------------------+\n1 row in set (0.00 sec)\n\nmysql&gt; select inet_ntoa(3683584242);\n+-----------------------+\n| inet_ntoa(3683584242) |\n+-----------------------+\n| 219.143.8.242\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\n+-----------------------+\n1 row in set (0.00 sec)\n\nmysql&gt; select inet_aton('192.168.1.38');\n+---------------------------+\n| inet_aton('192.168.1.38') |\n+---------------------------+\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3232235814 |\n+---------------------------+\n1 row in set (0.00 sec)\n\nselect inet_aton('219.143.8.242');\nselect inet_ntoa('3683584242');\nselect inet_ntoa(3683584242);<\/pre>\n<p>\u4e24\u4e2a\u51fd\u6570\u90fd\u662f\u6839\u636e\uff1aA*256*256*256+B*256*256+C*256+D\u7684\u7b97\u6cd5<br \/>\n192*256*256*256+168*256*256+1*256+38 = 3 232 235 814<\/p>\n<p>\u53cd\u8fc7\u6765\uff0c\u4eceint\u8f6c\u6362\u4e3aIP\u5730\u5740\u5206\u522b\u662fphp\u7684long2ip()\u548cmysql\u7684INET_NTOA()\u3002<br \/>\nmysql\u5b58\u50a8\u8fd9\u4e2a\u503c\u662f\u5b57\u6bb5\u9700\u8981\u7528int UNSIGNED\u3002\u4e0d\u7528UNSIGNED\u7684\u8bdd\uff0c128\u4ee5\u4e0a\u7684IP\u6bb5\u5c31\u5b58\u50a8\u4e0d\u4e86\u4e86\u3002<br \/>\n\u4f20\u7edf\u7684\u65b9\u6cd5\uff0c\u521b\u5efavarchar(15)\uff0c\u9700\u8981\u5360\u752815\u4e2a\u5b57\u8282\uff0c\u800c\u6539\u65f6\u4f7f\u7528int\u53ea\u9700\u89814\u5b57\u8282\uff0c\u53ef\u4ee5\u7701\u4e00\u4e9b\u5b57\u8282\u3002<\/p>\n<p>PHP\u5b58\u5165\u65f6\uff1a$ip = ip2long($ip);<br \/>\nMySQL\u53d6\u51fa\u65f6\uff1aSELECT INET_NTOA(ip) FROM table &#8230;<br \/>\nPHP\u53d6\u51fa\u65f6\uff0c\u591a\u4e00\u6b65\uff1a$ip = long2ip($ip);<\/p>\n<p><strong>\u8f6c\u6362\u4ee5\u524d\u7684\u6570\u636e\uff1a<\/strong><\/p>\n<p>1.\u628a\u4ee5\u524d\u7684varchar()\u6570\u636e\u8f6c\u6362\u4e3aint\u578b\u7684SQL\u8bed\u53e5\uff1a<br \/>\nUPDATE `hx_table` SET ip = INET_ATON(ip) WHERE INET_ATON(ip) is NOT NULL<\/p>\n<p>2.\u628a\u5b57\u6bb5\u66f4\u6539\u4e3aint\u578b\uff1a<br \/>\nALTER TABLE `hx_table` CHANGE `ip` `ip` INT UNSIGNED NOT NULL<\/p>\n<p>\u7a0b\u5e8f\u505a\u76f8\u5e94\u4fee\u6539\u4e0a\u4f20\uff0c\u5b8c\u6210\u3002<\/p>\n<p>\u56e0\u6b64\u4e00\u79cd\u662f\u4fee\u6539PHP\u7a0b\u5e8f\uff0c\u4f7f\u5176\u80af\u5b9a\u5b58\u5165\u6b63\u6570\u3002<br \/>\n\u53e6\u4e00\u79cd\u662f\u5c06mysql\u7684\u8fd9\u4e2a\u5b57\u6bb5\u4f7f\u7528int\uff0c\u975eUNSIGNED\uff0c\u4f7f\u5176\u53ef\u4ee5\u5b58\u5165\u8d1f\u6570\u3002<\/p>\n<p><strong>\u53c2\u8003\u94fe\u63a5\uff1a<\/strong><\/p>\n<ul>\n<li><a href=\"http:\/\/www.9enjoy.com\/mysql-ip2long\/\" target=\"_blank\">IP\u5730\u5740\u5728mysql\u7684\u5b58\u50a8\uff08IP\u5730\u5740\u548cint\u7684\u8f6c\u6362\uff09<\/a><\/li>\n<li><a href=\"http:\/\/m114.org\/mysql-how-to-store-the-ip-address\/\" target=\"_blank\">MySQL\u600e\u6837\u5b58\u50a8IP\u5730\u5740<\/a><\/li>\n<li><a href=\"http:\/\/chenhaibo0806999.iteye.com\/blog\/1447824\" target=\"_blank\">mysql \u5e38\u7528\u547d\u4ee4\u4e4b\u51fd\u6570<\/a><\/li>\n<li><a href=\"http:\/\/www.ntu.edu.sg\/home\/ehchua\/programming\/sql\/SampleDatabases.html\" target=\"_blank\">MySQL Sample Databases<\/a><\/li>\n<\/ul>\n<hr \/>\n<p>\u5f85\u7eed\u2026\u2026<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u603b\u7ed3\u4e00\u4e0b\u6700\u8fd1\u5b66\u5230\u7684MySQL\u7684tips\uff0c\u505a\u4e2a\u5907\u5fd8\uff1a INSERT IGNORE \u4e0e INSERT INTO\u7684\u533a [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[16],"class_list":["post-1486","post","type-post","status-publish","format-standard","hentry","category-database","tag-mysql"],"views":4799,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/1486","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=1486"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/1486\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=1486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=1486"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=1486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}