文档章节

mysql字符集以及乱码实战02

泡海椒
 泡海椒
发布于 2016/01/03 15:55
字数 2909
阅读 12
收藏 0

mysql数据库编码问题五种方法
mysql> show create database tt;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| tt       | CREATE DATABASE `tt` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use tt;
Database changed
create table student(
     id int(4) not null,
     name char(40) not null,
   age tinyint(2) not null default '0',
    dept varchar(16) default null
  );
 

  mysql> create table student(
    ->      id int(4) not null,
    ->      name char(40) not null,
    ->    age tinyint(2) not null default '0',
    ->     dept varchar(16) default null
    ->   );
Query OK, 0 rows affected (0.10 sec)

mysql> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                   |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(4) NOT NULL,
  `name` char(40) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 insert into student(id,name,dept) values (1,'hello','你好');
 mysql> insert into student(id,name,dept) values (1,'hello','你好');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from student;
+----+-------+-----+------+
| id | name  | age | dept |
+----+-------+-----+------+
|  1 | hello |   0 | EC   |
|  1 | hello |   0 | ??   |
+----+-------+-----+------+
2 rows in set (0.00 sec)
mysql> select * from student;
+----+-------+-----+------+
| id | name  | age | dept |
+----+-------+-----+------+
|  1 | hello |   0 | EC   |
|  1 | hello |   0 | ??   |
+----+-------+-----+------+
2 rows in set (0.00 sec)

mysql> show create table student;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    13
Current database: tt

+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                   |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(4) NOT NULL,
  `name` char(40) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> set names latin1;--》设置mysql客户端和建表的字符集Latin1一致
Query OK, 0 rows affected (0.01 sec)(库表都是latin临时的)
继续插入一条中文语句

mysql> insert into student(id,name,dept) values (1,'hello','你好');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+-------+-----+--------+
| id | name  | age | dept   |
+----+-------+-----+--------+
|  1 | hello |   0 | EC     |
|  1 | hello |   0 | ??     |
|  1 | hello |   0 | 你好   |
+----+-------+-----+--------+
3 rows in set (0.00 sec)
新开一个terminnal窗口
vi test.sql
set names latin1;
insert into student(id,name,dept) values (1,'hello','你好');
退出当前mysql
mysql》quit;
[root@localhost  data]# mysql  -S /data/3308/mysql.sock
mysql> system ls /root/test.sql
/root/test.sql
mysql> source /root/test.sql;第二种
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.07 sec)
mysql> select * from student;
+----+-------+-----+--------+
| id | name  | age | dept   |
+----+-------+-----+--------+
|  1 | hello |   0 | EC     |
|  1 | hello |   0 | ??     |
|  1 | hello |   0 | 你好   |
|  1 | hello |   0 | 你好   |
+----+-------+-----+--------+
4 rows in set (0.00 sec)
 小结dql,dml语句之前set names 系统及其库表的字符集
 第三通过mysql命令指定字符集参数指定latin1 字符集导入test.sql文件方式
  mysql  -S /data/3308/mysql.sock tt </root/test.sql
  第四种
  mysql  -S /data/3308/mysql.sock  --default-character-set=latin1 tt</root/test.sql
  mysql  -S /data/3308/mysql.sock -e "set names latin1;select * from tt.student;"
  +----+-------+-----+--------+
