如何在MySQL中为table_a添加is_in_table_b列，标识指定日期范围的数据是否存在于table_b

Got it, let's work through this problem. You need to add a flag column to table_a that indicates if the row exists in table_b within the date range '2021-01-01 00:00:00' to '2021-03-31 23:59:59'. Here's a straightforward, performance-friendly approach:

Step 1: Add the New Column #

First, we'll add the is_in_table_b column to table_a. We'll use TINYINT(1) since it's perfect for storing 0/1 flags, and set a default of 0 (since we'll only update rows that exist in table_b to 1 later):

ALTER TABLE table_a ADD COLUMN is_in_table_b TINYINT(1) DEFAULT 0;

Step 2: Update the Column Values #

The most efficient way to update this flag is using a LEFT JOIN between table_a and table_b, which avoids the pitfalls of NOT IN (like handling NULL values) and performs better with large datasets.

This query will set is_in_table_b to 1 only for rows where:

• The id matches between table_a and table_b
• The date_b in table_b falls within our target range
• The date_a in table_a also falls within the target range (since you only care about this date window)

UPDATE table_a a
LEFT JOIN table_b b 
  ON a.id = b.id 
  AND b.date_b BETWEEN '2021-01-01 00:00:00' AND '2021-03-31 23:59:59'
SET a.is_in_table_b = 1
WHERE a.date_a BETWEEN '2021-01-01 00:00:00' AND '2021-03-31 23:59:59'
AND b.id IS NOT NULL;

Optional: Optimize with Indexes #

If you're working with large datasets (your tables have tens of thousands of rows), adding indexes on the join and date columns will speed up the update significantly:

-- Index for table_a to speed up filtering and joining
CREATE INDEX idx_table_a_id_date ON table_a(id, date_a);

-- Index for table_b to speed up matching by id and date range
CREATE INDEX idx_table_b_id_date ON table_b(id, date_b);

Step 3: Verify the Results #

To make sure everything works as expected, run a sample query to check the flag values:

SELECT id, name, code, date_a, is_in_table_b
FROM table_a
WHERE date_a BETWEEN '2021-01-01 00:00:00' AND '2021-03-31 23:59:59'
LIMIT 10;

Alternative: Using a CASE Statement #

If you prefer a subquery approach (though less performant for large data), you can use a CASE statement to set the flag:

UPDATE table_a
SET is_in_table_b = CASE
    WHEN id IN (
        SELECT b.id 
        FROM table_b b
        WHERE b.date_b BETWEEN '2021-01-01 00:00:00' AND '2021-03-31 23:59:59'
    ) 
    AND date_a BETWEEN '2021-01-01 00:00:00' AND '2021-03-31 23:59:59'
    THEN 1
    ELSE 0
END;

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