MySQL中如何知道哪些用户具有哪些库表的权限？
参考解答：
SELECT host,db,user,password from mysql.user; SELECT host,db,user FROM mysql.db WHERE db='db_name'; SELECT host,db,user,Table_name FROM mysql.tables_priv WHERE db='db_name'; SELECT host,db,user,Table_name,Column_name FROM mysql.columns_priv WHERE db='db_name'; SELECT host,db,user,Proc_priv FROM mysql.procs_priv WHERE db='db_name';
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
mysql> desc mysql.tables_priv;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | char(77) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+-------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
mysql> desc mysql.columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)
mysql> desc mysql.procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Routine_name | char(64) | NO | PRI | | |
| Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | |
| Grantor | char(77) | NO | MUL | | |
| Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
参考链接：
- http://serverfault.com/questions/263868/how-to-know-all-users-that-can-access-certain-database-mysql
- http://serverfault.com/questions/2616/which-mysql-users-have-access-to-a-database
==
MySQL中查看当前的连接信息
结论：使用status命令
==
MySQL的string函数
==
字符串比较函数
==
MySQL的正则表达式
==
如何获取MySQL的字符串长度？
搜索关键字：MySQL string length
参考解答：
- LENGTH() returns the length of the string measured in bytes.
- CHAR_LENGTH() returns the length of the string measured in characters.
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:
select length(_utf8 '€'), char_length(_utf8 '€') --> 3, 1
As you can see the Euro sign occupies 3 bytes (it’s encoded as 0xE282AC in UTF-8) even though it’s only one character.
=
- You are looking for CHAR_LENGTH() to get the number of characters in a string.
- For multi-byte charsets LENGTH() will give you the number of bytes the string occupies, while CHAR_LENGTH() will return the number of characters.
《 “MySQL中如何知道哪些用户具有哪些库表的权限？” 》 有 2 条评论
用Golang写的数据库防火墙
https://github.com/nim4/DBShield
MySQL中字段的「字节长度」和「字符长度」
https://stackoverflow.com/questions/1870937/mysql-how-to-select-data-by-string-length
`
You are looking for CHAR_LENGTH() to get the number of characters in a string.
For multi-byte charsets LENGTH() will give you the number of bytes the string occupies, while CHAR_LENGTH() will return the number of characters.
CHAR_LENGTH() –获取字符串的字符个数
LENGTH() –对于多字节的字符集而言，它会返回字符串实际占用的字节数
`