多同列名产品表执行UNION ALL报non-object错误，求批量合并方法

Hey there! Let's tackle this problem head-on. First, that "non-object" error might pop up even with matching column names if there's a hidden mismatch (like column order being off, or subtle data type differences) or if your database is having trouble parsing the implicit * in your union. But let's skip the manual SELECT grind—here's how to dynamically generate the union query for all your product tables.

Dynamic SQL Solutions by Database #

Since you didn't specify your database, I'll cover the most common ones:

MySQL/MariaDB

Use the information_schema system table to fetch all your target tables and build the union query automatically:

-- Set up the dynamic SQL variable
SET @sql = NULL;

-- Generate the union query string
SELECT GROUP_CONCAT(
    'SELECT * FROM ', table_name SEPARATOR ' UNION ALL '
) INTO @sql
FROM information_schema.tables
WHERE table_schema = 'your_database_name' -- Replace with your DB name
AND table_name LIKE 'product_%'; -- Adjust the pattern to match your tables (e.g., product_table1, product_table2)

-- Execute the generated query
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Note: If you have a ton of tables, you might hit MySQL's group_concat_max_len limit. Temporarily increase it with SET SESSION group_concat_max_len = 1000000; before running the query.

SQL Server

Leverage sys.tables and STRING_AGG to build your union:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
    'SELECT * FROM ' + QUOTENAME(table_name), ' UNION ALL '
)
FROM sys.tables
WHERE name LIKE 'product_%'; -- Match your table naming pattern

-- Run the dynamic query
EXEC sp_executesql @sql;

PostgreSQL

Use a CTE to list your tables and string_agg to construct the union:

WITH target_tables AS (
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = 'public' -- Replace with your schema if needed
    AND table_name LIKE 'product_%'
)
SELECT string_agg(
    'SELECT * FROM ' || quote_ident(table_name), ' UNION ALL '
) INTO @sql
FROM target_tables;

-- Execute the query
EXECUTE @sql;

Critical Checks to Avoid Errors #

• Verify column order & data types: Even if column names match, if Table A has id INT, name VARCHAR and Table B has name VARCHAR, id INT, the union will fail. Double-check that all tables have identical column sequences and compatible types.
• Filter tables carefully: Make sure your LIKE pattern only includes the product tables you want to union—don't accidentally pull in unrelated tables.
• Test with a small subset first: Before running on all tables, test the dynamic query generation with 2-3 tables to confirm it works.

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