求MySQL/MariaDB中关联查询自动规避重复列名且自动包含新增字段的解决方案

Simplest Solution: Use USING Instead of ON #

The easiest way to fix the duplicate column issue while keeping all fields (and automatically including future ones) is to swap your ON join condition for USING. When you use USING for a column present in both tables, MySQL/MariaDB will only include a single instance of that column in the result set—no duplicates, no manual field listing required.

Your revised query becomes:

SELECT * FROM Supplier INNER JOIN Product USING (idSupplier);

This returns every column from both tables, but only one idSupplier column (since the join ensures their values are identical, it doesn’t matter which table it comes from). Any new columns added to Supplier or Product later will automatically show up in results without you touching the query.

If USING Isn’t Sufficient: Dynamic SQL with INFORMATION_SCHEMA #

If you need more control (like explicitly retaining Supplier.idSupplier and excluding Product.idSupplier, or handling non-join duplicate columns), you can generate your SELECT clause dynamically using MySQL’s metadata stored in INFORMATION_SCHEMA. This guarantees your query always includes all current columns without manual maintenance.

Option 1: Reusable Stored Procedure

Create a stored procedure that builds and executes the query dynamically each time it’s called:

DELIMITER //
CREATE PROCEDURE GetSupplierProducts()
BEGIN
    DECLARE select_columns TEXT;

    -- Build SELECT clause: all Supplier columns + all Product columns except idSupplier
    SELECT GROUP_CONCAT(
        CASE
            WHEN table_name = 'Supplier' THEN CONCAT('`', column_name, '`')
            WHEN table_name = 'Product' AND column_name != 'idSupplier' THEN CONCAT('`', column_name, '`')
        END
        SEPARATOR ', '
    ) INTO select_columns
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name IN ('Supplier', 'Product');

    -- Execute the dynamic query
    SET @sql = CONCAT('SELECT ', select_columns, ' FROM Supplier INNER JOIN Product ON Supplier.idSupplier = Product.idSupplier');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Call it with:

CALL GetSupplierProducts();

This will automatically include any new columns added to either table (excluding Product.idSupplier to avoid duplicates).

Option 2: Ad-Hoc Dynamic Query

If you don’t want a stored procedure, generate the SELECT clause on the fly and execute it manually:

-- First, get the dynamic SELECT clause
SELECT GROUP_CONCAT(
    CASE
        WHEN table_name = 'Supplier' THEN CONCAT('`', column_name, '`')
        WHEN table_name = 'Product' AND column_name != 'idSupplier' THEN CONCAT('`', column_name, '`')
    END
    SEPARATOR ', '
) AS select_clause
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name IN ('Supplier', 'Product');

-- Copy the result of select_clause and paste it into the query below
SELECT [paste_select_clause_here] FROM Supplier INNER JOIN Product ON Supplier.idSupplier = Product.idSupplier;

This works for one-off queries but is less convenient for repeated use.

Key Notes #

• The USING method is ideal for most cases where you just want to eliminate duplicate join columns and keep things simple.
• Dynamic SQL is better if you need granular control over which columns are included/excluded, or if there are duplicate columns not involved in the join.
• Both approaches ensure new columns added to your tables are automatically included in future results—no manual query updates needed when your schema changes.

内容的提问来源于stack exchange，提问作者Jan Wiesemann