MyISAM类型表更改为InnoDB类型的表

桃扇骨 2024-03-30 14:25 25阅读 0赞

mysql数据库中的MyISAM类型表更改为InnoDB类型的表

改单个表  
改多个表  
修改为InnoDB  
修改为MyISAM  
批量修改mysql数据库字符集  
Mysql修改表、字段、库的字符集

改单个表  
ALTER TABLE TABLENAME ENGINE=InnoDB;  
ALTER TABLE TABLENAME ENGINE=MyISAM;

改多个表  
修改为InnoDB

SELECT CONCAT( 'ALTER TABLE ', TABLE\_NAME, ' ENGINE=InnoDB;' )  
FROM information\_schema.tables  
WHERE table\_schema = 'DBNAME'  
LIMIT 0 , 10000;

修改为MyISAM  
SELECT CONCAT( 'ALTER TABLE ', TABLE\_NAME, ' ENGINE=MyISAM;' )  
FROM information\_schema.tables  
WHERE table\_schema = 'DBNAME'  
LIMIT 0 , 10000;

使用说明：

将以上SQL语句中的 DBNAME 替换成需要修改的数据库名称。

执行SQL，这个时候还没有修改，只是给了一个查询结果。

将查询结果复制，去掉前后的引号，然后执行。

批量修改mysql数据库字符集

ALTER DATABASE DBNAME DEFAULT CHARACTER SET=‘utf8’;

mysql数据库表字段乱码，连接数据库时设置正确的编码即可

mysql\_query(‘set names utf8’);

Mysql修改表、字段、库的字符集

ALTER DATABASE \`zcqipa250\` DEFAULT CHARACTER SET utf8 COLLATE utf8\_general\_ci;

ALTER TABLE \`qipa250\_articles\` ENGINE=INNODB;  
ALTER TABLE \`qipa250\_articles\_text\` ENGINE=INNODB;  
ALTER TABLE \`qipa250\_authors\` ENGINE=INNODB;  
ALTER TABLE \`qipa250\_comments\` ENGINE=INNODB;  
ALTER TABLE \`qipa250\_comments\_description\` ENGINE=INNODB;  
ALTER TABLE \`popular\_searches\` ENGINE=INNODB;  
ALTER TABLE \`un\_products\_to\_wishlists\` ENGINE=INNODB;  
ALTER TABLE \`un\_wishlists\` ENGINE=INNODB;

ALTER TABLE \`qipa250\_articles\` CONVERT TO CHARACTER SET utf8 COLLATE utf8\_general\_ci;  
ALTER TABLE \`qipa250\_articles\_text\` CONVERT TO CHARACTER SET utf8 COLLATE utf8\_general\_ci;  
ALTER TABLE \`qipa250\_authors\` CONVERT TO CHARACTER SET utf8 COLLATE utf8\_general\_ci;  
ALTER TABLE \`qipa250\_comments\` CONVERT TO CHARACTER SET utf8 COLLATE utf8\_general\_ci;  
ALTER TABLE \`qipa250\_comments\_description\` CONVERT TO CHARACTER SET utf8 COLLATE utf8\_general\_ci;  
ALTER TABLE \`popular\_searches\` CONVERT TO CHARACTER SET utf8 COLLATE utf8\_general\_ci;  
ALTER TABLE \`un\_products\_to\_wishlists\` CONVERT TO CHARACTER SET utf8 COLLATE utf8\_general\_ci;  
ALTER TABLE \`un\_wishlists\` CONVERT TO CHARACTER SET utf8 COLLATE utf8\_general\_ci;