| id | name  | age | dept   |
+----+-------+-----+--------+
|  1 | hello |   0 | EC     |
|  1 | hello |   0 | ??     |
|  1 | hello |   0 | 你好   |
|  1 | hello |   0 | 你好   |
|  1 | hello |   0 | 你好   |
+----+-------+-----+--------+
vi test.sql
insert into student(id,name,dept) values (1,'hello','你好1');
第五种在客户端指定字符集永久有效
修改my.cnf
[client]
default-character-set=latin1(5.1及其以前版本)
character-set-server=latin1适合5.5
不乱吗的思想保证客户端和服务端 ,库表程序不乱码
[root@localhost  data]# cat /etc/sysconfig/i18n 系统的字符集
#LANG="en_US.UTF-8"
LANG="zh_CN.UTF-8"
中英文环境最好用utf8
库表
create datanbase * default character set utf8 collation utf8-general_ci
show characters;
建表
 CREATE TABLE `student` (
  `id` int(4) NOT NULL,
  `name` char(40) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1          
mysql> show variables;查看变量 比my.cnf 的多查看变量是否生效
mysql> show status;
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 1           |
| Aborted_connects                         | 1           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 5           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 136         |
| Bytes_sent                               | 10085       |
| Com_admin_commands                       | 0           |
| Com_assign_to_keycache                   | 0           |
| Com_alter_db                             | 0           |
| Com_alter_db_upgrade                     | 0           |
| Com_alter_event                          | 0           |
| Com_alter_function                       | 0           |
| Com_alter_procedure                      | 0           |
| Com_alter_server                         | 0           |
| Com_alter_table                          | 0           |
| Com_alter_tablespace                     | 0           |
| Com_analyze                              | 0           |
| Com_begin                                | 0           |
| Com_binlog                               | 0           |
| Com_call_procedure                       | 0           |
| Com_change_db                            | 0           |
| Com_change_master                        | 0           |
| Com_check                                | 0           |
| Com_checksum                             | 0           |
| Com_commit                               | 0           |
| Com_create_db                            | 0           |
| Com_create_event                         | 0           |
| Com_create_function                      | 0           |
| Com_create_index                         | 0           |
| Com_create_procedure                     | 0           |
| Com_create_server                        | 0           |
| Com_create_table                         | 0           |
| Com_create_trigger                       | 0           |
| Com_create_udf                           | 0           |
| Com_create_user                          | 0           |
| Com_create_view                          | 0           |
| Com_dealloc_sql                          | 0           |
| Com_delete                               | 0           |
| Com_delete_multi                         | 0           |
| Com_do                                   | 0           |
| Com_drop_db                              | 0           |
| Com_drop_event                           | 0           |
| Com_drop_function                        | 0           |
| Com_drop_index                           | 0           |
| Com_drop_procedure                       | 0           |
| Com_drop_server                          | 0           |
| Com_drop_table                           | 0           |
| Com_drop_trigger                         | 0           |
| Com_drop_user                            | 0           |
| Com_drop_view                            | 0           |
| Com_empty_query                          | 0           |
| Com_execute_sql                          | 0           |
| Com_flush                                | 0           |
| Com_grant                                | 0           |
| Com_ha_close                             | 0           |
| Com_ha_open                              | 0           |
| Com_ha_read                              | 0           |
| Com_help                                 | 0           |
| Com_insert                               | 0           |
| Com_insert_select                        | 0           |
| Com_install_plugin                       | 0           |
| Com_kill                                 | 0           |
| Com_load                                 | 0           |
| Com_lock_tables                          | 0           |
| Com_optimize                             | 0           |
| Com_preload_keys                         | 0           |
| Com_prepare_sql                          | 0           |
| Com_purge                                | 0           |
| Com_purge_before_date                    | 0           |
| Com_release_savepoint                    | 0           |
| Com_rename_table                         | 0           |
| Com_rename_user                          | 0           |
| Com_repair                               | 0           |
| Com_replace                              | 0           |
| Com_replace_select                       | 0           |
| Com_reset                                | 0           |
| Com_resignal                             | 0           |
| Com_revoke                               | 0           |
| Com_revoke_all                           | 0           |
| Com_rollback                             | 0           |
| Com_rollback_to_savepoint                | 0           |
| Com_savepoint                            | 0           |
| Com_select                               | 1           |
| Com_set_option                           | 0           |
| Com_signal                               | 0           |
| Com_show_authors                         | 0           |
| Com_show_binlog_events                   | 0           |
| Com_show_binlogs                         | 0           |
| Com_show_charsets                        | 0           |
| Com_show_collations                      | 0           |
| Com_show_contributors                    | 0           |
| Com_show_create_db                       | 0           |
| Com_show_create_event                    | 0           |
| Com_show_create_func                     | 0           |
| Com_show_create_proc                     | 0           |
| Com_show_create_table                    | 0           |
| Com_show_create_trigger                  | 0           |
| Com_show_databases                       | 0           |
| Com_show_engine_logs                     | 0           |
| Com_show_engine_mutex                    | 0           |
| Com_show_engine_status                   | 0           |
| Com_show_events                          | 0           |
| Com_show_errors                          | 0           |
| Com_show_fields                          | 0           |
| Com_show_function_status                 | 0           |
| Com_show_grants                          | 0           |
| Com_show_keys                            | 0           |
| Com_show_master_status                   | 0           |
| Com_show_open_tables                     | 0           |
| Com_show_plugins                         | 0           |
| Com_show_privileges                      | 0           |
| Com_show_procedure_status                | 0           |
| Com_show_processlist                     | 0           |
| Com_show_profile                         | 0           |
| Com_show_profiles                        | 0           |
| Com_show_relaylog_events                 | 0           |
| Com_show_slave_hosts                     | 0           |
| Com_show_slave_status                    | 0           |
| Com_show_status                          | 1           |
| Com_show_storage_engines                 | 0           |
| Com_show_table_status                    | 0           |
| Com_show_tables                          | 0           |
| Com_show_triggers                        | 0           |
| Com_show_variables                       | 1           |
| Com_show_warnings                        | 0           |
| Com_slave_start                          | 0           |
| Com_slave_stop                           | 0           |
| Com_stmt_close                           | 0           |
| Com_stmt_execute                         | 0           |
| Com_stmt_fetch                           | 0           |
| Com_stmt_prepare                         | 0           |
| Com_stmt_reprepare                       | 0           |
| Com_stmt_reset                           | 0           |
| Com_stmt_send_long_data                  | 0           |
| Com_truncate                             | 0           |
| Com_uninstall_plugin                     | 0           |
| Com_unlock_tables                        | 0           |
| Com_update                               | 0           |
| Com_update_multi                         | 0           |
| Com_xa_commit                            | 0           |
| Com_xa_end                               | 0           |
| Com_xa_prepare                           | 0           |
| Com_xa_recover                           | 0           |
| Com_xa_rollback                          | 0           |
| Com_xa_start                             | 0           |
| Compression                              | OFF         |
| Connections                              | 20          |
| Created_tmp_disk_tables                  | 0           |
| Created_tmp_files                        | 7           |
| Created_tmp_tables                       | 1           |
| Delayed_errors                           | 0           |
| Delayed_insert_threads                   | 0           |
| Delayed_writes                           | 0           |
| Flush_commands                           | 1           |
| Handler_commit                           | 0           |
| Handler_delete                           | 0           |
| Handler_discover                         | 0           |
| Handler_prepare                          | 0           |
| Handler_read_first                       | 0           |
| Handler_read_key                         | 0           |
| Handler_read_last                        | 0           |
| Handler_read_next                        | 0           |
| Handler_read_prev                        | 0           |
| Handler_read_rnd                         | 0           |
| Handler_read_rnd_next                    | 330         |
| Handler_rollback                         | 0           |
| Handler_savepoint                        | 0           |
| Handler_savepoint_rollback               | 0           |
| Handler_update                           | 0           |
| Handler_write                            | 329         |
| Innodb_buffer_pool_pages_data            | 332         |
| Innodb_buffer_pool_bytes_data            | 5439488     |
| Innodb_buffer_pool_pages_dirty           | 0           |
| Innodb_buffer_pool_bytes_dirty           | 0           |
| Innodb_buffer_pool_pages_flushed         | 427         |
| Innodb_buffer_pool_pages_free            | 12467       |
| Innodb_buffer_pool_pages_misc            | 1           |
| Innodb_buffer_pool_pages_total           | 12800       |
| Innodb_buffer_pool_read_ahead_rnd        | 0           |
| Innodb_buffer_pool_read_ahead            | 0           |
| Innodb_buffer_pool_read_ahead_evicted    | 0           |
| Innodb_buffer_pool_read_requests         | 3274        |
| Innodb_buffer_pool_reads                 | 0           |
| Innodb_buffer_pool_wait_free             | 0           |
| Innodb_buffer_pool_write_requests        | 2761        |
| Innodb_data_fsyncs                       | 96          |
| Innodb_data_pending_fsyncs               | 0           |
| Innodb_data_pending_reads                | 0           |
| Innodb_data_pending_writes               | 0           |
| Innodb_data_read                         | 0           |
| Innodb_data_reads                        | 1           |
| Innodb_data_writes                       | 1705        |
| Innodb_data_written                      | 12791296    |
| Innodb_dblwr_pages_written               | 254         |
| Innodb_dblwr_writes                      | 15          |
| Innodb_have_atomic_builtins              | ON          |
| Innodb_log_waits                         | 0           |
| Innodb_log_write_requests                | 3232        |
| Innodb_log_writes                        | 23          |
| Innodb_os_log_fsyncs                     | 40          |
| Innodb_os_log_pending_fsyncs             | 0           |
| Innodb_os_log_pending_writes             | 0           |
| Innodb_os_log_written                    | 1624576     |
| Innodb_page_size                         | 16384       |
| Innodb_pages_created                     | 332         |
| Innodb_pages_read                        | 0           |
| Innodb_pages_written                     | 427         |
| Innodb_row_lock_current_waits            | 0           |
| Innodb_row_lock_time                     | 0           |
| Innodb_row_lock_time_avg                 | 0           |
| Innodb_row_lock_time_max                 | 0           |
| Innodb_row_lock_waits                    | 0           |
| Innodb_rows_deleted                      | 0           |
| Innodb_rows_inserted                     | 5           |
| Innodb_rows_read                         | 19          |
| Innodb_rows_updated                      | 0           |
| Innodb_truncated_status_writes           | 0           |
| Key_blocks_not_flushed                   | 0           |
| Key_blocks_unused                        | 6692        |
| Key_blocks_used                          | 6           |
| Key_read_requests                        | 20          |
| Key_reads                                | 6           |
| Key_write_requests                       | 5           |
| Key_writes                               | 5           |
| Last_query_cost                          | 10.499000   |
| Max_used_connections                     | 1           |
| Not_flushed_delayed_rows                 | 0           |
| Open_files                               | 31          |
| Open_streams                             | 0           |
| Open_table_definitions                   | 39          |
| Open_tables                              | 32          |
| Opened_files                             | 179         |
| Opened_table_definitions                 | 0           |
| Opened_tables                            | 0           |
| Performance_schema_cond_classes_lost     | 0           |
| Performance_schema_cond_instances_lost   | 0           |
| Performance_schema_file_classes_lost     | 0           |
| Performance_schema_file_handles_lost     | 0           |
| Performance_schema_file_instances_lost   | 0           |
| Performance_schema_locker_lost           | 0           |
| Performance_schema_mutex_classes_lost    | 0           |
| Performance_schema_mutex_instances_lost  | 0           |
| Performance_schema_rwlock_classes_lost   | 0           |
| Performance_schema_rwlock_instances_lost | 0           |
| Performance_schema_table_handles_lost    | 0           |
| Performance_schema_table_instances_lost  | 0           |
| Performance_schema_thread_classes_lost   | 0           |
| Performance_schema_thread_instances_lost | 0           |
| Prepared_stmt_count                      | 0           |
| Qcache_free_blocks                       | 1           |
| Qcache_free_memory                       | 67088568    |
| Qcache_hits                              | 0           |
| Qcache_inserts                           | 5           |
| Qcache_lowmem_prunes                     | 0           |
| Qcache_not_cached                        | 37          |
| Qcache_queries_in_cache                  | 2           |
| Qcache_total_blocks                      | 6           |
| Queries                                  | 173         |
| Questions                                | 3           |
| Rpl_status                               | AUTH_MASTER |
| Select_full_join                         | 0           |
| Select_full_range_join                   | 0           |
| Select_range                             | 0           |
| Select_range_check                       | 0           |
| Select_scan                              | 1           |
| Slave_heartbeat_period                   | 0.000       |
| Slave_open_temp_tables                   | 0           |
| Slave_received_heartbeats                | 0           |
| Slave_retried_transactions               | 0           |
| Slave_running                            | OFF         |
| Slow_launch_threads                      | 0           |
| Slow_queries                             | 0           |
| Sort_merge_passes                        | 0           |
| Sort_range                               | 0           |
| Sort_rows                                | 0           |
| Sort_scan                                | 0           |
| Table_locks_immediate                    | 87          |
| Table_locks_waited                       | 0           |
| Tc_log_max_pages_used                    | 0           |
| Tc_log_page_size                         | 0           |
| Tc_log_page_waits                        | 0           |
| Threads_cached                           | 0           |
| Threads_connected                        | 1           |
| Threads_created                          | 1           |
| Threads_running                          | 1           |
| Uptime                                   | 229517      |
| Uptime_since_flush_status                | 229517      |
+------------------------------------------+-------------+
289 rows in set (0.06 sec)
mysql> show global status;思考怎么在线更改参数
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 1           |
| Aborted_connects                         | 1           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 5           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 10241       |
| Bytes_sent                               | 51932       |
| Com_admin_commands                       | 0           |
| Com_assign_to_keycache                   | 0           |
| Com_alter_db                             | 0           |
| Com_alter_db_upgrade                     | 0           |
| Com_alter_event                          | 0           |
| Com_alter_function                       | 0           |
| Com_alter_procedure                      | 0           |
| Com_alter_server                         | 0           |
| Com_alter_table                          | 0           |
| Com_alter_tablespace                     | 0           |
| Com_analyze                              | 0           |
| Com_begin                                | 0           |
| Com_binlog                               | 0           |
| Com_call_procedure                       | 0           |
| Com_change_db                            | 13          |
| Com_change_master                        | 0           |
| Com_check                                | 0           |
| Com_checksum                             | 0           |
| Com_commit                               | 0           |
| Com_create_db                            | 3           |
| Com_create_event                         | 0           |
| Com_create_function                      | 0           |
| Com_create_index                         | 1           |
| Com_create_procedure                     | 0           |
| Com_create_server                        | 0           |
| Com_create_table                         | 3           |
| Com_create_trigger                       | 0           |
| Com_create_udf                           | 0           |
| Com_create_user                          | 0           |
| Com_create_view                          | 0           |
| Com_dealloc_sql                          | 0           |
| Com_delete                               | 0           |
| Com_delete_multi                         | 0           |
| Com_do                                   | 0           |
| Com_drop_db                              | 2           |
| Com_drop_event                           | 0           |
| Com_drop_function                        | 0           |
| Com_drop_index                           | 0           |
| Com_drop_procedure                       | 0           |
| Com_drop_server                          | 0           |
| Com_drop_table                           | 1           |
| Com_drop_trigger                         | 0           |
| Com_drop_user                            | 0           |
| Com_drop_view                            | 0           |
| Com_empty_query                          | 0           |
| Com_execute_sql                          | 0           |
| Com_flush                                | 1           |
| Com_grant                                | 2           |
| Com_ha_close                             | 0           |
| Com_ha_open                              | 0           |
| Com_ha_read                              | 0           |
| Com_help                                 | 2           |
| Com_insert                               | 8           |
| Com_insert_select                        | 0           |
| Com_install_plugin                       | 0           |
| Com_kill                                 | 0           |
| Com_load                                 | 0           |
| Com_lock_tables                          | 2           |
| Com_optimize                             | 0           |
| Com_preload_keys                         | 0           |
| Com_prepare_sql                          | 0           |
| Com_purge                                | 0           |
| Com_purge_before_date                    | 0           |
| Com_release_savepoint                    | 0           |
| Com_rename_table                         | 0           |
| Com_rename_user                          | 0           |
| Com_repair                               | 0           |
| Com_replace                              | 0           |
| Com_replace_select                       | 0           |
| Com_reset                                | 0           |
| Com_resignal                             | 0           |
| Com_revoke                               | 3           |
| Com_revoke_all                           | 0           |
| Com_rollback                             | 0           |
| Com_rollback_to_savepoint                | 0           |
| Com_savepoint                            | 0           |
| Com_select                               | 47          |
| Com_set_option                           | 22          |
| Com_signal                               | 0           |
| Com_show_authors                         | 0           |
| Com_show_binlog_events                   | 0           |
| Com_show_binlogs                         | 0           |
| Com_show_charsets                        | 0           |
| Com_show_collations                      | 0           |
| Com_show_contributors                    | 0           |
| Com_show_create_db                       | 4           |
| Com_show_create_event                    | 0           |
| Com_show_create_func                     | 0           |
| Com_show_create_proc                     | 0           |
| Com_show_create_table                    | 5           |
| Com_show_create_trigger                  | 0           |
| Com_show_databases                       | 4           |
| Com_show_engine_logs                     | 0           |
| Com_show_engine_mutex                    | 0           |
| Com_show_engine_status                   | 0           |
| Com_show_events                          | 0           |
| Com_show_errors                          | 0           |
| Com_show_fields                          | 5           |
| Com_show_function_status                 | 0           |
| Com_show_grants                          | 4           |
| Com_show_keys                            | 2           |
| Com_show_master_status                   | 0           |
| Com_show_open_tables                     | 0           |
| Com_show_plugins                         | 0           |
| Com_show_privileges                      | 0           |
| Com_show_procedure_status                | 0           |
| Com_show_processlist                     | 0           |
| Com_show_profile                         | 0           |
| Com_show_profiles                        | 0           |
| Com_show_relaylog_events                 | 0           |
| Com_show_slave_hosts                     | 0           |
| Com_show_slave_status                    | 0           |
| Com_show_status                          | 2           |
| Com_show_storage_engines                 | 0           |
| Com_show_table_status                    | 2           |
| Com_show_tables                          | 5           |
| Com_show_triggers                        | 2           |
| Com_show_variables                       | 1           |
| Com_show_warnings                        | 0           |
| Com_slave_start                          | 0           |
| Com_slave_stop                           | 0           |
| Com_stmt_close                           | 0           |
| Com_stmt_execute                         | 0           |
| Com_stmt_fetch                           | 0           |
| Com_stmt_prepare                         | 0           |
| Com_stmt_reprepare                       | 0           |
| Com_stmt_reset                           | 0           |
| Com_stmt_send_long_data                  | 0           |
| Com_truncate                             | 0           |
| Com_uninstall_plugin                     | 0           |
| Com_unlock_tables                        | 3           |
| Com_update                               | 0           |
| Com_update_multi                         | 0           |
| Com_xa_commit                            | 0           |
| Com_xa_end                               | 0           |
| Com_xa_prepare                           | 0           |
| Com_xa_recover                           | 0           |
| Com_xa_rollback                          | 0           |
| Com_xa_start                             | 0           |
| Compression                              | OFF         |
| Connections                              | 20          |
| Created_tmp_disk_tables                  | 15          |
| Created_tmp_files                        | 7           |
| Created_tmp_tables                       | 51          |
| Delayed_errors                           | 0           |
| Delayed_insert_threads                   | 0           |
| Delayed_writes                           | 0           |
| Flush_commands                           | 1           |
| Handler_commit                           | 21          |
| Handler_delete                           | 0           |
| Handler_discover                         | 0           |
| Handler_prepare                          | 10          |
| Handler_read_first                       | 13          |
| Handler_read_key                         | 21          |
| Handler_read_last                        | 0           |
| Handler_read_next                        | 8           |
| Handler_read_prev                        | 0           |
| Handler_read_rnd                         | 0           |
| Handler_read_rnd_next                    | 777         |
| Handler_rollback                         | 3           |
| Handler_savepoint                        | 0           |
| Handler_savepoint_rollback               | 0           |
| Handler_update                           | 1           |
| Handler_write                            | 674         |
| Innodb_buffer_pool_pages_data            | 332         |
| Innodb_buffer_pool_bytes_data            | 5439488     |
| Innodb_buffer_pool_pages_dirty           | 0           |
| Innodb_buffer_pool_bytes_dirty           | 0           |
| Innodb_buffer_pool_pages_flushed         | 427         |
| Innodb_buffer_pool_pages_free            | 12467       |
| Innodb_buffer_pool_pages_misc            | 1           |
| Innodb_buffer_pool_pages_total           | 12800       |
| Innodb_buffer_pool_read_ahead_rnd        | 0           |
| Innodb_buffer_pool_read_ahead            | 0           |
| Innodb_buffer_pool_read_ahead_evicted    | 0           |
| Innodb_buffer_pool_read_requests         | 3274        |
| Innodb_buffer_pool_reads                 | 0           |
| Innodb_buffer_pool_wait_free             | 0           |
| Innodb_buffer_pool_write_requests        | 2761        |
| Innodb_data_fsyncs                       | 96          |
| Innodb_data_pending_fsyncs               | 0           |
| Innodb_data_pending_reads                | 0           |
| Innodb_data_pending_writes               | 0           |
| Innodb_data_read                         | 0           |
| Innodb_data_reads                        | 1           |
| Innodb_data_writes                       | 1705        |
| Innodb_data_written                      | 12791296    |
| Innodb_dblwr_pages_written               | 254         |
| Innodb_dblwr_writes                      | 15          |
| Innodb_have_atomic_builtins              | ON          |
| Innodb_log_waits                         | 0           |
| Innodb_log_write_requests                | 3232        |
| Innodb_log_writes                        | 23          |
| Innodb_os_log_fsyncs                     | 40          |
| Innodb_os_log_pending_fsyncs             | 0           |
| Innodb_os_log_pending_writes             | 0           |
| Innodb_os_log_written                    | 1624576     |
| Innodb_page_size                         | 16384       |
| Innodb_pages_created                     | 332         |
| Innodb_pages_read                        | 0           |
| Innodb_pages_written                     | 427         |
| Innodb_row_lock_current_waits            | 0           |
| Innodb_row_lock_time                     | 0           |
| Innodb_row_lock_time_avg                 | 0           |
| Innodb_row_lock_time_max                 | 0           |
| Innodb_row_lock_waits                    | 0           |
| Innodb_rows_deleted                      | 0           |
| Innodb_rows_inserted                     | 5           |
| Innodb_rows_read                         | 19          |
| Innodb_rows_updated                      | 0           |
| Innodb_truncated_status_writes           | 0           |
| Key_blocks_not_flushed                   | 0           |
| Key_blocks_unused                        | 6692        |
| Key_blocks_used                          | 6           |
| Key_read_requests                        | 20          |
| Key_reads                                | 6           |
| Key_write_requests                       | 5           |
| Key_writes                               | 5           |
| Last_query_cost                          | 0.000000    |
| Max_used_connections                     | 1           |
| Not_flushed_delayed_rows                 | 0           |
| Open_files                               | 31          |
| Open_streams                             | 0           |
| Open_table_definitions                   | 39          |
| Open_tables                              | 32          |
| Opened_files                             | 179         |
| Opened_table_definitions                 | 45          |
| Opened_tables                            | 46          |
| Performance_schema_cond_classes_lost     | 0           |
| Performance_schema_cond_instances_lost   | 0           |
| Performance_schema_file_classes_lost     | 0           |
| Performance_schema_file_handles_lost     | 0           |
| Performance_schema_file_instances_lost   | 0           |
| Performance_schema_locker_lost           | 0           |
| Performance_schema_mutex_classes_lost    | 0           |
| Performance_schema_mutex_instances_lost  | 0           |
| Performance_schema_rwlock_classes_lost   | 0           |
| Performance_schema_rwlock_instances_lost | 0           |
| Performance_schema_table_handles_lost    | 0           |
| Performance_schema_table_instances_lost  | 0           |
| Performance_schema_thread_classes_lost   | 0           |
| Performance_schema_thread_instances_lost | 0           |
| Prepared_stmt_count                      | 0           |
| Qcache_free_blocks                       | 1           |
| Qcache_free_memory                       | 67088568    |
| Qcache_hits                              | 0           |
| Qcache_inserts                           | 5           |
| Qcache_lowmem_prunes                     | 0           |
| Qcache_not_cached                        | 37          |
| Qcache_queries_in_cache                  | 2           |
| Qcache_total_blocks                      | 6           |
| Queries                                  | 175         |
| Questions                                | 175         |
| Rpl_status                               | AUTH_MASTER |
| Select_full_join                         | 0           |
| Select_full_range_join                   | 0           |
| Select_range                             | 0           |
| Select_range_check                       | 0           |
| Select_scan                              | 40          |
| Slave_heartbeat_period                   | 0.000       |
| Slave_open_temp_tables                   | 0           |
| Slave_received_heartbeats                | 0           |
| Slave_retried_transactions               | 0           |
| Slave_running                            | OFF         |
| Slow_launch_threads                      | 0           |
| Slow_queries                             | 0           |
| Sort_merge_passes                        | 0           |
| Sort_range                               | 0           |
| Sort_rows                                | 0           |
| Sort_scan                                | 8           |
| Table_locks_immediate                    | 87          |
| Table_locks_waited                       | 0           |
| Tc_log_max_pages_used                    | 0           |
| Tc_log_page_size                         | 0           |
| Tc_log_page_waits                        | 0           |
| Threads_cached                           | 0           |
| Threads_connected                        | 1           |
| Threads_created                          | 1           |
| Threads_running                          | 1           |
| Uptime                                   | 229620      |
| Uptime_since_flush_status                | 229620      |
+------------------------------------------+-------------+
289 rows in set (0.00 sec)
                                                                                                                                                             |
mysql的cnf里也要改重启不重启都生效


mysql字符集包括两部分(字符集,校对规则)
字符集  长度   说明
GBK      2      不是国际标准
utf-8    3      中英文混合的环境
latin1   1      mysql默认的字符集
utf8mb4  4      UTF-8 UNICODE
如何选用正确的字符集
如果处理各种各样的文字,发布到不同语言国家的地区,应选unicode字符集对mysql来说就是utf-8每个汉字三个字节,如果应用需处理英文,仅有少量汉字的UTF-8 更好
如果只需支持中文,并且数据量很大,性能要求也很高,可选GBK,若需要大量的运算,比较排序等,定长字符集,更快,性能更改
处理移动互联网业务,可能需要使用utf8mb4
[root@localhost ~]#  mysql  -S /data/3308/mysql.sock -e "show character set";
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
mysql  -S /data/3308/mysql.sock -e "show character set" | egrep "gbk|utf8|latin1"|awk '{print $0}'
lhost ~]# mysql  -S /data/3308/mysql.sock -e "show character set" | egrep "gbk|utf8|latin1"|awk '{print $0}'
latin1    cp1252 West European    latin1_swedish_ci    1
gbk    GBK Simplified Chinese    gbk_chinese_ci    2
utf8    UTF-8 Unicode    utf8_general_ci    3
utf8mb4    UTF-8 Unicode    utf8mb4_general_ci    4
mysql> show variables like 'character_set%'
    -> ;
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | latin1                                    |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

