mysql 修改当前数据库所有表的所有字段为utf8mb4_general_ci

你可以使用以下命令来修改当前数据库所有表的所有字段为utf8mb4_general_ci：

ALTER DATABASE `database_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS=0;

SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS sql_statements
FROM information_schema.tables
WHERE table_schema = 'database_name'
AND engine IS NOT NULL
INTO OUTFILE '/tmp/alter_tables.sql';

SET FOREIGN_KEY_CHECKS=1;

source /tmp/alter_tables.sql;

这个命令将会为当前数据库中的所有表的所有字段修改字符集为utf8mb4_general_ci。请注意将上述命令中的`database_name`替换为您的数据库名称。

相关问题

mysql 修改当前数据库所有表的所有字段为utf8mb4

可以使用以下 SQL 语句修改当前数据库所有表的所有字段为 utf8mb4：

ALTER DATABASE `数据库名` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS=0;

SELECT concat('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') as sql_statements
FROM information_schema.tables
WHERE table_schema='数据库名' AND table_type='BASE TABLE';

SET FOREIGN_KEY_CHECKS=1;

将上述 SQL 语句中的 `数据库名` 替换为你要操作的数据库名，然后在 MySQL 控制台中执行即可。这段 SQL 语句会先修改当前数据库的字符集和校对规则为 utf8mb4，然后生成一系列 SQL 语句用于修改每个表的字符集和校对规则为 utf8mb4，最后执行这些 SQL 语句完成修改。注意，这个操作可能会比较耗时，因此请在数据量较小的时候执行。

mysql数据库aaa下的所有表和所有字段使用utf8mb4_general_ci 字符集,直接给出答案

Here's the SQL code to change the character set and collation for all tables in a database named "aaa" in MySQL:

ALTER DATABASE aaa CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

This will change the character set and collation of the database as a whole. To change the character set and collation for all tables and columns in the database, you can use the following code:

SET FOREIGN_KEY_CHECKS=0;

SELECT concat('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.tables
WHERE table_schema = 'aaa' AND table_collation != 'utf8mb4_general_ci';

SET FOREIGN_KEY_CHECKS=1;

This code will select all tables in the "aaa" database that have a collation different from `utf8mb4_General_ci` and generate an `ALTER TABLE` statement for each of them. The `SET FOREIGN_KEY_CHECKS=0` statement disables foreign key checks, allowing the collation change to occur, while `SET FOREIGN_KEY_CHECKS=1` re-enables foreign key checks.

Note: Before running the above code, make sure to backup your data.