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?

数据库

MySQL

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

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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