MySQL 查看修改字符集

查看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 (默认的内部操作字符集)

https://www.cnblogs.com/shihaiming/p/5855616.html

查看库的字符集

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