C基础 MariaDB处理简单案例

引言MariaDB 是一款灰常不错开源数据库，这里直接用它来解决业务问题。业务需求: 现在数据库中表示按照天分表的. 突然我们需要按照月来处理数据.例如输入一个玩家id, 查找这个玩家这个月内看了一件事几次. 我们先搭建一个环境.操作系统:Linux version 4.4.0-22-generic （buildd@lgw01-41）（gcc version 5.3.1 20160413 （Ubuntu 5.3.1-14ubuntu2）） #40-Ubuntu SMP Thu May 12 22:03:46 UTC 2016 首先安装 MariaDB数据库sudo apt-get install mariadb-serversudo apt-get install mariadb-clientsudo apt-get install libmariadb2sudo apt-get install libmariadb-client-lgpl-devsudo apt-get install libreoffice-mysql-connector后面是C访问 MariaDB驱动. 这里扯一点, 目前关于MariaDB不懂问题, 搜不见直接当成mysql开始搜.MariaDB安装成功后默认是开启的, 看下面图描述后面搭建测试环境首先看 oss_musicelves.sql-- MySQL dump 10.10---- Host: localhostDatabase: oss_log-- -------------------------------------------------------- Server version5.5.24-tmysql-1.4/*！40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*！40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*！40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*！40101 SET NAMES utf8 */;/*！40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*！40103 SET TIME_ZONE="+00:00" */;/*！40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*！40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*！40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */;/*！40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `oss_musicelves`--DROP TABLE IF EXISTS `oss_musicelves`;CREATE TABLE `oss_musicelves` （`record_id` bigint（20） NOT NULL AUTO_INCREMENT,`account_id` bigint（20） NOT NULL,`server_id` int（11） NOT NULL,`char_id` bigint（20） NOT NULL,`char_sex` int（11） NOT NULL,`type_id` int（11） NOT NULL,`timeStamp` datetime NOT NULL DEFAULT "0000-00-00 00:00:00",`ptype` int（11） NOT NULL,`specifytype` int（11） NOT NULL,`childtype` int（11） NOT NULL,PRIMARY KEY （`record_id`）,KEY `idx_specifytype` （`specifytype`）） ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;---- Dumping data for table `oss_musicelves`--/*！40000 ALTER TABLE `oss_musicelves` DISABLE KEYS */;LOCK TABLES `oss_musicelves` WRITE;INSERT INTO `oss_musicelves` VALUES （1,411948833,84869352,27899597414400801,0,1812,"2016-05-31 14:27:41",0,1,1）,（2,1344702709,90964200,30422720614402293,0,1812,"2016-05-31 14:58:26",0,1,1）,（3,706409913,90964200,30422720614401465,1,1812,"2016-05-31 14:58:27",0,1,2）,（4,706409913,392964857,30422720614401465,1,1812,"2016-05-31 14:58:59",0,2,4）,（5,1344702709,392964857,30422720614402293,0,1812,"2016-05-31 14:58:59",0,2,4）,（6,706409913,90964200,30422720614401465,1,1812,"2016-05-31 15:04:52",0,1,2）,（7,706409913,392964857,30422720614401465,1,1812,"2016-05-31 15:05:54",0,2,4）,（8,1344702709,392964857,30422720614402293,0,1812,"2016-05-31 15:05:54",0,2,4）,（9,1344702709,90964200,30422720614402293,0,1812,"2016-05-31 15:10:29",0,1,1）,（10,706409913,90964200,30422720614401465,1,1812,"2016-05-31 15:10:32",0,1,2）,（11,1344702709,392964857,30422720614402293,0,1812,"2016-05-31 15:10:54",0,2,4）,（12,3145910262,90964200,29520779366416374,1,1812,"2016-05-31 15:30:00",0,1,1）,（13,1372825842,90964200,30173879500803314,1,1812,"2016-05-31 15:30:01",0,1,2）,（14,3145910262,392964857,29520779366416374,1,1812,"2016-05-31 15:30:04",0,2,4）,（15,1372825842,392964857,30173879500803314,1,1812,"2016-05-31 15:30:04",0,2,4）,（16,3145910262,392964857,29520779366416374,1,1812,"2016-05-31 15:34:24",0,2,4）,（17,1372825842,392964857,30173879500803314,1,1812,"2016-05-31 15:34:24",0,2,4）,（18,706409913,90964200,30422720614401465,1,1812,"2016-05-31 15:40:14",0,1,1）,（19,1344702709,90964200,30422720614402293,0,1812,"2016-05-31 15:40:16",0,1,2）,（20,3145910262,392964857,29520779366416374,1,1812,"2016-05-31 15:42:19",0,2,4）,（21,1372825842,392964857,30173879500803314,1,1812,"2016-05-31 15:42:19",0,2,4）,（22,1027763684,90964200,30175730790400484,0,1812,"2016-05-31 16:56:33",1,1,1）,（23,1372825842,90964200,30173879500803314,1,1812,"2016-05-31 16:56:50",0,1,2）,（24,1372825842,392964857,30173879500803314,1,1812,"2016-05-31 16:57:37",0,2,3）,（25,1027763684,392964857,30175730790400484,0,1812,"2016-05-31 16:57:37",1,2,3）,（26,1372825842,392964857,30173879500803314,1,1812,"2016-05-31 17:04:33",0,2,3）,（27,1027763684,392964857,30175730790400484,0,1812,"2016-05-31 17:04:33",1,2,3）,（28,1027763684,90964200,30175730790400484,0,1812,"2016-05-31 17:14:15",1,1,2）,（29,1372825842,392964857,30173879500803314,1,1812,"2016-05-31 17:14:50",0,2,3）,（30,1027763684,392964857,30175730790400484,0,1812,"2016-05-31 17:14:50",1,2,3）,（31,751699770,90964200,30175199027201850,1,1812,"2016-05-31 18:14:59",1,1,1）;UNLOCK TABLES;/*！40000 ALTER TABLE `oss_musicelves` ENABLE KEYS */;/*！40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*！40101 SET SQL_MODE=@OLD_SQL_MODE */;/*！40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*！40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*！40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*！40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*！40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*！40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 这个 oss_musicelves.sql 文件主要功能是创建 oss_musicelves数据库, 并填充数据.还有一个搭建环境的脚本 mariadb_test.sql 和上一个sql文件放在同一个目录下.# 创建一个测试数据库create database oss_log;# 进入oss_log 数据库use oss_log;# 创建 oss_musicelves 数据库, 并导入数据source oss_musicelves.sql;# 批量创建表和数据create table 2016_6_1_oss_musicelves select * from oss_musicelves;create table 2016_6_2_oss_musicelves select * from oss_musicelves;create table 2016_6_3_oss_musicelves select * from oss_musicelves;create table 2016_6_4_oss_musicelves select * from oss_musicelves;create table 2016_6_5_oss_musicelves select * from oss_musicelves;create table 2016_6_9_oss_musicelves select * from oss_musicelves;create table 2016_6_10_oss_musicelves select * from oss_musicelves;create table 2016_6_12_oss_musicelves select * from oss_musicelves;# 查询表是否创建成功show tables;# 这里处理拿到的数据select distinct table_name from information_schema.columns where table_name like "2016_6_%_oss_musicelves"; Linux系统教程：如何检查MariaDB服务端版本 http://www.linuxidc.com/Linux/2015-08/122382.htmMariaDB Proxy读写分离的实现 http://www.linuxidc.com/Linux/2014-05/101306.htmLinux下编译安装配置MariaDB数据库的方法 http://www.linuxidc.com/Linux/2014-11/109049.htmCentOS系统使用yum安装MariaDB数据库 http://www.linuxidc.com/Linux/2014-11/109048.htm安装MariaDB与MySQL并存 http://www.linuxidc.com/Linux/2014-11/109047.htmUbuntu 上如何将 MySQL 5.5 数据库迁移到 MariaDB 10 http://www.linuxidc.com/Linux/2014-11/109471.htm[翻译]Ubuntu 14.04 （Trusty） Server 安装 MariaDB http://www.linuxidc.com/Linux/2014-12/110048htm直接放在 MariaDB控制台中直接刷进去. 搭建的具体环境如下到这里环境基本搭建好了. MariaDB入门等等, 完全可以当做mysql 学习温故一遍。前言上面问题就是原本是 select * from oss_musicelves; 就可以解决的问题.这里需要输入年和月外加一些特殊条件 . select * from %_%_%_oss_musicelves; 解决. 单纯用sql脚本也可以解决.非常复杂.用的不熟.这里首先通过 shell 脚本处理touch getmouths.shchmod +x getmouths.shvi getmouths.sh 具体的脚本内容如下#！/bin/sh#得到输入的玩家ptidif [ $# -lt 1 ]thenecho "uage: $0 [ptid]"exit -1fiptid=$1mouth=$（date +%m | sed s"/^0//"）#第一个参数是月份if [ $# -ge 2 ]thenmouth=$2fi#第二个参数是年year=$（date +%Y）if [ $# -ge 3 ]thenyear=$3fi#得到查询的随机表名tbname=""${year}_${mouth}_%_oss_musicelves""#这里得到mysql 中所有合法表名rm -rf __tmptouch __tmp#开始查询数据库了, 需要以root权限启动这个脚本mysql -e "select distinct table_name from information_schema.columns where table_name like $tbname" | awk "NR>1" | while read namedomysql -e "select count（*） from oss_log.$name where specifytype = 1 and char_id = $ptid" | awk "NR>1" | while read cutdoecho "$name : $cut"echo $cut >> __tmpbreakdonedone#统计表里面的数据sum=$（cat __tmp | awk "{s+=$1} END {print s}"）rm -rf __tmp# 最后输出统计结果echo "$year-$mouth sum: $sum" 使用脚本截图通过shell可以完成我们的需求. Linux上shell真好用. Window的bat不好用.正文第一部分 : 让C调用MariaDB跑通先看测试Demo mariadb_demo.c#include <stdio.h>#include <stdlib.h>#include <mysql.h>/* * 第一个 mariadb程序 */int main（int argc, char *argv[]） {// 创建数据连接对象MYSQL *con = mysql_init（NULL）;if （con == NULL） {fprintf（stderr, "%s ", mysql_error（con））;exit（EXIT_FAILURE）;}if （！mysql_real_connect（con, "localhost", "root", "", NULL, 0, NULL, 0）） {fprintf（stderr, "%s ", mysql_error（con））; mysql_close（con）; exit（EXIT_FAILURE）;}if （mysql_query（con, "show databases;"）） {fprintf（stderr, "%s ", mysql_error（con））;mysql_close（con）;exit（EXIT_FAILURE）;}puts（"mariadb is connect and run succesed！"）;mysql_close（con）;return 0;} 具体的编译命令su rootgcc -Wall -ggdb2 -I/usr/include/mariadb -o mariadb_demo.out mariadb_demo.c -lmysqlclient./mariadb_demo.out 运行结果如下到这里基本C 调用 MariaDB 基本流程跑通了. 但是很不爽. 只能通过root用户使用.那我们改变这里不爽. 进入第二部分. 扩展资料 c in mariadb http://stackoverflow.com/questions/17265471/using-mariadb-in-c更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2016-06/132206p2.htm