檢視MYSQL資料庫伺服器和資料庫字元集
方法一：show variables like '%character%';
方法二：show variables like 'collation%';
show charset;
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
character_set_client(用戶端來源資料使用的字元集)
character_set_connection(連接配接層字元集)
character_set_database(目前選中資料庫的預設字元集)
character_set_results(查詢結果字元集)
character_set_server(預設的内部操作字元集)
檢視庫的字元集
show create database 表名;
show database status from 庫名 like 表名;
檢視表的字元集
show table status from 庫名 like 表名;
show table status from p2p_test like '%
'
show create table 表名
檢視表中所有列的字元集
show full columns from test_info;
修改全局字元集
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;
https://www.cnblogs.com/xingzc/p/6039350.html
修改庫的字元集
alter database db_name default character set gbk;
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
ALTER DATABASE caitu99 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
把表預設的字元集和所有字元列(CHAR,VARCHAR,TEXT)改為新的字元集：
alter table 表名 convert to character set 字元集;
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
隻是修改表的預設字元集
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
修改字段的字元集
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
alter table test1 modify name char(10) character set gbk;
ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
同時修改某一個資料庫中所有表的所有字段的編碼格式
mysql中的begin-end流程控制語句與局部變量
begin-end、流程控制語句、局部變量隻能用于函數、存儲過程内部、遊标、觸發器的定義内部。
https://blog.csdn.net/baoliangsheng/article/details/46459471
begin
DECLARE cnt VARCHAR(100); -- 聲明變量用來記錄查詢出的表名
DECLARE i int; -- 循環條件，同時可以用來标記表第幾張表
set i = 0;
-- 循環開始
while i < 32 do -- 這裡是32是因為我的資料庫中表的數量是32，想不寫死可以通過再定義一個變量，動态指派
select table_name into @cnt from information_schema.`TABLES` where TABLE_SCHEMA = '資料庫名' limit i,1;
-- select @cnt; -- mysql的列印語句
-- alter table @cnt convert to character set utf8; -- 這一句報錯，必須動态拼接才行
set @sql = concat("alter table ", @cnt, " convert to character set utf8"); -- 拼接，注意語句中的空格
prepare stmt from @sql; -- 預處理
execute stmt; -- 執行
deallocate prepare stmt; -- 釋放
set i = i + 1;
end while;
-- 循環結束，注意分号
end
https://blog.csdn.net/LUNG108/article/details/78285054
查找表資訊
DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_status` $$
CREATE PROCEDURE `sp_status`(dbname VARCHAR(50))
BEGIN
-- Obtaining tables and views
(
SELECT
TABLE_NAME AS `Table Name`,
ENGINE AS `Engine`,
TABLE_ROWS AS `Rows`,
CONCAT(
(FORMAT((DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2),2))
, ' Mb')
AS `Size`,
TABLE_COLLATION AS `Collation`
FROM information_schema.TABLES
WHERE TABLES.TABLE_SCHEMA = dbname
AND TABLES.TABLE_TYPE = 'BASE TABLE'
)
UNION
(
SELECT
TABLE_NAME AS `Table Name`,
'[VIEW]' AS `Engine`,
'-' AS `Rows`,
'-' `Size`,
'-' AS `Collation`
FROM information_schema.TABLES
WHERE TABLES.TABLE_SCHEMA = dbname
AND TABLES.TABLE_TYPE = 'VIEW'
)
ORDER BY 1;
-- Obtaining functions, procedures and triggers
(
SELECT ROUTINE_NAME AS `Routine Name`,
ROUTINE_TYPE AS `Type`,
'' AS `Comment`
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = dbname
ORDER BY ROUTINES.ROUTINE_TYPE, ROUTINES.ROUTINE_NAME
)
UNION
(
SELECT TRIGGER_NAME,'TRIGGER' AS `Type`,
concat('On ',EVENT_MANIPULATION,': ',EVENT_OBJECT_TABLE) AS `Comment`
FROM information_schema.TRIGGERS
WHERE EVENT_OBJECT_SCHEMA = dbname
)
ORDER BY 2,1;
END$$
DELIMITER ;
CALL sp_status(DATABASE());
https://blog.csdn.net/xhiaa/article/details/7814686
修改腳本
show charset;
set character_set_database=utf8mb4;
set collation_database=utf8mb4_general_ci;
ALTER DATABASE caitu99 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
DELIMITER //
DROP PROCEDURE IF EXISTS `proc_reset_table_character` //
create PROCEDURE proc_reset_table_character ()
BEGIN
DECLARE cnt VARCHAR(100); -- 聲明變量用來記錄查詢出的表名
DECLARE i int; -- 循環條件，同時可以用來标記表第幾張表
DECLARE t_cnt int;
set i = 0;
set @allsql='';
set t_cnt = 0;
select count(1) into t_cnt from information_schema.`TABLES` where TABLE_SCHEMA = 'db_test' and TABLE_TYPE = 'BASE TABLE'; -- and TABLE_COLLATION <> 'utf8_general_ci'
-- 循環開始
while i < t_cnt do -- 這裡是32是因為我的資料庫中表的數量是32，想不寫死可以通過再定義一個變量，動态指派
select table_name into @cnt from information_schema.`TABLES` where TABLE_SCHEMA = 'db_test' and TABLE_TYPE = 'BASE TABLE' limit i,1;
-- select @cnt; -- mysql的列印語句
-- alter table @cnt convert to character set utf8; -- 這一句報錯，必須動态拼接才行
set @sql = concat("alter table ", @cnt, " convert to character set utf8 COLLATE utf8_general_ci;"); -- 拼接，注意語句中的空格
prepare stmt from @sql; -- 預處理
execute stmt; -- 執行
deallocate prepare stmt; -- 釋放
set @allsql =concat(@allsql,CHAR(10),@sql);
set i = i + 1;
end while;
SELECT @allsql;
END
//
DELIMITER ;
-- SET @p_inout=1;
-- CALL inout_param(@p_inout) ;
-- SELECT @p_inout;
call proc_reset_table_character();
索引 限制
ALTER TABLE t_users DROP INDEX id_number;
ALTER TABLE `t_users` ADD INDEX id_number ( `id_number` ) ;
https://www.cnblogs.com/a-du/p/7117837.html
https://blog.csdn.net/u013967628/article/details/76559368