mysql> set names latin1
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | latin1                                    |
| character_set_connection | latin1                                    |
| character_set_database   | latin1                                    |
| character_set_filesystem | binary                                    |
| character_set_results    | latin1                                    |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)
改变了character_set_client,character_set_connection,character_set_results
同理mysql --default-character_set也是改变这三个且是临时生效
不乱吗
 character_set_client     | latin1        #客户端字符集set names latin1                        |
| character_set_connection | latin1       #连接字符集 set names latin1                         |
| character_set_database   | latin1       #数据库字符集 配置文件指定或者建库建表指定           |
| character_set_filesystem | binary                                                            |
| character_set_results    | latin1       #返回结果字符集  set names latin1                    |
| character_set_server     | latin1       #服务器符集 配置文件指定或者建库建表指定             |
| character_set_system     | utf8                                                              |
| character_sets_dir       | /application/mysql-5.5.32/shar
linux服务端改
[root@localhost ~]# cat /etc/sysconfig/i18n
#LANG="en_US.UTF-8"
LANG="zh_CN.UTF-8"
putty->window->translation->remote-character_set->utf8
对于已有的数据库想修改字符集不能直接通过 “alter database charater set *”或者“alter table tablename character set *”
这两个都没有更新已有记录的字符集,而是对新建的表或者记录生效,
已有的字符调整,必须先将数据导出,经过修改字符集后重新导入后才可完成,
修改数据库编码
alter database [your dbname] charset  [your character setting]
操作流程latin->gbk
1导出表结构
mysqldump -S /data/3308/mysql.sock  --default-character_set=latin1 -d dbname>alltable.sql;
2编辑alttable.sql;将latin1改为GBK
3确保数据不在跟新
mysqldump -S /data/3308/mysql.sock --quick --no-creat-info --extended-insert --default-character_set=latin1 dbname>alldata.sql
4打开alldata.sql将set names latin1改为set names  gbk;
5建库
create database dbname default charset gbk;(或者系统客户端和服务端都要调整)
6创建表执行alltable.sql
mysqldump -S /data/3308/mysql.sock dbname<alltable.sql
mysqldump -S /data/3308/mysql.sock dbname<alldata.sql


