数据库现在是latin1_general_ci,我想将排序规则更改为utf8mb4_general_ci。
在PhpMyAdmin中是否有任何设置来更改数据库,表,列的排序规则?而不是一个一个地改变?
数据库现在是latin1_general_ci,我想将排序规则更改为utf8mb4_general_ci。
在PhpMyAdmin中是否有任何设置来更改数据库,表,列的排序规则?而不是一个一个地改变?
当前回答
我很惊讶地发现,所以我不得不回到这里报告,优秀和维护良好的Interconnect/it SAFE SEARCH and REPLACE ON DATABASE脚本有一些选项可以将表转换为utf8 / unicode,甚至转换为innodb。它是一个脚本,通常用于将数据库驱动的网站(Wordpress, Drupal, Joomla等)从一个域迁移到另一个域。
https://github.com/interconnectit/Search-Replace-DB https://interconnectit.com/products/search-and-replace-for-wordpress-databases/
其他回答
如果你运行phpMyAdmin >>选择数据库>>选择表>>到“操作”选项卡>>在“表选项”部分>>,你可以选择排序从下拉列表>>,一旦你按{go}在屏幕的顶部,你会看到一条消息:
您的SQL查询已成功执行
还有一个脚本
ALTER TABLE `tableName` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
但是它不会改变现有列的排序规则。 为此,您可以使用这个脚本(这个脚本也来自phpMyAdmin)
ALTER TABLE `tableName` CHANGE `Name` `Name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
生成查询以更新每个表和每个表的列。 我以前在我的一些项目中使用过这种方法,并且能够解决我的大部分COLLATION问题。(特别是在join上)
要使用,只需将结果导出为带分隔符的文本(可能是新行'\n')
每个表
SELECT CONCAT('ALTER TABLE `', TABLE_NAME,
'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
AS 'USE `DATABASE_NAME`;'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DATABASE_NAME'
AND TABLE_TYPE LIKE 'BASE TABLE'
每一列
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME,'` ',
DATA_TYPE, IF(CHARACTER_MAXIMUM_LENGTH IS NULL
OR DATA_TYPE LIKE 'longtext', '', CONCAT('(', CHARACTER_MAXIMUM_LENGTH,
')')
), ' COLLATE utf8mb4_unicode_ci;') AS 'USE `DATABASE_NAME`;'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'DATABASE_NAME'
AND (SELECT INFORMATION_SCHEMA.TABLES.TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA =
INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA
AND INFORMATION_SCHEMA.TABLES.TABLE_NAME =
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
LIMIT 1) LIKE 'BASE TABLE'
AND DATA_TYPE IN ( 'char', 'varchar' ) /* include other types if necessary */
我很惊讶地发现,所以我不得不回到这里报告,优秀和维护良好的Interconnect/it SAFE SEARCH and REPLACE ON DATABASE脚本有一些选项可以将表转换为utf8 / unicode,甚至转换为innodb。它是一个脚本,通常用于将数据库驱动的网站(Wordpress, Drupal, Joomla等)从一个域迁移到另一个域。
https://github.com/interconnectit/Search-Replace-DB https://interconnectit.com/products/search-and-replace-for-wordpress-databases/
你需要单独转换每个表:
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4
(这同样可以转换列),或者用latin1导出数据库,然后用utf8mb4导入。
我在这里读到,你需要手动转换每个表,这是不正确的。下面是一个如何使用存储过程的解决方案:
DELIMITER $$
DROP PROCEDURE IF EXISTS changeCollation$$
-- character_set parameter could be 'utf8'
-- or 'latin1' or any other valid character set
CREATE PROCEDURE changeCollation(IN character_set VARCHAR(255))
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_table_name varchar(255) DEFAULT "";
DECLARE v_message varchar(4000) DEFAULT "No records";
-- This will create a cursor that selects each table,
-- where the character set is not the one
-- that is defined in the parameter
DECLARE alter_cursor CURSOR FOR SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
AND COLLATION_NAME NOT LIKE CONCAT(character_set, '_%');
-- This handler will set the value v_finished to 1
-- if there are no more rows
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
OPEN alter_cursor;
-- Start a loop to fetch each rows from the cursor
get_table: LOOP
-- Fetch the table names one by one
FETCH alter_cursor INTO v_table_name;
-- If there is no more record, then we have to skip
-- the commands inside the loop
IF v_finished = 1 THEN
LEAVE get_table;
END IF;
IF v_table_name != '' THEN
IF v_message = 'No records' THEN
SET v_message = '';
END IF;
-- This technic makes the trick, it prepares a statement
-- that is based on the v_table_name parameter and it means
-- that this one is different by each iteration inside the loop
SET @s = CONCAT('ALTER TABLE ',v_table_name,
' CONVERT TO CHARACTER SET ', character_set);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET v_message = CONCAT('The table ', v_table_name ,
' was changed to the default collation of ', character_set,
'.\n', v_message);
SET v_table_name = '';
END IF;
-- Close the loop and the cursor
END LOOP get_table;
CLOSE alter_cursor;
-- Returns information about the altered tables or 'No records'
SELECT v_message;
END $$
DELIMITER ;
创建过程后,可以简单地调用它:
CALL changeCollation('utf8');
更多细节请阅读这篇博客。