mysql查询表，指定排除字段
查询user表中除email以外的所有字段假设数据库bbs中有user表，字段如下
id,username,password,gender,email
-------------------------------------------------------------------------
在命令行窗口输入：
SET @sql =
CONCAT('SELECT ',
(
SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), if(instr(GROUP_CONCAT(COLUMN_NAME),'email,')>0,'email,',',email'), '')
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='user' AND TABLE_SCHEMA = 'bbs'
),
' FROM user');
PREPARE stmt FROM @sql;
use bbs;
EXECUTE stmt;
---------------------------------------------------------------
解释：
下一步操作都基于上一步操作；
操作1
mysql> use bbs;
SELECT * FROM user;
+----+----------+----------+--------+--------------+
| id | username | password | gender | email |
+----+----------+----------+--------+--------------+
| 1 | jack | 670B1472 | male | jack@163.com |
操作2
mysql> SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='user' and TABLE_SCHEMA='bbs';
+-------------+
| column_name |
+-------------+
| id |
| username |
| password |
| gender |
| email |
+-------------+
从 INFORMATION_SCHEMA.COLUMNS 表中查找 数据库名为bbs且表名为user的列信息
操作3
mysql> SELECT GROUP_CONCAT(COLUMN_NAME ) from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='user' and TABLE_SCHEMA='bbs';
+-----------------------------------+
| GROUP_CONCAT(COLUMN_NAME ) |
+-----------------------------------+
| id,username,password,gender,email |
+-----------------------------------+
使用组函数GROUP_CONCAT连接COLUMN_NAME并以逗号作为分割符
操作4
mysql> SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME ),',email', '') from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='user' and TABLE_SCHEMA='bbs';
+--------------------------------------------------+
| REPLACE(GROUP_CONCAT(COLUMN_NAME ),',email', '') |
+--------------------------------------------------+
| id,username,password,gender |
+--------------------------------------------------+
把字符串"id,username,password,gender,email"中的",email"替换为空串""
操作5
最后将字符串“id,username,password,gender”前后分别拼接 "SELECT " 和 " FROM user"则可以得到结果
注意：因为排除的某个字段可能不是在最后，例如排除“id,username,password,gender,email”中的gender则要替换"gender,"所以要做这样的处理replace(str, if(instr(str, "email,")>0, "email,",",email"))---如果字符串中的列名后有逗号，则把逗号一并替换，否则只替换列名
另外，个人觉得这么处理可读性非常差，建议还是通过程序来实现。使用一些数据库工具库会方便一些，如果是用java可以使用DBUtils这个库
页:
[1]