© 著作权归作者所有

下一篇: mysql索引
泡海椒
粉丝 11
博文 282
码字总数 288344
作品 0
成都
程序员
私信 提问
mysql字符集以及乱码实战

单实例启动 /etc/init.d/mysqld start 1启动mysqlsafe --datadir =? --pid=? other args >/dev/null 2>&1 & 2 初始化数据库时mysql输出给出的启动方法 mysqlsafe --user=mysql& 提示: 当找回......

泡海椒
2016/01/03
8
0
如何解决MySQL字符集乱码问题

一、字符集本概念 字符集的基本概念如下 : 字符(Character)是指人类语言中最小的表义符号。例如’A’、’B’等给定一系列字符,对每个字符赋予一个数值,用数值来代表对应的字符,这一数值就...

linux小陶
2016/12/10
15
0
MySQL字符集 GBK、GB2312、UTF8区别 解决 MYSQL中文乱码问题

MySQL中涉及的几个字符集 character-set-server/default-character-set:服务器字符集,默认情况下所采用的。 character-set-database:数据库字符集。 character-set-table:数据库表字符集...

酸奶喝不完
2012/08/23
0
2
Mysql-02.常用操作命令

下面的常用操作指令是已经用root用户或者其他管理员用户登录Mysql数据库成功后执行的操作,数据库版本是Mysql 5.7.16(root用户下执行select version();指令即可看到版本号)。只写了一些基础...

