{"id":2120,"date":"2015-05-01T00:28:49","date_gmt":"2015-04-30T16:28:49","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2120"},"modified":"2015-05-01T00:28:49","modified_gmt":"2015-04-30T16:28:49","slug":"mysql%e4%b8%ad%e5%a6%82%e4%bd%95%e7%9f%a5%e9%81%93%e5%93%aa%e4%ba%9b%e7%94%a8%e6%88%b7%e5%85%b7%e6%9c%89%e5%93%aa%e4%ba%9b%e5%ba%93%e8%a1%a8%e7%9a%84%e6%9d%83%e9%99%90%ef%bc%9f","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2120.html","title":{"rendered":"MySQL\u4e2d\u5982\u4f55\u77e5\u9053\u54ea\u4e9b\u7528\u6237\u5177\u6709\u54ea\u4e9b\u5e93\u8868\u7684\u6743\u9650\uff1f"},"content":{"rendered":"<h5><strong>MySQL\u4e2d\u5982\u4f55\u77e5\u9053\u54ea\u4e9b\u7528\u6237\u5177\u6709\u54ea\u4e9b\u5e93\u8868\u7684\u6743\u9650\uff1f<\/strong><\/h5>\n<h6>\u53c2\u8003\u89e3\u7b54\uff1a<\/h6>\n<pre class=\"lang:default decode:true \">SELECT host,db,user,password from mysql.user;\nSELECT host,db,user FROM mysql.db WHERE db='db_name';\nSELECT host,db,user,Table_name FROM mysql.tables_priv WHERE db='db_name';\nSELECT host,db,user,Table_name,Column_name FROM mysql.columns_priv WHERE db='db_name';\nSELECT host,db,user,Proc_priv FROM mysql.procs_priv WHERE db='db_name';<\/pre>\n<pre class=\"lang:default decode:true \">mysql&gt; show tables from mysql;\n+---------------------------+\n| Tables_in_mysql           |\n+---------------------------+\n| columns_priv              |\n| db                        |\n| event                     |\n| func                      |\n| general_log               |\n| help_category             |\n| help_keyword              |\n| help_relation             |\n| help_topic                |\n| host                      |\n| ndb_binlog_index          |\n| plugin                    |\n| proc                      |\n| procs_priv                |\n| servers                   |\n| slow_log                  |\n| tables_priv               |\n| time_zone                 |\n| time_zone_leap_second     |\n| time_zone_name            |\n| time_zone_transition      |\n| time_zone_transition_type |\n| user                      |\n+---------------------------+\n23 rows in set (0.00 sec)\n\nmysql&gt; desc mysql.db;\n+-----------------------+---------------+------+-----+---------+-------+\n| Field                 | Type          | Null | Key | Default | Extra |\n+-----------------------+---------------+------+-----+---------+-------+\n| Host                  | char(60)      | NO   | PRI |         |       |\n| Db                    | char(64)      | NO   | PRI |         |       |\n| User                  | char(16)      | NO   | PRI |         |       |\n| Select_priv           | enum('N','Y') | NO   |     | N       |       |\n| Insert_priv           | enum('N','Y') | NO   |     | N       |       |\n| Update_priv           | enum('N','Y') | NO   |     | N       |       |\n| Delete_priv           | enum('N','Y') | NO   |     | N       |       |\n| Create_priv           | enum('N','Y') | NO   |     | N       |       |\n| Drop_priv             | enum('N','Y') | NO   |     | N       |       |\n| Grant_priv            | enum('N','Y') | NO   |     | N       |       |\n| References_priv       | enum('N','Y') | NO   |     | N       |       |\n| Index_priv            | enum('N','Y') | NO   |     | N       |       |\n| Alter_priv            | enum('N','Y') | NO   |     | N       |       |\n| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |\n| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |\n| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |\n| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |\n| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |\n| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |\n| Execute_priv          | enum('N','Y') | NO   |     | N       |       |\n| Event_priv            | enum('N','Y') | NO   |     | N       |       |\n| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |\n+-----------------------+---------------+------+-----+---------+-------+\n22 rows in set (0.00 sec)\n\nmysql&gt; desc mysql.tables_priv;\n+-------------+-------------+------+-----+-------------------+-----------------------------+\n| Field       | Type        | Null | Key | Default           | Extra                       |\n+-------------+-------------+------+-----+-------------------+-----------------------------+\n| Host        | char(60)    | NO   | PRI |                   |                             |\n| Db          | char(64)    | NO   | PRI |                   |                             |\n| User        | char(16)    | NO   | PRI |                   |                             |\n| Table_name  | char(64)    | NO   | PRI |                   |                             |\n| Grantor     | char(77)    | NO   | MUL |                   |                             |\n| Timestamp   | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |\n| Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO   | | | |\n| Column_priv | set('Select','Insert','Update','References')                                                                                      | NO   | | | |\n+-------------+-------------+------+-----+-------------------+-----------------------------+\n8 rows in set (0.00 sec)\n\nmysql&gt; desc mysql.columns_priv;\n+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+\n| Field       | Type                                         | Null | Key | Default           | Extra                       |\n+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+\n| Host        | char(60)                                     | NO   | PRI |                   |                             |\n| Db          | char(64)                                     | NO   | PRI |                   |                             |\n| User        | char(16)                                     | NO   | PRI |                   |                             |\n| Table_name  | char(64)                                     | NO   | PRI |                   |                             |\n| Column_name | char(64)                                     | NO   | PRI |                   |                             |\n| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |\n| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                             |\n+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+\n7 rows in set (0.00 sec)\n\nmysql&gt; desc mysql.procs_priv;\n+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+\n| Field        | Type                                   | Null | Key | Default           | Extra                       |\n+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+\n| Host         | char(60)                               | NO   | PRI |                   |                             |\n| Db           | char(64)                               | NO   | PRI |                   |                             |\n| User         | char(16)                               | NO   | PRI |                   |                             |\n| Routine_name | char(64)                               | NO   | PRI |                   |                             |\n| Routine_type | enum('FUNCTION','PROCEDURE')           | NO   | PRI | NULL              |                             |\n| Grantor      | char(77)                               | NO   | MUL |                   |                             |\n| Proc_priv    | set('Execute','Alter Routine','Grant') | NO   |     |                   |                             |\n| Timestamp    | timestamp                              | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |\n+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+\n8 rows in set (0.00 sec)<\/pre>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"http:\/\/serverfault.com\/questions\/263868\/how-to-know-all-users-that-can-access-certain-database-mysql\" target=\"_blank\">http:\/\/serverfault.com\/questions\/263868\/how-to-know-all-users-that-can-access-certain-database-mysql<\/a><\/li>\n<li><a href=\"http:\/\/serverfault.com\/questions\/2616\/which-mysql-users-have-access-to-a-database\" target=\"_blank\">http:\/\/serverfault.com\/questions\/2616\/which-mysql-users-have-access-to-a-database<\/a><\/li>\n<\/ul>\n<p>==<\/p>\n<h5><strong>MySQL\u4e2d\u67e5\u770b\u5f53\u524d\u7684\u8fde\u63a5\u4fe1\u606f<\/strong><\/h5>\n<ul>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/3613704\/mysql-show-current-connection-info\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/3613704\/mysql-show-current-connection-info<\/a><\/li>\n<\/ul>\n<p>\u7ed3\u8bba\uff1a\u4f7f\u7528status\u547d\u4ee4<\/p>\n<p>==<\/p>\n<h5>MySQL\u7684string\u51fd\u6570<\/h5>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/string-functions.html\" target=\"_blank\">MySQL :: MySQL 5.5 Reference Manual :: 12.5 String Functions<\/a><\/li>\n<\/ul>\n<p>==<\/p>\n<h5>\u5b57\u7b26\u4e32\u6bd4\u8f83\u51fd\u6570<\/h5>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/string-comparison-functions.html\" target=\"_blank\">MySQL :: MySQL 5.5 Reference Manual :: 12.5.1 String Comparison Functions<\/a><\/li>\n<\/ul>\n<p>==<\/p>\n<h5>MySQL\u7684\u6b63\u5219\u8868\u8fbe\u5f0f<\/h5>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/regexp.html\" target=\"_blank\">MySQL :: MySQL 5.5 Reference Manual :: 12.5.2 Regular Expressions<\/a><\/li>\n<\/ul>\n<p>==<\/p>\n<h6>\u5982\u4f55\u83b7\u53d6MySQL\u7684\u5b57\u7b26\u4e32\u957f\u5ea6\uff1f<\/h6>\n<p>\u641c\u7d22\u5173\u952e\u5b57\uff1aMySQL string length<\/p>\n<h6>\u53c2\u8003\u89e3\u7b54\uff1a<\/h6>\n<ul>\n<li>LENGTH()\u00a0returns the length of the\u00a0string <span style=\"color: #ff0000;\"><strong>measured in bytes<\/strong><\/span>.<\/li>\n<li>CHAR_LENGTH()\u00a0returns the length of the\u00a0string <strong><span style=\"color: #ff0000;\">measured in characters<\/span><\/strong>.<\/li>\n<\/ul>\n<p>This is especially relevant for Unicode, in which most characters are encoded in two bytes. Or UTF-8, where the number of bytes varies. For example:<\/p>\n<pre class=\"lang:default decode:true\">select length(_utf8 '\u20ac'), char_length(_utf8 '\u20ac')\n--&gt; 3, 1<\/pre>\n<p>As you can see the Euro sign occupies 3 bytes (it&#8217;s encoded as\u00a00xE282AC\u00a0in UTF-8) even though it&#8217;s only one character.<\/p>\n<p>=<\/p>\n<ul>\n<li>You are looking for\u00a0CHAR_LENGTH()\u00a0to get <span style=\"color: #ff0000;\"><strong>the number of characters in a string<\/strong><\/span>.<\/li>\n<li>For multi-byte charsets\u00a0LENGTH()\u00a0will give you the <span style=\"color: #ff0000;\"><strong>number of bytes the string occupies<\/strong><\/span>, while\u00a0CHAR_LENGTH()\u00a0will return the number of characters.<\/li>\n<\/ul>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/1870937\/mysql-how-to-select-data-by-string-length\">http:\/\/stackoverflow.com\/questions\/1870937\/mysql-how-to-select-data-by-string-length<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/1734334\/mysql-length-vs-char-length\">http:\/\/stackoverflow.com\/questions\/1734334\/mysql-length-vs-char-length<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>MySQL\u4e2d\u5982\u4f55\u77e5\u9053\u54ea\u4e9b\u7528\u6237\u5177\u6709\u54ea\u4e9b\u5e93\u8868\u7684\u6743\u9650\uff1f \u53c2\u8003\u89e3\u7b54\uff1a SELECT host,db,user,pass [&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,6],"tags":[499,500,16,501,502],"class_list":["post-2120","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-other","tag-char_length","tag-length","tag-mysql","tag-priv","tag-status"],"views":2932,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2120","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=2120"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2120\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}