2017.02.21 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  範例

 

[email protected]/tmp/test$ cat main.c /* *1, shell的字元集 *2, connect的字元集 *3, server的字元集  *4, database， table 字元集 * *1, code的字元集 *2, set names utf8 *3, database， table字元集 * */#include <stdio.h>#include <stdlib.h>#include <string.h>#include <mysql/mysql.h> 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"; /* 如果是空，就是Null 字元   */    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, ‘老王‘, ‘[email protected]‘)",                           "INSERT INTO student VALUES(2, ‘張三‘, ‘[email protected]‘)",                           "INSERT INTO student VALUES(3, ‘李四‘, ‘[email protected]‘)",                           "INSERT INTO student VALUES(4, ‘李剛‘, ‘[email protected]‘)",                           "INSERT INTO student(name, email) VALUES(‘劉翔‘, ‘[email protected]‘)",                           "select * from student",                           "update student set email=‘[email protected]‘ 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 < lColumnNum; 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 < lColumnNum; 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;}[email protected]/tmp/test$ gcc main.c -L /usr/lib/x86_64-linux-gnu/ -lmysqlclient  && ./a.out MySQL初始化成功 串連遠程MySQL成功 id    name    email     1    老王    [email protected]     2    張三    [email protected]     3    李四    [email protected]     4    李剛    [email protected]     5    劉翔    [email protected]     id    name    email     1    老王    [email protected]     2    張三    [email protected]     4    李剛    [email protected]     5    劉翔    [email protected]     離開MySQL [email protected]/tmp/test$ mysql -hlocalhost  -uroot -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 19Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 | 老王   | [email protected]   ||  2 | 張三   | [email protected] ||  4 | 李剛   | [email protected]      ||  5 | 劉翔   | [email protected]  |+----+--------+--------------------+4 rows in set (0.00 sec)mysql> ^DBye[email protected]/tmp/test$

本文出自 “魂鬥羅” 部落格，請務必保留此出處http://990487026.blog.51cto.com/10133282/1899798

2017.02.21   Mysql 字元集 亂碼 排錯過程