blocks|key|460742|text|ALTER+TABLE+MySQL命令应该可以做到这一点。下面的命令将表的默认字符集及其所有列的字符集更改为UTF8。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|460743|ALTER+TABLE+etape_prospection+CONVERT+TO+CHARACTER+SET+utf8+COLLATE+utf8_general_ci;|code-block|syntax|javascript|460744|此命令将表中所有类似文本的列转换为新字符集。字符集每个字符使用不同数量的数据，因此MySQL将转换某些列的类型，以确保有足够的空间来容纳与旧列类型相同数量的字符。|460745|我建议您在修改任何实时数据之前阅读ALTER+TABLE+MySQL+documentation。|460746|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/alter-table.html^0|0|B|0|0|0|H|V|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|11|8|@]|D|@$9|12|A|13|1|14]]|E|$]]|$1|O|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

The <code>ALTER TABLE</code> MySQL 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.

<pre><code>ALTER TABLE etape_prospection CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
</code></pre>

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.

I recommend you read the <a href="http://dev.mysql.com/doc/refman/5.1/en/alter-table.html" rel="noreferrer">ALTER TABLE MySQL documentation</a> before modifying any live data.

blocks|key|460829|text|更改表的默认字符集：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|460830|ALTER+TABLE+etape_prospection
++CHARACTER+SET+utf8,
++COLLATE+utf8_general_ci;|code-block|syntax|javascript|460831|要更改字符串列字符集，请执行以下查询：|460832|ALTER+TABLE+etape_prospection
++CHANGE+COLUMN+etape_prosp_comment+etape_prosp_comment+TEXT+CHARACTER+SET+utf8+COLLATE+utf8_general_ci;|460833|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Change table's default charset:

<pre><code>ALTER TABLE etape_prospection
 CHARACTER SET utf8,
 COLLATE utf8_general_ci;
</code></pre>

To change string column charset exceute this query:

<pre><code>ALTER TABLE etape_prospection
 CHANGE COLUMN etape_prosp_comment etape_prosp_comment TEXT CHARACTER SET utf8 COLLATE utf8_general_ci;
</code></pre>

blocks|key|460899|text|您可以使用alter+table+set+default+charset更改缺省值，但这不会更改现有列的字符集。要更改这一点，您需要使用alter+table+modify+column。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|460900|更改列的字符集只意味着它将能够存储更大范围的字符。您的应用程序使用mysql客户端与数据库通信，因此您可能还需要更改客户端编码。|460901|entityMap^0|5|V|1W|P|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

You can change the default with an <code>alter table set default charset</code> but that won't change the charset of the existing columns. To change that you need to use a <code>alter table modify column</code>. 

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.

blocks|key|676169|text|如果要将表default+character+set和所有字符列更改为新字符集，请使用如下语句：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|676170|ALTER+TABLE+tbl_name+CONVERT+TO+CHARACTER+SET+charset_name;|code-block|syntax|javascript|676171|因此，查询将是：|676172|ALTER+TABLE+etape_prospection+CONVERT+TO+CHARACTER+SET+utf8;|676173|entityMap^0|5|L|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

If you want to change the table <code>default character set</code> and all character columns to a new character set, use a statement like this: 

<pre><code>ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
</code></pre>

So query will be:

<pre><code>ALTER TABLE etape_prospection CONVERT TO CHARACTER SET utf8;
</code></pre>

blocks|key|676274|text|如果有人正在寻找一个完整的解决方案来更改所有数据库表的默认字符集并转换数据，这可能是一个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|676275|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+;|code-block|syntax|javascript|676276|您可以将此代码放在文件中，例如chg_char_set.sql，并通过从MySQL终端调用它来执行它：|offset|length|style|CODE|676277|SOURCE+~/path-to-the-file/chg_char_set.sql|676278|然后使用所需的输入参数调用定义的过程，例如|676279|CALL+change_character_set('utf8mb4',+'utf8mb4_bin');|676280|一旦测试了结果，就可以删除这些存储过程：|676281|DROP+PROCEDURE+`change_character_set`;
DROP+PROCEDURE+`exec_query`;|676282|entityMap^0|0|0|F|G|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@$I|11|J|12|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

If someone is searching for a complete solution for changing default charset for all database tables and converting the data, this could be one:


<pre class="lang-sql prettyprint-override"><code>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 ;
</code></pre>

You can place this code inside the file e.g. <code>chg_char_set.sql</code> and execute it e.g. by calling it from MySQL terminal:

<pre class="lang-sql prettyprint-override"><code>SOURCE ~/path-to-the-file/chg_char_set.sql
</code></pre>

Then call defined procedure with desired input parameters e.g.

<pre class="lang-sql prettyprint-override"><code>CALL change_character_set('utf8mb4', 'utf8mb4_bin');
</code></pre>

Once you've tested the results, you can drop those stored procedures:

<pre class="lang-sql prettyprint-override"><code>DROP PROCEDURE `change_character_set`;
DROP PROCEDURE `exec_query`;
</code></pre>

There is a MySQL <code>table</code> which has this definition taken from <code>SQLYog Enterprise</code> :

<pre><code>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 
</code></pre>

I want to change the <code>default charset</code> of this table from <code>latin1</code> to <code>utf8</code>. How to do that ?

How to change the default charset of a MySQL table?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

有一个MySQL table，它的定义取自SQLYog Enterprise：Table              Create Table                                             -----------------  --------------------------...

问如何更改MySQL表的默认字符集？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何更改MySQL表的默认字符集？EN