Introduction

MySQL does not support a native SELECT * EXCEPT some_column syntax. If you want every column except one, you must either list the columns explicitly or generate that list dynamically. The best choice depends on whether you care more about stable, readable SQL or about adapting automatically to schema changes.

The Best Default Is Explicit Column Selection

For ordinary application queries, the best answer is still to name the columns you want.

SELECT id, name, email, created_at
FROM users;

This is not glamorous, but it is clear, stable, and reviewable. It also protects you from the hidden problems of SELECT *, such as returning unnecessary data or silently changing result shape when the schema evolves.

If the only reason you want "all except one" is convenience, explicit selection is usually the right engineering choice.

MySQL Has No Native Exclusion Syntax

Some SQL users expect syntax like this:

SELECT * EXCEPT(password_hash)
FROM users;

MySQL does not have that feature. If you need exclusion behavior, you must build it yourself.

That design fact is important because it changes the tradeoff. You are not choosing between two equally built-in features. You are choosing between explicit SQL and dynamic SQL generation.

Generate the Column List from INFORMATION_SCHEMA

If the table has many columns or changes frequently, you can generate a query from metadata.

1SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY ORDINAL_POSITION)
2INTO @cols
3FROM INFORMATION_SCHEMA.COLUMNS
4WHERE TABLE_SCHEMA = 'app_db'
5  AND TABLE_NAME = 'users'
6  AND COLUMN_NAME <> 'password_hash';
7
8SET @sql = CONCAT('SELECT ', @cols, ' FROM `users`');
9PREPARE stmt FROM @sql;
10EXECUTE stmt;
11DEALLOCATE PREPARE stmt;

This works, but it is more complicated and less transparent than explicit SQL. It is appropriate for administrative tooling or generated reports, not usually for hand-written application queries.

Use Dynamic Selection in Scripts and Admin Tasks

When you are building export tooling, schema-driven reporting, or one-off admin jobs, generating the column list in application code can be easier to control.

1import mysql.connector
2
3conn = mysql.connector.connect(host="localhost", user="app", password="secret", database="app_db")
4cur = conn.cursor()
5
6cur.execute("""
7    SELECT COLUMN_NAME
8    FROM INFORMATION_SCHEMA.COLUMNS
9    WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME <> %s
10    ORDER BY ORDINAL_POSITION
11""", ("app_db", "users", "password_hash"))
12
13columns = [f"`{row[0]}`" for row in cur.fetchall()]
14sql = f"SELECT {', '.join(columns)} FROM `users`"
15print(sql)

This pattern is often easier to test and maintain than embedding dynamic SQL inside stored statements.

Ask Whether You Should Exclude at All

A request for "all columns except one" often signals a deeper design question. For example:

• is the excluded column sensitive
• is the result intended for an API or export
• is SELECT * being used too casually

If the excluded column is something like password_hash or a large blob field, the better answer may be to define a safe projection explicitly and reuse it. That is more robust than dynamically excluding sensitive data every time.

Views Can Help Reuse Safe Column Sets

If the same exclusion pattern is used repeatedly, a view can give you a stable named projection.

CREATE VIEW user_public_view AS
SELECT id, name, email, created_at
FROM users;

Then consumers query the view instead of rebuilding the column list every time. This is often better than clever query generation when the shape is meant to be reused consistently.

Common Pitfalls

• Assuming MySQL has a built-in SELECT * EXCEPT syntax when it does not.
• Using dynamic SQL for routine application queries that would be clearer as explicit column lists.
• Building metadata-driven SQL without quoting identifiers safely.
• Treating exclusion of sensitive columns as an ad hoc convenience instead of defining a deliberate safe projection.
• Overusing SELECT * and then compensating with complex exclusion logic later.

Summary

• MySQL has no native way to select all columns except one with a single built-in clause.
• The best default is to list the desired columns explicitly.
• Dynamic SQL based on INFORMATION_SCHEMA works when schema-driven generation is genuinely needed.
• Administrative scripts and reporting tools are better places for dynamic exclusion logic than core application queries.
• If the exclusion is repeated often, a view or explicit projection is usually the cleaner design.