26 lines
988 B
MySQL
26 lines
988 B
MySQL
|
USE vn;
|
||
|
|
UPDATE vn.deviceProduction dp
|
||
|
|
JOIN (
|
||
|
|
SELECT id
|
||
|
|
FROM deviceProduction dp
|
||
|
|
WHERE LENGTH(android_id) < 16
|
||
|
|
) sub ON sub.id = dp.id
|
||
|
|
SET dp.android_id = NULL;
|
||
|
|
UPDATE vn.deviceProduction dp
|
||
|
|
JOIN (
|
||
|
|
SELECT id
|
||
|
|
FROM (
|
||
|
|
SELECT id, COUNT(*) total, android_id
|
||
|
|
FROM deviceProduction dp
|
||
|
|
WHERE android_id IS NOT NULL
|
||
|
|
GROUP BY android_id
|
||
|
|
) sub
|
||
|
|
WHERE total > 1
|
||
|
|
) sub ON sub.id = dp.id
|
||
|
|
SET dp.android_id = NULL;
|
||
|
|
ALTER TABLE vn.deviceLog DROP COLUMN deviceProductionFk;
|
||
|
|
ALTER TABLE vn.deviceLog MODIFY COLUMN android_id varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL;
|
||
|
|
ALTER TABLE vn.deviceProduction MODIFY COLUMN android_id varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL NULL;
|
||
|
|
ALTER TABLE vn.deviceProduction ADD CONSTRAINT deviceProduction_UN UNIQUE KEY (android_id);
|
||
|
|
ALTER TABLE vn.deviceProduction ADD CONSTRAINT deviceProduction_CHECK CHECK (android_id REGEXP '[A-Za-z0-9]{16,}' OR android_id IS NULL);
|