{"id":2589,"date":"2015-12-20T22:18:49","date_gmt":"2015-12-20T14:18:49","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2589"},"modified":"2015-12-20T22:18:49","modified_gmt":"2015-12-20T14:18:49","slug":"mysql%e6%89%b9%e9%87%8f%e6%8f%92%e5%85%a5%e6%95%b0%e6%8d%ae","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2589.html","title":{"rendered":"MySQL[\u6279\u91cf]\u63d2\u5165\u6570\u636e"},"content":{"rendered":"<p>=Start=<\/p>\n<h6>\u641c\u7d22\u5173\u952e\u5b57\uff1a<\/h6>\n<ul>\n<li>mysql insert many data<\/li>\n<li>mysql \u6279\u91cf \u63d2\u5165 \u6570\u636e<\/li>\n<li>mysql insert data speed<\/li>\n<li>mysql insert ignore<\/li>\n<\/ul>\n<h4>\u53c2\u8003\u89e3\u7b54\uff1a<\/h4>\n<h5>\u5148\u8bf4\u89e3\u51b3\u65b9\u6848\uff1a<\/h5>\n<pre class=\"lang:default decode:true\">INSERT INTO tbl_name\n    (a,b,c)\nVALUES\n    (1,2,3),\n    (4,5,6),\n    (7,8,9);\n\nINSERT IGNORE INTO tbl_name\n    (a,b,c)\nVALUES\n    (1,2,3),\n    (4,5,6),\n    (7,8,9);\n\nload data infile '\/path\/to\/file.csv'\n    into table tbl_name\n    fields terminated by ',' optionally enclosed by '\"'\n    lines terminated by '\\n'\n    ignore 1 lines\n    (field1, field2, field3,..., fieldN);\n\nload data local infile '\/path\/to\/file.csv'\n    into table tbl_name\n    fields terminated by ',' optionally enclosed by '\"'\n    lines terminated by '\\n'\n    ignore 1 lines\n    (field1, field2, field3,..., fieldN);<\/pre>\n<h5>\u518d\u8bf4\u5728\u5b9e\u9645\u8fc7\u7a0b\u4e2d\u78b0\u5230\u7684\u95ee\u9898\uff1a<\/h5>\n<h6><span style=\"color: #0000ff;\">0.\u5f71\u54cdMySQL\u63d2\u5165\u8bed\u53e5\u7684\u901f\u5ea6\u7684\u56e0\u7d20<\/span>\u300c<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/insert-speed.html\" target=\"_blank\">Speed of INSERT Statements<\/a>\u300d<\/h6>\n<p><span style=\"color: #ff0000;\">\u4e3a\u4e86\u4f18\u5316\u63d2\u5165\u901f\u5ea6\uff0c\u53ef\u4ee5\u5c06\u591a\u4e2a\u5c0f\u7684\u63d2\u5165\u64cd\u4f5c\u5408\u5e76\u6210\u4e00\u4e2a\u5927\u7684\u63d2\u5165\u64cd\u4f5c\u6765\u8fbe\u5230\u76ee\u7684\u3002<\/span>\u7406\u60f3\u60c5\u51b5\u662f\uff1a\u5728\u4e00\u4e2a\u8fde\u63a5\u5efa\u7acb\u540e\uff0c\u4e00\u6b21\u6027\u53d1\u9001\u6240\u6709\u7684\u6570\u636e\u3001\u5ef6\u8fdf\u6240\u6709\u7684\u7d22\u5f15\u66f4\u65b0\u64cd\u4f5c\u76f4\u5230\u6279\u91cf\u63d2\u5165\u5b8c\u6210\u3002<\/p>\n<p>\u4e0b\u9762\u662fMySQL\u4e2d\u63d2\u5165\u4e00\u884c\u6570\u636e\u7684\u5404\u9636\u6bb5\u64cd\u4f5c\u7684\u6240\u9700\u65f6\u95f4\u5360\u6bd4\u60c5\u51b5\uff1a<\/p>\n<ul>\n<li>Connecting\u2014\u2014\u5efa\u7acb\u8fde\u63a5: (3)<\/li>\n<li>Sending query to server\u2014\u2014\u5411\u670d\u52a1\u5668\u53d1\u9001\u8bf7\u6c42: (2)<\/li>\n<li>Parsing query\u2014\u2014\u670d\u52a1\u5668\u89e3\u6790\u8bf7\u6c42: (2)<\/li>\n<li>Inserting row\u2014\u2014\u63d2\u5165\u6570\u636e: (1 \u00d7 size of row)<\/li>\n<li>Inserting indexes\u2014\u2014\u63d2\u5165\u7d22\u5f15: (1 \u00d7 number of indexes)<\/li>\n<li>Closing\u2014\u2014\u5173\u95ed\u8fde\u63a5: (1)<\/li>\n<\/ul>\n<p>\u8bf4\u660e\uff1a\u4ee5\u4e0a\u5185\u5bb9\u5e76\u6ca1\u6709\u628a\u6253\u5f00\u8868\u64cd\u4f5c\u7684\u65f6\u95f4\u7b97\u8fdb\u53bb\uff0c\u56e0\u4e3a\u90a3\u5728\u6bcf\u4e2a\u5e76\u53d1\u6267\u884c\u7684\u67e5\u8be2\u64cd\u4f5c\u4e2d\u53ea\u4f1a\u53d1\u751f\u4e00\u6b21\u3002\u5f53\u4f7f\u7528B-tree\u7ed3\u6784\u4f5c\u7d22\u5f15\u65f6\uff0c\u63d2\u5165\u7d22\u5f15\u64cd\u4f5c\u7684\u901f\u5ea6\u4e0e\u8868\u7684\u5927\u5c0f\u6210\u53cd\u6bd4\uff08\u6bd4\u503c\u7ea6\u4e3alog N\uff09\u3002<\/p>\n<h6>1.insert vs. bulk insert<\/h6>\n<p>\u5e38\u89c4\u7684\u6bcf\u4e2ainsert\u8bed\u53e5\u63d2\u5165\u4e00\u6761\u8bb0\u5f55\u6548\u7387\u592a\u4f4e\uff0c\u82e5\u5c06\u591a\u4e2a\u8bb0\u5f55\u653e\u5728\u4e00\u4e2ainsert\u8bed\u53e5\u4e2d\u8fdb\u884c\u63d2\u5165\uff0c\u6548\u7387\u4f1a\u6709\u8f83\u5927\u63d0\u9ad8\uff08\u53c2\u8003\u4e0a\u9762\u7684\u8bf4\u660e\uff09\uff1b\u5f53\u8bb0\u5f55\u53ef\u80fd\u5b58\u5728\u91cd\u590d\u65f6\u4f7f\u7528ignore\u5173\u952e\u5b57\u8fdb\u884c\u5ffd\u7565\uff08\u662f\u5426\u4f7f\u7528\u9700\u8981\u89c6\u5177\u4f53\u60c5\u51b5\u800c\u5b9a\uff09\uff1b<\/p>\n<ul>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/insert.html\" target=\"_blank\">http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/insert.html<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/1793169\/which-is-faster-multiple-single-inserts-or-one-multiple-row-insert\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/1793169\/which-is-faster-multiple-single-inserts-or-one-multiple-row-insert<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/9819271\/why-is-mysql-innodb-insert-so-slow\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/9819271\/why-is-mysql-innodb-insert-so-slow<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/548541\/insert-ignore-vs-insert-on-duplicate-key-update\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/548541\/insert-ignore-vs-insert-on-duplicate-key-update<\/a><\/li>\n<\/ul>\n<h6>2.bulk_insert_buffer_size<\/h6>\n<p>\u5728\u4e00\u4e2ainsert\u8bed\u53e5\u4e2d\u8fdb\u884c\u5927\u91cf\u8bb0\u5f55\u7684\u63d2\u5165\u65f6\u53ef\u80fd\u9700\u8981\u5173\u6ce8\u4e00\u4e0b\u300c<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/server-system-variables.html#sysvar_bulk_insert_buffer_size\" target=\"_blank\">bulk_insert_buffer_size<\/a>\u300d\u53d8\u91cf\u7684\u5927\u5c0f\uff1b<\/p>\n<h6>3.load data<\/h6>\n<p><span style=\"color: #ff0000;\"><strong>\u4e2a\u4eba\u611f\u89c9\u7528\u300cload data\u300d\u7684\u65b9\u5f0f\u901f\u5ea6\u662f\u6700\u5feb\u7684\uff0c\u4f46\u662f\u5751\u4e5f\u6bd4\u8f83\u591a<\/strong><\/span>\uff08\u9664\u975e\u7528\u4e8e\u52a0\u8f7d\u7684\u6587\u4ef6\u5185\u5bb9\u683c\u5f0f\u5168\u90fd\u51c6\u786e\u65e0\u8bef\uff0c\u4e00\u822c\u60c5\u51b5\u4e0b\u8fd9\u4e2a\u6bd4\u8f83\u96be\u4ee5\u4fdd\u8bc1\uff0c\u6bd4\u5982\u6587\u4ef6\u4e2d\u591a\u4e86\u4e2a\u7a7a\u884c\u4ec0\u4e48\u7684\uff09\uff1b<\/p>\n<h6>4.\u7528\u300cload data\u300d\u7684\u65b9\u5f0f\u65f6\u6700\u5e38\u78b0\u5230\u7684\u6743\u9650\u95ee\u9898<\/h6>\n<blockquote>\n<p style=\"padding-left: 30px;\">ERROR 1045 (28000): Access denied for user &#8216;xxx&#8217;@&#8217;xxx&#8217; (using password: YES)<\/p>\n<\/blockquote>\n<p>\u539f\u56e0\uff1a\u8fd9\u4e2a\u4e00\u822c\u662f\u56e0\u4e3a\u975eroot\u7528\u6237\u6ca1\u6709FILE Privilege\uff0c\u53ef\u4ee5\u901a\u8fc7show grants\u67e5\u770b\u5f53\u524d\u767b\u9646\u7528\u6237\u7684\u6743\u9650\uff0c\u4e5f\u53ef\u4ee5\u901a\u8fc7select mysql.user\u67e5\u770b\u67d0\u7528\u6237\u7684\u6743\u9650\uff0c\u4e00\u822c\u60c5\u51b5\u4e0b\uff0cnormal user\u662f\u65e0FILE\u6743\u9650\u7684\u3002<\/p>\n<p>\u4e09\u79cd\u89e3\u51b3\u529e\u6cd5\uff1a<\/p>\n<p>1\uff09\u7ed9\u300cload data\u300d\u547d\u4ee4\u52a0local\u53c2\u6570\uff0c\u7528\u300cload data local infile &#8216;filename&#8217; into table xxx\u300d\u6765\u5bfc\u6570\u636e\uff08\u63a8\u8350\uff09<\/p>\n<p>2\uff09\u7ed9normal user\u5f00\u901aFILE Privilege\uff0c<span style=\"color: #ff0000;\"><strong>\u6ce8\u610f<\/strong>\uff1aFILE\u6743\u9650\u4e0eSELECE\/DELETE\/UPDATE\u7b49\u4e0d\u540c\uff0c\u540e\u8005\u662f\u53ef\u4ee5\u5177\u4f53\u6307\u5b9a\u5230\u67d0\u4e2adb\u7684\u67d0\u4e2a\u8868\u7684\uff0c\u800cFILE\u5219\u662f\u5168\u5c40\u7684<\/span>\uff0c\u5373\u53ea\u80fd\u901a\u8fc7\uff1a<\/p>\n<pre class=\"lang:default decode:true\">GRANT FILE ON *.* TO user@xxx;\n\/* \u6216 *\/\nUPDATE mysql.user SET File_priv = 'Y' WHERE Host = 'xxx' AND User = 'user';\n\nFLUSH PRIVILEGES;<\/pre>\n<p>\u624d\u80fd\u4f7fFILE\u6743\u9650\u5bf9\u6240\u6709db\u7684\u6240\u6709tables\u751f\u6548\u3002\u901a\u8fc7\uff1a<\/p>\n<pre class=\"lang:default decode:true\">GRANT ALL ON dbName.* TO 'someuser'@'somehost';<\/pre>\n<p>\u4e0d\u80fd\u4f7f\u6307\u5b9a\u7684user\u5728\u6307\u5b9a\u7684db\u4e0a\u5177\u6709FILE\u6743\u9650\u3002\u6839\u636e\u6700\u5c0f\u6743\u9650\u7684\u539f\u5219\uff0c\u8fd9\u4e2a\u65b9\u6cd5\u5e76\u4e0d\u5b89\u5168\uff0c\u6545\u4e0d\u63a8\u8350\u4f7f\u7528\u3002<\/p>\n<p>3\uff09\u4fee\u6539 ~\/.my.cnf \u4e2d\u7684\u914d\u7f6e\uff0c\u5982\u4e0b\uff1a<\/p>\n<pre class=\"lang:default decode:true\">[mysql]\nlocal-infile<\/pre>\n<p>\u53c2\u8003\u94fe\u63a5\uff1a<\/p>\n<ul>\n<li><a href=\"http:\/\/blog.csdn.net\/slvher\/article\/details\/8768468\" target=\"_blank\">http:\/\/blog.csdn.net\/slvher\/article\/details\/8768468<\/a><\/li>\n<li><a href=\"http:\/\/www.webhostingtalk.com\/showthread.php?t=375379\" target=\"_blank\">http:\/\/www.webhostingtalk.com\/showthread.php?t=375379<\/a><\/li>\n<li><a href=\"http:\/\/bytes.com\/topic\/mysql\/answers\/880007-how-normal-user-can-load-data-into-table\" target=\"_blank\">http:\/\/bytes.com\/topic\/mysql\/answers\/880007-how-normal-user-can-load-data-into-table<\/a><\/li>\n<li><a href=\"http:\/\/www.markhneedham.com\/blog\/2011\/01\/18\/mysql-the-used-command-is-not-allowed-with-this-mysql-version\/\" target=\"_blank\">http:\/\/www.markhneedham.com\/blog\/2011\/01\/18\/mysql-the-used-command-is-not-allowed-with-this-mysql-version\/<\/a><\/li>\n<\/ul>\n<h6>5.\u300cload data\u300d\u7684\u8bed\u6cd5<\/h6>\n<p>\u5c06\u6307\u5b9acolumn\u5b57\u6bb5\u7684\u90e8\u5206\u653e\u5728\u6700\u540e\uff0c\u53c2\u8003\uff1a<\/p>\n<ul>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/load-data.html\" target=\"_blank\">http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/load-data.html<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/23875304\/load-data-infile-error-mysql-throws-a-syntax-error-when-specifying-columns\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/23875304\/load-data-infile-error-mysql-throws-a-syntax-error-when-specifying-columns<\/a><\/li>\n<\/ul>\n<p>=EOF=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u641c\u7d22\u5173\u952e\u5b57\uff1a mysql insert many data mysql \u6279\u91cf \u63d2\u5165 \u6570\u636e my [&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,23,7],"tags":[579,580,16],"class_list":["post-2589","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-programing","tag-insert","tag-load-data","tag-mysql"],"views":5350,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2589","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=2589"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2589\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2589"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2589"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2589"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}