mysql 删除某列,如何“选择*”从MySQL中的表中删除某些列？

I have a table with the following columns:

id,name,age,surname,lastname,catgory,active

Instead of: SELECT name,age,surname,lastname,catgory FROM table

How can I make something like this: SELECT * FROM table [but not select id,active]

解决方案

While many say it is best practice to explicitly list every column you want returned, there are situations where you might want to save time and omit certain columns from the results (e.g. testing). Below I have given two options that solve this problem.

1. Create a Function that retrieves all of the desired column names: ( I created a schema called functions to hold this function)

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `getTableColumns`(_schemaName varchar(100), _tableName varchar(100), _omitColumns varchar(200)) RETURNS varchar(5000) CHARSET latin1

BEGIN

SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns

WHERE table_schema = _schemaName AND table_name = _tableName AND FIND_IN_SET(COLUMN_NAME,_omitColumns) = 0 ORDER BY ORDINAL_POSITION;

END

Create and execute select statement:

SET @sql = concat('SELECT ', (SELECT

functions.getTableColumns('test', 'employees', 'age,dateOfHire')), ' FROM test.employees');

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;

2. OR without writing a function you could:

SET @sql = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM

information_schema.columns WHERE table_schema = 'test' AND table_name =

'employees' AND column_name NOT IN ('age', 'dateOfHire')),

' from test.eployees');

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;

*Replace test with your own schema name

**Replace employees with your own table name

***Replace age,dateOfHire with the columns you want to omit (you can leave it blank to return all columns or just enter one column name to omit)

** **You can adjust the lengths of the varchars in the function to meet your needs