{"id":2030,"date":"2015-03-28T11:16:11","date_gmt":"2015-03-28T03:16:11","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2030"},"modified":"2015-03-28T11:16:11","modified_gmt":"2015-03-28T03:16:11","slug":"mysql%e7%9a%84%e4%b8%80%e4%ba%9btips%e6%80%bb%e7%bb%93_2","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2030.html","title":{"rendered":"MySQL\u7684\u4e00\u4e9btips\u603b\u7ed3_2"},"content":{"rendered":"<h5>1.MySQL\u4e2d\u7684\u901a\u914d\u7b26\u548cescape\u8f6c\u4e49<\/h5>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/3070411\/mysql-wildcards-and\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/3070411\/mysql-wildcards-and<\/a><\/li>\n<li><a href=\"http:\/\/www.mysqltutorial.org\/mysql-like\/\" target=\"_blank\">http:\/\/www.mysqltutorial.org\/mysql-like\/<\/a><\/li>\n<li><a href=\"http:\/\/www.2cto.com\/database\/201303\/195794.html\" target=\"_blank\">MySql\u7684like\u8bed\u53e5\u4e2d\u7684\u901a\u914d\u7b26\uff1a\u767e\u5206\u53f7\u3001\u4e0b\u5212\u7ebf\u548cescape<\/a><\/li>\n<\/ul>\n<h6>\u53c2\u8003\u4fe1\u606f\uff1a<\/h6>\n<p>MySql\u7684like\u8bed\u53e5\u4e2d\u7684\u901a\u914d\u7b26\uff1a\u767e\u5206\u53f7\u3001\u4e0b\u5212\u7ebf\u548cescape<\/p>\n<p>% \u4ee3\u8868\u4efb\u610f\u591a\u4e2a\u5b57\u7b26<\/p>\n<pre class=\"lang:default decode:true\">select * from user where username like '%zero';\nselect * from user where username like 'zero%';\nselect * from user where username like '%zero%';<\/pre>\n<p>_ \u4ee3\u8868\u4efb\u610f\u4e00\u4e2a\u5b57\u7b26<\/p>\n<pre class=\"lang:default decode:true\">select * from user where username like '_';\nselect * from user where username like 'zer_';\nselect * from user where username like '_ero';<\/pre>\n<p>\u5982\u679c\u6211\u5c31\u771f\u7684\u8981\u67e5%\u6216\u8005_\uff0c\u600e\u4e48\u529e\u5462\uff1f<strong>\u4f7f\u7528escape<\/strong>\uff0c<span style=\"color: #ff0000;\">\u8f6c\u4e49\u5b57\u7b26\u540e\u9762\u7684%\u6216_\u5c31\u4e0d\u4f5c\u4e3a\u901a\u914d\u7b26\u4e86\uff0c\u6ce8\u610f\u524d\u9762\u6ca1\u6709\u8f6c\u4e49\u5b57\u7b26\u7684%\u548c_\u4ecd\u7136\u8d77\u901a\u914d\u7b26\u4f5c\u7528<\/span>\uff1a<\/p>\n<pre class=\"lang:default decode:true\">select username from user where username like '%zero\/_%' escape '\/';\nselect username from user where username like '%zero\/%%' escape '\/';<\/pre>\n<p>\u5b9e\u9645\u6d4b\u8bd5\u60c5\u51b5\uff08\u4e4b\u524d\u5b66\u4e60Python\u7684MySQLdb\u6a21\u5757\u65f6\u65b0\u5efa\u7684\u6570\u636e\u5e93\uff09\uff1a<\/p>\n<p><a href=\"http:\/\/ixyzero.com\/blog\/wp-content\/uploads\/2015\/03\/mysql_escape.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2031\" src=\"http:\/\/ixyzero.com\/blog\/wp-content\/uploads\/2015\/03\/mysql_escape.png\" alt=\"mysql_escape\" width=\"628\" height=\"465\" \/><\/a><\/p>\n<h5>2.MySQL\u4e2d\u4e0d\u7b49\u4e8e\u7684\u5199\u6cd5<\/h5>\n<table style=\"height: 45px;\" width=\"472\">\n<tbody>\n<tr>\n<td width=\"87\"><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/comparison-operators.html#operator_not-equal\">!=, &lt;&gt;<\/a><\/td>\n<td width=\"149\">Not equal operator<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/comparison-operators.html\" target=\"_blank\">https:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/comparison-operators.html<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/non-typed-operators.html\" target=\"_blank\">https:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/non-typed-operators.html<\/a><\/li>\n<li><a href=\"http:\/\/www.jb51.net\/article\/40264.htm\" target=\"_blank\">mysql \u4e0d\u7b49\u4e8e \u7b26\u53f7\u5199\u6cd5<\/a><\/li>\n<\/ul>\n<h5>3.MySQL\u4e2ddatetime\u7c7b\u578b\u53d8\u91cf\u7684\u6bd4\u8f83<\/h5>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<p><a href=\"http:\/\/blog.sina.com.cn\/s\/blog_43eb83b90102e0mx.html\" target=\"_blank\">mysql\u4e2ddatetime\u6bd4\u8f83\u5927\u5c0f\u95ee\u9898<\/a><\/p>\n<h5>4.\u7f16\u5199Python\u811a\u672c\u83b7\u53d6MySQL\u5e93\u3001\u8868\u4fe1\u606f<\/h5>\n<p>\u4ee3\u7801\u7247\u6bb5\u5982\u4e0b\uff1a<\/p>\n<pre class=\"lang:default decode:true\">def get_records(cur, db, table, count=10):\n    results = []\n    cur.execute('select * from `%s`.`%s` limit %d;' % (db, table, count))\n    results = [','.join(str(v) for v in row) for row in cur.fetchall()]\n    if len(results) == 0:\n        return []\n    else:\n        return results<\/pre>\n<p>\u6613\u9519\u70b9\u5728\u4e8e\uff1acur.fetchall()\u4e4b\u540e\u7684\u5904\u7406\u3002<\/p>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/10880813\/typeerror-sequence-item-0-expected-string-int-found\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/10880813\/typeerror-sequence-item-0-expected-string-int-found<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/19641579\/python-convert-tuple-to-string\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/19641579\/python-convert-tuple-to-string<\/a><\/li>\n<li><a href=\"http:\/\/segmentfault.com\/blog\/qiwsir\/1190000000715408\" target=\"_blank\">http:\/\/segmentfault.com\/blog\/qiwsir\/1190000000715408<\/a><\/li>\n<\/ul>\n<h5>5.\u5982\u4f55\u91cd\u7f6eMySQL\u5bc6\u7801\uff1f<\/h5>\n<p>\u5728 Linux \u4e0b\uff1a<\/p>\n<p>\u5982\u679c MySQL \u6b63\u5728\u8fd0\u884c\uff0c\u9996\u5148\u6740\u4e4b\uff1a<\/p>\n<pre class=\"lang:default decode:true\">$ sudo kill -9 mysqld_pid<\/pre>\n<p>\u7136\u540e\u4ee5\u5b89\u5168\u6a21\u5f0f\u542f\u52a8 MySQL \uff1a<\/p>\n<pre class=\"lang:default decode:true\">$ bin\/mysqld_safe --skip-grant-tables &amp;<\/pre>\n<p>\u53e6\u5f00\u4e00\u4e2a MySQL\u5ba2\u6237\u7aef \u5c31\u53ef\u4ee5\u4e0d\u9700\u8981\u5bc6\u7801\u5c31\u8fdb\u5165 MySQL \u4e86\u3002\u5e76\u5728\u53e6\u5916\u4e00\u4e2a MySQL\u5ba2\u6237\u7aef \u4e2d\u6267\u884c\u547d\u4ee4\uff1a<\/p>\n<pre class=\"lang:default decode:true\">mysql&gt; update mysql.user set password=password(\"new_password\") where user=\"root\";\nmysql&gt; flush privileges;<\/pre>\n<p>\u66f4\u65b0\u4e86\u5bc6\u7801\u4e4b\u540e\u9000\u51fa\u63a7\u5236\u53f0\uff0c\u91cd\u542fMySQL\u670d\u52a1\u3002<\/p>\n<h5>6.\u65e0\u6cd5\u5173\u95edmysqld_safe\u600e\u4e48\u529e\uff1f<\/h5>\n<p>\u6709\u65f6\u5019\u4f1a\u53d1\u73b0\u521a\u624d\u542f\u52a8\u7684 mysqld_safe \u65e0\u6cd5\u5173\u95ed\u4e86\uff1a<\/p>\n<blockquote>\n<p style=\"padding-left: 30px;\"><span style=\"color: #ff0000;\">mysqld_safe mysqld restarted<\/span><\/p>\n<\/blockquote>\n<p>\u6bcfkill\u6389\u4e00\u6b21\u5c31\u4f1a\u81ea\u52a8restart\uff0c\u8ba9\u4eba\u5f88\u607c\u706b\uff0c\u627e\u4e86\u4e00\u5708\u6ca1\u627e\u5230\uff0c\u6700\u540e\u53d1\u73b0\uff1a<\/p>\n<p><a href=\"http:\/\/serverfault.com\/questions\/214922\/mysql-wont-stop-mysqld-safe-appeared-in-top\" target=\"_blank\">http:\/\/serverfault.com\/questions\/214922\/mysql-wont-stop-mysqld-safe-appeared-in-top<\/a><\/p>\n<blockquote><p><strong><span style=\"color: #ff0000;\">Remember, mysqld_safe IS NOT MYSQL. It has a loop in it to call mysqld, the actual server daemon process.<\/span><\/strong> It was probably looping itself silly trying to start up mysqld. That was all the problem was.You should always:<\/p><\/blockquote>\n<ol>\n<li>\n<blockquote><p>make sure the socket file from the last time mysqld ran is gone.<\/p><\/blockquote>\n<\/li>\n<li>\n<blockquote><p>check the mysql error log after a failed startup.<\/p><\/blockquote>\n<\/li>\n<\/ol>\n<pre class=\"lang:default decode:true \">$ sudo mv \/path\/to\/mysqld\/mysqld.pid \/path\/to\/mysqld\/mysqld.pid.bak\n$ sudo mv \/path\/to\/mysql\/mysql.sock \/path\/to\/mysql\/mysql.sock.bak\n$ sudo kill -9 mysqld_pid\n\n$ sudo service mysqld status\nmysqld dead but pid file exists\n\n$ sudo service mysqld status\nmysqld is stopped\n\n$ sudo service mysqld start\nStarting mysqld: [ OK ]<\/pre>\n<h5>7.\u600e\u6837\u6dfb\u52a0\u65b0\u7528\u6237\uff1f<\/h5>\n<p>\u76f4\u63a5grant\u547d\u4ee4\u6dfb\u52a0\u7528\u6237&amp;\u8d4b\u4e88\u76f8\u5e94\u6743\u9650\uff1a<\/p>\n<pre class=\"lang:default decode:true \">mysql&gt; CREATE DATABASE IF NOT EXISTS db_name;\nmysql&gt; GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'localhost' identified by 'new_password';\nmysql&gt; flush privileges;<\/pre>\n<h6>\u641c\u7d22\u5173\u952e\u5b57\uff1a<\/h6>\n<ul>\n<li>MySQL show grants<\/li>\n<li>MySQL add new user and database<\/li>\n<li>mysqld_safe mysqld restarted<\/li>\n<\/ul>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/show-grants.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/show-grants.html<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/create-database.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/create-database.html<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/5016505\/mysql-grant-all-privileges-on-database\">http:\/\/stackoverflow.com\/questions\/5016505\/mysql-grant-all-privileges-on-database<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/1720244\/create-new-user-in-mysql-and-give-it-full-access-to-one-database\">http:\/\/stackoverflow.com\/questions\/1720244\/create-new-user-in-mysql-and-give-it-full-access-to-one-database<\/a><\/li>\n<li><a href=\"https:\/\/www.digitalocean.com\/community\/tutorials\/how-to-create-a-new-user-and-grant-permissions-in-mysql\">https:\/\/www.digitalocean.com\/community\/tutorials\/how-to-create-a-new-user-and-grant-permissions-in-mysql<\/a><\/li>\n<li><a href=\"http:\/\/serverfault.com\/questions\/214922\/mysql-wont-stop-mysqld-safe-appeared-in-top\">http:\/\/serverfault.com\/questions\/214922\/mysql-wont-stop-mysqld-safe-appeared-in-top<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>1.MySQL\u4e2d\u7684\u901a\u914d\u7b26\u548cescape\u8f6c\u4e49 \u53c2\u8003\u94fe\u63a5\uff1a http:\/\/stackoverflow.com\/qu [&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],"tags":[166,443,16],"class_list":["post-2030","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","tag-datetime","tag-escape","tag-mysql"],"views":3252,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2030","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=2030"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2030\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2030"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2030"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2030"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}