龙_沐风
2016/11/14
14
0
Linux下MySQL中文乱码的问题

我使用的是树莓派,源于Debian的 mysql的字符集设置如下 当前数据库字符集 表字符集 使用navicat for mysql 勾选使用MySQL字符集 65001(UTF-8) 插入中文件,navicat中显示为乱码,mysql查询也...

Dean_King
2014/09/07
1K
4

没有更多内容

加载失败,请刷新页面

加载更多

C 语言 二级指针操作文件 柔性数组使用

#include <stdio.h>#include <stdlib.h>#include <string.h>typedef struct _info* pInfo;struct _info{int line;int len;char data[0];}info;int getFil......

小张525
29分钟前
0
0
中介者模式

https://blog.csdn.net/jason0539/article/details/45216585

南桥北木
29分钟前
0
0
抽离css以及公共js

分离css 分离css:为何要把 CSS 文件分离出来,而不是直接一起打包在 JS 中。最主要的原因是我们希望更好地利用缓存。 extract-text-webpack-plugin > 1. 假设我们原本页面的静态资源都打包成...

莫西摩西
今天
1
0
Jenkins的配置从节点中默认没有Launch agent via Java Web Start,该如何配置使用

Jenkins的配置从节点中默认没有Launch agent via Java Web Start,如下图所示,而这种启动方式在Windows上是最方便的。 如何设置才能让出来呢? 1:打开"系统管理"——"Configure Global Sec...

shzwork
今天
2
0
BAT面试必问HashMap源码分析

HashMap 简介 HashMap 主要用来存放键值对,它基于哈希表的Map接口实现,是常用的Java集合之一。 JDK1.8 之前 HashMap 由 数组+链表 组成的,数组是 HashMap 的主体,链表则是主要为了解决哈...

别打我会飞
今天
17
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部