You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
36 lines
1023 B
36 lines
1023 B
USE lifanghe_education;
|
|
|
|
CREATE TABLE IF NOT EXISTS appointment_duplicates_backup LIKE appointments;
|
|
|
|
INSERT INTO appointment_duplicates_backup
|
|
SELECT a.*
|
|
FROM appointments a
|
|
JOIN appointments b
|
|
ON a.user_id = b.user_id
|
|
AND (a.created_at < b.created_at OR (a.created_at = b.created_at AND a.id < b.id))
|
|
LEFT JOIN appointment_duplicates_backup bak ON bak.id = a.id
|
|
WHERE bak.id IS NULL;
|
|
|
|
DELETE a
|
|
FROM appointments a
|
|
JOIN appointments b
|
|
ON a.user_id = b.user_id
|
|
AND (a.created_at < b.created_at OR (a.created_at = b.created_at AND a.id < b.id));
|
|
|
|
SET @schema_name = DATABASE();
|
|
SET @has_unique := (
|
|
SELECT COUNT(1)
|
|
FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = @schema_name
|
|
AND TABLE_NAME = 'appointments'
|
|
AND INDEX_NAME = 'uk_appointments_user'
|
|
);
|
|
SET @add_unique_sql := IF(
|
|
@has_unique = 0,
|
|
'ALTER TABLE appointments ADD UNIQUE KEY uk_appointments_user (user_id)',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE add_unique_stmt FROM @add_unique_sql;
|
|
EXECUTE add_unique_stmt;
|
|
DEALLOCATE PREPARE add_unique_stmt;
|