SQL Syntax

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: sql语法

| username: TiDBer_FMWXa7ja

[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]

2
| username: tidb菜鸟一只 | Original post link

Create a real intermediate table that stores values from 1 to 10,000,000.

3
| username: Jellybean | Original post link

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.
4
| username: TiDBer_FMWXa7ja | Original post link

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.

5
| username: Jellybean | Original post link

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.

6
| username: Kongdom | Original post link

Please provide the version. If the version supports temporary tables, you can use temporary tables.

7
| username: Jellybean | Original post link

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).