创建Temporary table后未在数据库显示且无报错的技术问题咨询

Hey there! Let's break down why your temporary table isn't showing up even though the CREATE TEMPORARY TABLE statement ran without errors—this is a super common gotcha once you understand how temp tables work under the hood.

Core Reason: Temporary Tables Are Session-Isolated #

First off, nearly all databases design temporary tables to be only visible to the session/connection that created them. That means:

• If you created the temp table in one terminal tab, GUI window, or database connection, you won't see it in another.
• If your database client auto-disconnects after idle time, the session (and temp table) gets destroyed without warning.
• You need to run your INSERT statements in the exact same connection where you created the table.

Database-Specific Quirks to Check #

Let's dive into how different databases handle temp tables, and how to verify they exist:

MySQL #

• CREATE TEMPORARY TABLE creates a session-only table that won't appear in SHOW TABLES results. To confirm it exists:

  SHOW TEMPORARY TABLES;
  -- Or query the information schema:
  SELECT table_name FROM information_schema.tables 
  WHERE table_type = 'TEMPORARY' AND table_schema = DATABASE();
  

• If a regular table with the same name exists, your temp table will override it only in your current session—other sessions still see the regular table, which can confuse you into thinking the temp table didn't create.

PostgreSQL #

• Temp tables are stored in the pg_temp schema (unique to your session). To list them:

  SELECT tablename FROM pg_tables WHERE schemaname = 'pg_temp';
  

• In the psql command line, use \dt pg_temp.* to view your session's temp tables. They'll be automatically dropped when your session ends.

SQL Server #

• Local temp tables (named with #TableName) are session-isolated. To check for them in the tempdb:

  SELECT name FROM tempdb.sys.tables 
  WHERE name LIKE '#YourTableName%'; -- Temp tables get a unique suffix
  

• Global temp tables (named with ##TableName) are visible to all sessions, but are dropped when the creating session closes.

Quick Fixes to Try #

1. Test in the same session: Right after creating the temp table, run SELECT * FROM your_temp_table;—if it returns an empty result set, the table exists! You can then run your INSERT here.
2. Check connection status: Make sure your database client hasn't auto-disconnected. Some GUI tools (like MySQL Workbench) drop idle connections, which wipes your temp table.
3. Use the right query to list temp tables: Don't rely on regular SHOW TABLES or schema browsers—use the database-specific commands above to see session temp tables.
4. Avoid name collisions: If you're reusing a table name that already exists as a regular table, rename your temp table to avoid confusion.

Once you confirm the table exists in your current session, your INSERT statements should work exactly as expected.

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