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.
58 lines
2.1 KiB
58 lines
2.1 KiB
3 weeks ago
|
-- ============================================
|
||
|
|
-- 修复主键类型问题
|
||
|
|
-- 将 double 类型主键改为 bigint
|
||
|
|
-- ============================================
|
||
|
|
|
||
|
|
-- 注意：执行前必须先备份数据！
|
||
|
|
|
||
|
|
-- 1. 备份现有数据
|
||
|
|
CREATE TABLE account_backup AS SELECT * FROM account;
|
||
|
|
CREATE TABLE book_backup AS SELECT * FROM book;
|
||
|
|
CREATE TABLE stocks_backup AS SELECT * FROM stocks;
|
||
|
|
CREATE TABLE trends_backup AS SELECT * FROM trends;
|
||
|
|
|
||
|
|
-- 2. 创建新表（正确的主键类型）
|
||
|
|
CREATE TABLE account_new (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
user_id BIGINT NOT NULL,
|
||
|
|
account_name VARCHAR(100) NOT NULL,
|
||
|
|
account_type VARCHAR(50),
|
||
|
|
status CHAR(1) DEFAULT '0',
|
||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
INDEX idx_user_id (user_id),
|
||
|
|
INDEX idx_status (status)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE book_new (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
user_id BIGINT NOT NULL,
|
||
|
|
account_id BIGINT NOT NULL,
|
||
|
|
book_name VARCHAR(100) NOT NULL,
|
||
|
|
book_type VARCHAR(50),
|
||
|
|
status CHAR(1) DEFAULT '0',
|
||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
INDEX idx_user_id (user_id),
|
||
|
|
INDEX idx_account_id (account_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 3. 迁移数据
|
||
|
|
INSERT INTO account_new (id, user_id, account_name, account_type, status, create_time, update_time)
|
||
|
|
SELECT CAST(id AS SIGNED), user_id, account_name, account_type, status, create_time, update_time
|
||
|
|
FROM account;
|
||
|
|
|
||
|
|
INSERT INTO book_new (id, user_id, account_id, book_name, book_type, status, create_time, update_time)
|
||
|
|
SELECT CAST(id AS SIGNED), user_id, account_id, book_name, book_type, status, create_time, update_time
|
||
|
|
FROM book;
|
||
|
|
|
||
|
|
-- 4. 验证数据完整性
|
||
|
|
SELECT COUNT(*) AS old_count FROM account;
|
||
|
|
SELECT COUNT(*) AS new_count FROM account_new;
|
||
|
|
SELECT COUNT(*) AS old_count FROM book;
|
||
|
|
SELECT COUNT(*) AS new_count FROM book_new;
|
||
|
|
|
||
|
|
-- 5. 切换表名（谨慎执行！）
|
||
|
|
-- RENAME TABLE account TO account_old, account_new TO account;
|
||
|
|
-- RENAME TABLE book TO book_old, book_new TO book;
|