mysql中文乱码问题解决 / C程序插入仍是乱码解决 / 卸载重装教学

操作系统：Ubuntu12.04 LTS

温馨提示，在修改配置文件时务必备份，以防不测。

终端输入 sudo vi /etc/mysql/my.cnf

在[mysqld]下添加
character-set-server=utf8
collation-server=utf8_general_ci

按Esc键退出编译模式
:wq (保存并退出)

终端输入，重启mysql

sudo service mysql restart

进入mysql

mysql -uroot -p

查看数据库编码

show variables like "%char%";

我的如下

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

use数据库

use person;

重新创建表

CREATE TABLE person(ID varchar(24) PRIMARY KEY,name varchar(64),age varchar(4), sex varchar(8), company varchar(64), profession varchar(64), qq varchar(24), email varchar(64), telephone varchar(16));

插入数据看看

INSERT INTO person values('1', '我我', '1', '1', '1', '啊的', '1', '1', '1');

查询数据

mysql> select * from person;
+--------------------+--------+------+------+----------------------+----------------------+-----------+------------------+-------------+
| ID                 | name   | age  | sex  | company              | profession           | qq        | email            | telephone   |
+--------------------+--------+------+------+----------------------+----------------------+-----------+------------------+-------------+
| 1                  | 我我   | 1    | 1    | 1                    | 啊的                 | 1         | 1                | 1           |

恩，中文乱码已经解决。

---

C程序插入mysql数据库仍然显示乱码？
#include <mysql/mysql.h>
源代码中加入以下代码

// 设定数据库编码
mysql_query(conn,"SET NAMES 'utf8'");
mysql_query(conn,"SET CHARACTER SET utf8");
mysql_query(conn,"SET CHARACTER_SET_RESULT = utf8");

源码参考

	//数据存储到数据库
	MYSQL* conn;
	bool isAutoCommit;

	conn = mysql_init(NULL);
    if(NULL == conn)
    {
        printf("errno:%d error:%s\n",mysql_errno(conn),mysql_error(conn));
        exit(-1);
    }
    
	char ip[16] = "127.0.0.1";
	char user[20] = "test";
	char passwd[20] = "test";
	char database[20] = "person";
	int port = 3306;

	if(NULL == mysql_real_connect(conn,ip,user,passwd,database,port,NULL,0))
    {
        printf("---errno:%d error:%s\n\n",mysql_errno(conn),mysql_error(conn));
        exit(-1);
    }
	
	isAutoCommit = true;
	mysql_autocommit(conn,isAutoCommit);

	// 设定数据库编码
	mysql_query(conn,"SET NAMES 'utf8'");
	mysql_query(conn,"SET CHARACTER SET utf8");
	mysql_query(conn,"SET CHARACTER_SET_RESULT = utf8");

    char cmd[SQL_SIZE];
	// 置字节字符串cmd的前SQL_SIZE个字节为零且包括‘\0’
    bzero(cmd, SQL_SIZE);
	// 创建person表
    // strcpy(cmd, "CREATE TABLE person(ID varchar(24) PRIMARY KEY,name text,age text, sex text, company text, profession text, qq text, email text, telephone text);");

	// 将sql语句写入cmd变量
	sprintf(cmd, "INSERT INTO person values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');",idnumber,name,age,sex,company,profession,qq,email,telephone);

	printf("%s\n\n",cmd);

	if(mysql_query(conn,cmd) != 0)
	{
		printf("errno:%d error:%s\n\n",mysql_errno(conn),mysql_error(conn));
		exit(-1);
	}
	//mysql_affected_rows(conn);

	printf("增加成功\n\n");	

---

如果你玩脱了，下面是卸载重装教学

卸载

sudo apt-get purge mysql-*
sudo rm -rf /etc/mysql/ /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

重装

sudo apt-get install mysql-server
sudo apt install mysql-client
sudo apt install libmysqlclient-dev

检验是否安装mysql成功

sudo netstat -tap | grep mysql

进入mysql

mysql -uroot -p