MySQL返回来的值都是字符串类型，还原每个字段【转载】

转载自https://chuanke.baidu.com/v4509752-209102-1284621.html C语言C++学习指南（数据库篇）MySQL与SQLite

AfMysql.h

  1 #ifndef _AF_MYSQL_H
  2 #define _AF_MYSQL_H
  3 
  4 /* AfMysql
  5      用于将MySQL的字段值转成相应的类型
  6 
  7     
  8      版本：2015-12-20
  9      官网：http://afanihao.cn   C/C++学习指南(MySQL篇)
 10 */
 11 
 12 #include <stdio.h>
 13 #include <stdlib.h>
 14 #include <string.h>
 15 
 16 #include <string>
 17 using std::string;
 18 
 19 class AfMysql
 20 {
 21 public:
 22     // 日期时间类型
 23     struct Date
 24     {
 25         Date():year(2000),month(1), day(1){}
 26         int year,month,day;
 27     };
 28     struct Time
 29     {
 30         Time():hour(0),minute(0), second(0){}
 31         int hour,minute,second;
 32     };
 33     struct DateTime
 34     {
 35         DateTime(): year(2000), month(1), day(1), hour(0), minute(0), second(0) {}
 36         int year,month,day;
 37         int hour,minute,second;
 38     };
 39 
 40     // 字符串类型
 41     typedef string  String;
 42     typedef int Int;
 43     typedef long long BigInt;
 44 
 45 public:
 46     static Int AsInt(const char* field, int length)
 47     {
 48         if(field==NULL) return 0;
 49         return atoi(field);        
 50     }
 51 
 52     static BigInt AsBigInt(const char* field, int length)
 53     {
 54         if(field==NULL) return 0;
 55 
 56         long long val;
 57 #ifdef _WIN32
 58         sscanf(field, "%I64d", &val);
 59 #else
 60 #endif
 61         return val;
 62     }
 63 
 64     // 时间类型
 65     static Date AsDate(const char* field, int length)
 66     {
 67         Date val;
 68         if(field==NULL) return val;
 69 
 70         sscanf(field, "%d-%d-%d", &val.year, &val.month, &val.day);
 71         return val;
 72     }
 73     static Time AsTime(const char* field, int length)
 74     {
 75         Time val;
 76         if(field==NULL) return val;
 77 
 78         sscanf(field, "%d:%d:%d", &val.hour, &val.minute, &val.second);
 79         return val;
 80     }
 81 
 82     static DateTime AsDateTime(const char* field, int length)
 83     {
 84         DateTime val;
 85         if(field==NULL) return val;
 86 
 87         sscanf(field,  "%d-%d-%d %d:%d:%d", 
 88             &val.year, &val.month, &val.day,
 89             &val.hour, &val.minute, &val.second);
 90         return val;
 91     }
 92 
 93     static String AsString(const char* field, int length)
 94     {
 95         if(field==NULL) return "";
 96 
 97         String val = field;
 98         return val;
 99     }
100 
101 };
102 
103 
104 
105 
106 
107 #endif

main.cpp

  1 #include <stdio.h>
  2 #include <stdlib.h>
  3 
  4 #include "AfMysql.h"
  5 
  6 // MySQL support on Windows
  7 #include <WinSock2.h>
  8 #include <mysql.h>
  9 #pragma comment(lib, "libmysql")
 10 
 11 
 12 int my_select(MYSQL* conn)
 13 {
 14     // SQL语句，末尾不加分号。每次只执行一条SQL语句。
 15     const char* sql = "SELECT * FROM student"; 
 16     int ret = mysql_query(conn, sql);
 17     if(ret != 0)
 18     {
 19         printf("error: %s 
", mysql_error(conn));
 20         return -1;
 21     }
 22 
 23     MYSQL_RES * result = mysql_store_result(conn);
 24     if(result == NULL)
 25     {
 26         //printf("error(%d): %s 
", mysql_errno(conn), mysql_error(conn));
 27     }
 28     else
 29     {
 30         // how many rows
 31         my_ulonglong num_rows = mysql_num_rows(result);
 32         printf("got %d rows: 
", (int)num_rows);
 33 
 34         // number of fields for each row
 35         unsigned int num_fields = mysql_num_fields(result);
 36         printf("number of fields: %d 
", (int)num_fields);
 37 
 38         // fetch the rows
 39         MYSQL_ROW row;
 40         while ((row = mysql_fetch_row(result)))
 41         {
 42             unsigned long *lengths = mysql_fetch_lengths(result);
 43             for(int i = 0; i < num_fields; i++)
 44             {
 45                 char* field = row[i]; // can be a NULL value
 46                 unsigned int field_length = lengths[i]; // the data length
 47 
 48                 //printf("     column[%d], length[%d] , data[%s] 
",
 49                 //    i, field_length, field ? field : "null");
 50                 // 处理每个字段
 51                 if(i==0)
 52                 {
 53                     AfMysql::Int val = AfMysql::AsInt(field, field_length);
 54                     printf("id: %d 
", val);
 55                 }
 56                 if(i==1)
 57                 {
 58                     AfMysql::String val = AfMysql::AsString(field, field_length);
 59                     printf("name: %s 
", val.c_str());
 60                 }
 61                 if(i==2)
 62                 {
 63                     AfMysql::Date val = AfMysql::AsDate(field, field_length);
 64                     printf("date: %d-%d-%d
", val.year, val.month, val.day);
 65                 }
 66                 if(i==3)
 67                 {
 68                     AfMysql::String val = AfMysql::AsString(field, field_length);
 69                     printf("cellphone: %s 
", val.c_str());
 70                 }
 71             }
 72             printf("
");
 73         }
 74 
 75         // release the memory
 76         mysql_free_result(result) ;
 77     }
 78 
 79     return 0;
 80 }
 81 
 82 int main()
 83 {
 84     long long val;
 85     sscanf("102388982430234", "%I64d", &val);
 86 
 87     if (mysql_library_init(0, NULL, NULL))
 88     {
 89         printf("could not initialize MySQL library
");
 90         return -1;
 91     }
 92 
 93     // 连接服务器
 94     MYSQL conn;
 95     mysql_init(&conn);
 96 
 97     MYSQL* ret = mysql_real_connect(&conn,
 98         "127.0.0.1","root","123456","example"
 99         ,0,NULL,0);
100     if (!ret)
101     {
102         printf("Failed to connect to database:  %s
",
103             mysql_error(&conn));
104     }
105 
106     my_select(&conn);
107 
108     // 关闭连接
109     mysql_close(&conn);
110 
111     mysql_library_end();
112     return 0;
113 }