MySQL主键id丢失解决方案

木一番

已于 2023-06-03 11:28:12 修改

阅读量650

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 数据库

于 2023-06-02 10:59:26 首次发布

本文链接：https://blog.youkuaiyun.com/yanzhenjingfan/article/details/131002780

在MySQL中，当正式环境的数据因误操作导致主键丢失，且手动修改工作量较大时，可以使用SQL语句批量修改。文章提供了一种解决方案，包括删除主键并重新设置为自动增量，以及恢复列的默认值。适用于有备份的空表且需要恢复主键和默认值的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

适用场景：有备份的空表，现在某个正式环境由于误操作，数据存在，但是主键部分丢失，手动修改工作量大，可使用此方案。

SELECT
 concat('ALTER TABLE ', table_name, ' MODIFY COLUMN ', column_name, ' ', column_type,' NOT NULL FIRST,DROP PRIMARY KEY', ';' ) drop_pri_seq,
  concat( 'ALTER TABLE ', table_name, ' MODIFY COLUMN ', column_name, ' ', column_type, ' NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (', column_name, ');' ) sql_alter_table_seq 
FROM
    (
    SELECT
        t1.table_name,
        t1.auto_increment,
        t2.column_name,
				t2.column_type
    FROM
        (
        SELECT
            auto_increment,
            table_name 
        FROM
            information_schema.TABLES 
        WHERE
            table_name IN ( SELECT table_name FROM information_schema.`COLUMNS` c WHERE COLUMN_KEY = 'pri' AND EXTRA = 'auto_increment' ) 
            AND table_schema = 'sioc_3_3' 
        ) t1
    LEFT JOIN ( SELECT table_schema, table_name, column_name, column_type FROM information_schema.`COLUMNS` c WHERE COLUMN_KEY = 'pri' AND EXTRA = 'auto_increment' AND table_schema = 'sioc_3_3' ) t2 ON t1.table_name = t2.table_name 
    ) tt

table_schema ：更换你自己的数据库

复制出组装拼接好的sql，运行一遍即可

默认值丢失

SELECT
    concat(
        'alter table ',
        TABLE_NAME,
        ' MODIFY COLUMN ',
        COLUMN_NAME,
				' ',
				column_type,
        ' default ',
        ( CASE WHEN LOCATE( 'varchar', COLUMN_TYPE ) = 1 THEN concat( '\'', COLUMN_DEFAULT, '\'' ) ELSE COLUMN_DEFAULT END ),
				( CASE WHEN is_nullable = 'yes' THEN concat(' NOT NULL ') else concat(' ')  END ),
				( CASE WHEN column_comment != '' THEN concat( ' COMMENT ', concat( '\'', column_comment, '\';' ) ) else concat(';')  END )       
) sql_alter_default 
FROM
    information_schema.`COLUMNS` c where    
		1 = 1 
    AND TABLE_SCHEMA = 'sioc_3_3' 
    AND COLUMN_DEFAULT IS NOT NULL 
    AND TABLE_NAME NOT LIKE 'bk_%'