MySQL主键id丢失解决方案

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

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_%'