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

查看库的字符集

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