druid mysql 乱码_2017.02.21 Mysql 字符集 乱码 排错过程

1，命令行确保以下字符集一致

*1, shell的字符集

*2, connect的字符集

*3, server的字符集

*4, database， table 字符集

MySQL API 编程，命令行确保以下字符集一致

*1, code的字符集

*2, set names utf8

*3, database， table字符集

以下是调用 MySQL API  范例

zhouss@zhouss-Shangqi-X4650/tmp/test$ cat main.c

/*

*1, shell的字符集

*2, connect的字符集

*3, server的字符集

*4, database， table 字符集

*

*1, code的字符集

*2, set names utf8

*3, database， table字符集

* */

#include 

#include 

#include 

#include 

int main(int argc, char **argv)

{

int       lRet          = 0;

int       lLoop         = 0;

int       lPos          = 0;

int       lColumnNum    = 0;

MYSQL     *hMySQLConnect= NULL;

MYSQL_ROW ppstRow       = NULL;

MYSQL_RES *result       = NULL;

MYSQL_FIELD *fields     = NULL;

char      *pszHost      = "localhost";

char      *pszUser      = "root";

char      *pszPass      = "password"; /* 如果是空，就是空字符   */

char      *pszDB        = "mysql";

char      szBuffer[2048]= {0};

char      *ppszQuery[]  = {

"SET NAMES UTF8",

"DROP   DATABASE IF EXISTS StudentDB",

"CREATE DATABASE StudentDB CHARACTER SET utf8",

"USE StudentDB",

"CREATE TABLE student(id int(2) PRIMARY KEY auto_increment, name varchar(50), email varchar(100)) DEFAULT CHARSET=utf8",

"INSERT INTO student VALUES(1, '老王', 'laowang@mail.com')",

"INSERT INTO student VALUES(2, '张三', 'zhangsan@gmail.com')",

"INSERT INTO student VALUES(3, '李四', 'lisi@outlook.com')",

"INSERT INTO student VALUES(4, '李刚', 'ligang@qq.com')",

"INSERT INTO student(name, email) VALUES('刘翔', 'liuxiang@yview.cn')",

"select * from student",

"update student set email='liuxiang@cctv.com' where name='刘翔'",

"delete from student where name='李四'",

"select * from student",

NULL

};

/* 1, 初始化MySQL handle  */

hMySQLConnect = mysql_init(NULL) ;

if (NULL == hMySQLConnect)

{

lRet =  mysql_errno(hMySQLConnect) ;

printf("MySQL初始化失败 \n");

return lRet;

}

printf( "MySQL初始化成功 \n");

/* 2, 链接远程MySQL服务器  */

/* MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag) */

hMySQLConnect = mysql_real_connect(hMySQLConnect, pszHost, pszUser, pszPass, pszDB, 0, NULL, 0);

if (NULL == hMySQLConnect)

{

lRet =  mysql_errno(hMySQLConnect) ;

printf( "MySQL链接失败 \n");

return lRet;

}

printf( "连接远程MySQL成功 \n");

/* 3, 主循环业务 */

while (1)

{

memset(szBuffer, 0, sizeof(szBuffer));

if( NULL == ppszQuery[lPos] ||

0 == strncmp(ppszQuery[lPos] , "exit" , 4) ||

0 == strncmp(ppszQuery[lPos] , "EXIT" , 4) )

{

break;

}

/* exec SqlQuery  */

strncpy(szBuffer, ppszQuery[lPos++], sizeof(szBuffer));

lRet = mysql_query(hMySQLConnect, szBuffer);

if (0 != lRet)

{

lRet =  mysql_errno(hMySQLConnect) ;

fprintf( stderr, "MySQL 查询失败 \n");

return lRet;

}

/* select ?   */

if (0 == strncmp("select", szBuffer, 6) || 0 == strncmp("SELECT", szBuffer, 6))

{

result = mysql_store_result(hMySQLConnect);

if(NULL == result)

{

break;

}

/* 输出字段名  */

lColumnNum = mysql_field_count(hMySQLConnect) ;

fields = mysql_fetch_fields(result);

memset(szBuffer, 0, sizeof(szBuffer));

for (lLoop = 0; lLoop 

{

strcat(szBuffer, fields[lLoop].name);

strcat(szBuffer, "    ");

}

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

/* 输出每行   */

while ((ppstRow = mysql_fetch_row(result)))

{

memset(szBuffer, 0, sizeof(szBuffer));

for (lLoop = 0; lLoop 

{

strcat(szBuffer, ppstRow[lLoop]);

strcat(szBuffer, "    ");

}

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

}

mysql_free_result(result);

}

}

/* 关闭MySQL handle */

mysql_close(hMySQLConnect);

printf( "离开MySQL \n");

return lRet;

}

zhouss@zhouss-Shangqi-X4650/tmp/test$ gcc main.c -L /usr/lib/x86_64-linux-gnu/ -lmysqlclient  && ./a.out

MySQL初始化成功

连接远程MySQL成功

id    name    email

1    老王    laowang@mail.com

2    张三    zhangsan@gmail.com

3    李四    lisi@outlook.com

4    李刚    ligang@qq.com

5    刘翔    liuxiang@yview.cn

id    name    email

1    老王    laowang@mail.com

2    张三    zhangsan@gmail.com

4    李刚    ligang@qq.com

5    刘翔    liuxiang@cctv.com

离开MySQL

zhouss@zhouss-Shangqi-X4650/tmp/test$ mysql -hlocalhost  -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 19

Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'character%';

+--------------------------+----------------------------+

| 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       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.01 sec)

mysql> select * from StudentDB.student;

+----+--------+--------------------+

| id | name   | email              |

+----+--------+--------------------+

|  1 | 老王   | laowang@mail.com   |

|  2 | 张三   | zhangsan@gmail.com |

|  4 | 李刚   | ligang@qq.com      |

|  5 | 刘翔   | liuxiang@cctv.com  |

+----+--------+--------------------+

4 rows in set (0.00 sec)

mysql> ^DBye

zhouss@zhouss-Shangqi-X4650/tmp/test$

set names utf8都作了什么？mysql> show variables like 'character%';

+--------------------------+----------------------------+

| 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       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.01 sec)

mysql> SET NAMES UTF8;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'character%';

+--------------------------+----------------------------+

| 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       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.01 sec)