使用mysql
自身的user
表,拼接其中的User
字段的值,使用GROUP_CONCAT
函数。
# 使用mysql表
USE mysql;
# 查询`user`
SELECT *
FROM `user`;
# 结果
Host User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired password_last_changed password_lifetime account_locked
localhost root Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y (BLOB) 0 bytes (BLOB) 0 bytes (BLOB) 0 bytes 0 0 0 0 mysql_native_password N 2018-12-29 10:46:47 N
localhost mysql.session N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N (BLOB) 0 bytes (BLOB) 0 bytes (BLOB) 0 bytes 0 0 0 0 mysql_native_password *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE N 2018-12-29 10:41:42 Y
localhost mysql.sys N N N N N N N N N N N N N N N N N N N N N N N N N N N N N (BLOB) 0 bytes (BLOB) 0 bytes (BLOB) 0 bytes 0 0 0 0 mysql_native_password *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE N 2018-12-29 10:41:42 Y
# 拼接`user`的值
SELECT GROUP_CONCAT(`User` separator ':') AS user
FROM `user`;
# 结果
user
mysql.session:mysql.sys:root
# 解析,GROUP_CONCAT(`列` separator '分隔符'),`separator`默认为','
# 进一步拼接,每个user的值都加上双引号,如:"A","B","C"这个形式
SELECT GROUP_CONCAT(CONCAT('"', `User`, '"') separator ',') AS user
FROM `user`;
# 结果
user
"mysql.session","mysql.sys","root"
# 解析
在GROUP_CONCAT函数内再使用CONCAT(str, str, str),其中第一个参数为左边的字符,中间的为目标列(column),第三个参数为右边的字符。
网友评论