Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: sql语法
[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Encountered Problem: Problem Phenomenon and Impact]
How to compose a field in a temporary table with a batch of numbers in an SQL statement, for example:
SELECT 1 as houseId
UNION SELECT 2 as houseId
UNION SELECT 3 as houseId
If I have 1 million houseIds, the above method is too cumbersome. Is there a better solution?
[Attachment: Screenshot/Log/Monitoring]
Create a real intermediate table that stores values from 1 to 10,000,000.
Your original requirement is to insert a given set of numbers into a table, or do you just need 1 to 1 million different numbers?
- If it’s a given set of numbers, you can create a new table and then load the data into it.
- If you just need numbers, you can use the sequence generator provided by TiDB.
The original requirement is to associate the house table with another table, but the two tables are in two different databases. Currently, the houseID set is first queried and then used as a parameter to call another service’s interface to associate through in(houseIds) (the performance of using in operation with millions of houseIDs is too low). I want to use houseIDs as several intermediate tables for inner join operations in the code, which can greatly improve efficiency.
I understand, your scenario is using the business-provided numbers, and your approach is actually correct. You can import the houseID into the target table and then use an inner join for efficient querying.
If you are using version 6.1 or later, there is an additional optimization point. You can set this houseID table as an in-memory cache table, which will make your join operations even faster.
Please provide the version. If the version supports temporary tables, you can use temporary tables.
If the two datasets are in the same TiDB cluster, you can indeed use SELECT and then optimize with temporary tables. If they are in different clusters, you will need to load them into the same cluster first and then use cache tables (or temporary tables).