如何更改 MySQL 表的默认字符集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8906813/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to change the default charset of a MySQL table?
提问by pheromix
There is a MySQL tablewhich has this definition taken from SQLYog Enterprise:
有一个 MySQL 的table定义取自SQLYog Enterprise：
Table Create Table
----------------- ---------------------------------------------------------
etape_prospection CREATE TABLE `etape_prospection` (
`etape_prosp_id` int(10) NOT NULL AUTO_INCREMENT,
`type_prosp_id` int(10) NOT NULL DEFAULT '0',
`prosp_id` int(10) NOT NULL DEFAULT '0',
`etape_prosp_date` datetime DEFAULT NULL,
`etape_prosp_comment` text,
PRIMARY KEY (`etape_prosp_id`),
KEY `concerne_fk` (`prosp_id`),
KEY `de_type_fk` (`type_prosp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
I want to change the default charsetof this table from latin1to utf8. How to do that ?
我想default charset将此表的更改latin1为utf8。怎么做 ？
回答by
If you want to change the table default character setand all character columns to a new character set, use a statement like this:
如果要将表default character set和所有字符列更改为新字符集，请使用如下语句：
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
So query will be:
所以查询将是：
ALTER TABLE etape_prospection CONVERT TO CHARACTER SET utf8;
回答by Devart
Change table's default charset:
更改表的默认字符集：
ALTER TABLE etape_prospection
CHARACTER SET utf8,
COLLATE utf8_general_ci;
To change string column charset exceute this query:
要更改字符串列字符集，请执行此查询：
ALTER TABLE etape_prospection
CHANGE COLUMN etape_prosp_comment etape_prosp_comment TEXT CHARACTER SET utf8 COLLATE utf8_general_ci;
回答by piersadrian
The ALTER TABLEMySQL command should do the trick. The following command will change the default character set of your table and the character set of all its columns to UTF8.
在ALTER TABLEMySQL命令应该做的伎俩。以下命令会将表的默认字符集及其所有列的字符集更改为 UTF8。
ALTER TABLE etape_prospection CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
This command will convert all text-like columns in the table to the new character set. Character sets use different amounts of data per character, so MySQL will convert the type of some columns to ensure there's enough room to fit the same number of characters as the old column type.
此命令将表中所有类似文本的列转换为新字符集。字符集每个字符使用不同数量的数据，因此 MySQL 将转换某些列的类型以确保有足够的空间来容纳与旧列类型相同数量的字符。
I recommend you read the ALTER TABLE MySQL documentationbefore modifying any live data.
我建议您在修改任何实时数据之前阅读ALTER TABLE MySQL 文档。
回答by milijan
If someone is searching for a complete solution for changing default charset for all database tables and converting the data, this could be one:
如果有人正在寻找一个完整的解决方案来更改所有数据库表的默认字符集并转换数据，这可能是一个：
DELIMITER $$
CREATE PROCEDURE `exec_query`(IN sql_text VARCHAR(255))
BEGIN
SET @tquery = `sql_text`;
PREPARE `stmt` FROM @tquery;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;
END$$
CREATE PROCEDURE `change_character_set`(IN `charset` VARCHAR(64), IN `collation` VARCHAR(64))
BEGIN
DECLARE `done` BOOLEAN DEFAULT FALSE;
DECLARE `tab_name` VARCHAR(64);
DECLARE `charset_cursor` CURSOR FOR
SELECT `table_name` FROM `information_schema`.`tables`
WHERE `table_schema` = DATABASE() AND `table_type` = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` = TRUE;
SET foreign_key_checks = 0;
OPEN `charset_cursor`;
`change_loop`: LOOP
FETCH `charset_cursor` INTO `tab_name`;
IF `done` THEN
LEAVE `change_loop`;
END IF;
CALL `exec_query`(CONCAT(
'ALTER TABLE `',
tab_name,
'` CONVERT TO CHARACTER SET ',
QUOTE(charset),
' COLLATE ',
QUOTE(collation),
';'
));
CALL `exec_query`(CONCAT('REPAIR TABLE `', tab_name, '`;'));
CALL `exec_query`(CONCAT('OPTIMIZE TABLE `', tab_name, '`;'));
END LOOP `change_loop`;
CLOSE `charset_cursor`;
SET foreign_key_checks = 1;
END$$
DELIMITER ;
You can place this code inside the file e.g. chg_char_set.sqland execute it e.g. by calling it from MySQL terminal:
您可以将此代码放置在文件中，例如chg_char_set.sql，通过从 MySQL 终端调用它来执行它：
SOURCE ~/path-to-the-file/chg_char_set.sql
Then call defined procedure with desired input parameters e.g.
然后使用所需的输入参数调用定义的过程，例如
CALL change_character_set('utf8mb4', 'utf8mb4_bin');
Once you've tested the results, you can drop those stored procedures:
测试结果后，您可以删除这些存储过程：
DROP PROCEDURE `change_character_set`;
DROP PROCEDURE `exec_query`;
回答by Joni
You can change the default with an alter table set default charsetbut that won't change the charset of the existing columns. To change that you need to use a alter table modify column.
您可以使用 更改默认值，alter table set default charset但这不会更改现有列的字符集。要更改它，您需要使用alter table modify column.
Changing the charset of a column only means that it will be able to store a wider range of characters. Your application talks to the db using the mysql client so you may need to change the client encoding as well.
更改列的字符集仅意味着它将能够存储更广泛的字符。您的应用程序使用 mysql 客户端与数据库对话，因此您可能还需要更改客户端编码。
