<code class="hljs perl">/* ********** gcc main.c -lsqlite3 -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient ************ SQLITE_OK = 0; 返回成功 SQLITE_ERROR = 1; SQL错误或错误的数据库 SQLITE_INTERNAL = 2; An internal logic error in SQLite SQLITE_PERM = 3; 拒绝访问 SQLITE_ABORT = 4; 回调函数请求中断 SQLITE_BUSY = 5; 数据库文件被锁 SQLITE_LOCKED = 6; 数据库中的一个表被锁 SQLITE_NOMEM = 7; 内存分配失败 SQLITE_READONLY = 8; 试图对一个只读数据库进行写操作 SQLITE_INTERRUPT = 9; 由sqlite_interrupt()结束操作 SQLITE_IOERR = 10; 磁盘I/O发生错误 SQLITE_CORRUPT = 11; 数据库磁盘镜像畸形 SQLITE_NOTFOUND = 12; (Internal Only)表或记录不存在 SQLITE_FULL = 13; 数据库满插入失败 SQLITE_CANTOPEN = 14; 不能打开数据库文件 SQLITE_PROTOCOL = 15; 数据库锁定协议错误 SQLITE_EMPTY = 16; (Internal Only)数据库表为空 SQLITE_SCHEMA = 17; 数据库模式改变 SQLITE_TOOBIG = 18; 对一个表数据行过多 SQLITE_CONSTRAINT = 19; 由于约束冲突而中止 SQLITE_MISMATCH = 20; 数据类型不匹配 SQLITE_MISUSE = 21; 数据库错误使用 SQLITE_NOLFS = 22; 使用主机操作系统不支持的特性 SQLITE_AUTH = 23; 非法授权 SQLITE_FORMAT = 24; 辅助数据库格式错误 SQLITE_RANGE = 25; 2nd parameter to sqlite_bind out of range SQLITE_NOTADB = 26; 打开的不是一个数据库文件 SQLITE_ROW = 100; sqlite_step() has another row ready SQLITE_DONE = 101; sqlite_step() has finished executing *************************************************************************************************** */ #include "DB.h" gseMutex dbMutex=GSE_MUTEX_INIT; static int IsUpdateAll = 0; static int iDelCount = 0; const int FREE_NUM = 1000; static int needVacuum = 0; int DBOpen(dbClass *sqlDB, const char *sqlName, char *localhost, char *user, char *password) { if(sqlDB == NULL || sqlName == NULL || 0 == strlen(sqlName)) { gseLog(L_ERROR,"NULL *p"); return -1; } int ret = 0; if(sqlDB->type == SQL_SQLITE3) { gseMutexLock(&dbMutex); sqlDB->db = (sqlite3 *)sqlDB->db; localhost = NULL; user = NULL; password = NULL; char sqlPathName[64]={0}; sprintf(sqlPathName, "%s", sqlName); ret = sqlite3_open(sqlPathName, (struct sqlite3 **)&(sqlDB->db)); if(ret != SQLITE_OK) { gseLog(L_ERROR,"ret = %d, Cannot open db: %s",ret,sqlite3_errmsg(sqlDB->db)); } gseMutexUnlock(&dbMutex); // gseLog(L_INFO,"Open database"); } else if(sqlDB->type == SQL_MYSQL) { #ifdef MYSQL_DEFINE sqlDB->db = (MYSQL *)sqlDB->db; if(localhost == NULL || user == NULL || password == NULL) { gseLog(L_ERROR,"localhost user password is NULL"); return -1; } char creatDbStr[64]={0}; char useDbStr[64]={0}; sprintf(creatDbStr, "create database %s", sqlName); sprintf(useDbStr, "use %s", sqlName); sqlDB->db= mysql_init(NULL); if (sqlDB->db== NULL) { gseLog(L_ERROR,"sqlOpen Error 1 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db)); exit(1); } if (mysql_real_connect(sqlDB->db, localhost, user, password, NULL, 0, NULL, 0) == NULL) { gseLog(L_ERROR,"sqlOpen Error 2 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db)); exit(1); } ret = mysql_query(sqlDB->db, creatDbStr); if (ret) { gseLog(L_ERROR,"sqlOpen Error 3 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db)); } ret = mysql_query(sqlDB->db, useDbStr); if (ret) { gseLog(L_ERROR,"sqlOpen Error 4 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db)); exit(1); } #endif } else { return -2; } return ret; } int DBClose(dbClass *sqlDB) { if(NULL == sqlDB || NULL == sqlDB->db) { return -1; } int ret = 0; if(sqlDB->type == SQL_SQLITE3) { gseMutexLock(&dbMutex); sqlDB->db = (sqlite3 *)sqlDB->db; ret = sqlite3_close(sqlDB->db); gseMutexUnlock(&dbMutex); } else if(sqlDB->type == SQL_MYSQL) { #ifdef MYSQL_DEFINE sqlDB->db = (MYSQL *)sqlDB->db; mysql_close(sqlDB->db); #endif } else { return -2; } // gseLog(L_INFO, "Close database"); return ret; } int DBCreate(dbClass *sqlDB, const char *tableName, char *creatValueStr) { if(sqlDB == NULL || tableName == NULL || creatValueStr == NULL || NULL == sqlDB->db || 0 == strlen(creatValueStr)) { gseLog(L_ERROR,"*db is NULL"); return -1; } char *errmsg = 0; int ret = 0; char createString[MAX_SQLSTR]={0}; if(strlen(creatValueStr) >= 1) creatValueStr[strlen(creatValueStr) - 1]='\0'; sprintf(createString, "create table %s(%s)", tableName,creatValueStr); //create table tb_cardId(cardID KEY, cardNum INT, endDate INT, password NVARCHAR(8)) memset(creatValueStr, 0, strlen(creatValueStr)); if(sqlDB->type == SQL_SQLITE3) { gseMutexLock(&dbMutex); sqlDB->db = (sqlite3 *)sqlDB->db; ret = sqlite3_exec(sqlDB->db,createString,NULL,NULL,&errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR,"ret = %d, create table fail: %s", ret, errmsg); } else gseLog(L_INFO, "create table %s success.", tableName); sqlite3_free(errmsg); gseMutexUnlock(&dbMutex); } else if(sqlDB->type == SQL_MYSQL) { #ifdef MYSQL_DEFINE sqlDB->db = (MYSQL *)sqlDB->db; ret = mysql_query(sqlDB->db, createString); if (ret) { gseLog(L_ERROR,"sqlCreate Error %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db)); } #endif } else { return -2; } return ret; } int DBInsert(dbClass *sqlDB, const char *tableName, char *valuesString) { if(sqlDB == NULL || tableName == NULL || valuesString == NULL || NULL == sqlDB->db || 0 == strlen(valuesString)) { gseLog(L_ERROR,"*db is NULL"); return -1; } char *errmsg = 0; int ret = 0; char sqlInsetTable[MAX_SQLSTR]={0}; if(strlen(valuesString) >= 1) valuesString[strlen(valuesString)-1]='\0'; sprintf(sqlInsetTable, "insert into %s values(%s)",tableName,valuesString); //insert into tb_cardId values(1,124343223,20130101,'12345678'); memset(valuesString, 0, strlen(valuesString)); if(sqlDB->type == SQL_SQLITE3) { gseMutexLock(&dbMutex); sqlDB->db = (sqlite3 *)sqlDB->db; ret = sqlite3_exec(sqlDB->db,sqlInsetTable,NULL,NULL,&errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR, "%s",sqlInsetTable); gseLog(L_ERROR,"ret = %d, inset table fail: %s",ret,errmsg); } sqlite3_free(errmsg); gseMutexUnlock(&dbMutex); } else if(sqlDB->type == SQL_MYSQL) { #ifdef MYSQL_DEFINE sqlDB->db = (MYSQL *)sqlDB->db; ret = mysql_query(sqlDB->db, sqlInsetTable); if (ret) { gseLog(L_ERROR,"sqlInsert Error 4 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db)); } #endif } else { ret = -2; } if(1 == IsUpdateAll && 0 != ret) { rollBack(sqlDB); return ret; } return ret; } int DBUpdate(dbClass *sqlDB, const char *tableName, char *updateSetStr, char *updateWhereStr) { if(sqlDB == NULL || tableName == NULL || updateSetStr == NULL || updateWhereStr == NULL) { gseLog(L_ERROR,"*db is NULL"); return -1; } if(strlen(updateSetStr) >= 1) updateSetStr[strlen(updateSetStr)-1]='\0'; if(strlen(updateWhereStr) >= 3) updateWhereStr[strlen(updateWhereStr)-3]='\0'; char *errmsg = 0; int ret = 0; char sqlUpdateTable[MAX_SQLSTR]={0}; sprintf(sqlUpdateTable, "update %s set %s where %s", tableName, updateSetStr, updateWhereStr); //update tb_cardID set cardNum=1243423,password='323443' where cardID=1 and endDate=2013; memset(updateSetStr, 0, strlen(updateSetStr)); memset(updateWhereStr, 0, strlen(updateWhereStr)); if(sqlDB->type == SQL_SQLITE3) { gseMutexLock(&dbMutex); sqlDB->db = (sqlite3 *)sqlDB->db; ret = sqlite3_exec(sqlDB->db,sqlUpdateTable,NULL,NULL,&errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR, "%s",sqlUpdateTable); gseLog(L_ERROR,"ret = %d, update table fail: %s", ret, errmsg); } sqlite3_free(errmsg); gseMutexUnlock(&dbMutex); } else if(sqlDB->type == SQL_MYSQL) { #ifdef MYSQL_DEFINE sqlDB->db = (MYSQL *)sqlDB->db; ret = mysql_query(sqlDB->db, sqlUpdateTable); if (ret) { gseLog(L_ERROR,"sqlUpdate Error 4 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db)); } #endif } else { ret = -2; } if(1 == IsUpdateAll && 0 != ret) { rollBack(sqlDB); return ret; } return ret; } int DBSearch(dbClass *sqlDB, const char *tableName, char *searchWhereStr, char (*returnValue)[SEARCH_MAXLEN]) { if(sqlDB == NULL || tableName == NULL || searchWhereStr == NULL) { gseLog(L_ERROR,"*db is NULL"); return -1; } char *errmsg = 0; int ret = 0; char sqlSearchTable[MAX_SQLSTR]={0}; int i; int nRow=0,nColumn=0; if(strlen(searchWhereStr) >= 3) searchWhereStr[strlen(searchWhereStr)-3]='\0'; sprintf(sqlSearchTable, "select * from %s where %s",tableName,searchWhereStr); //select * from tb_cardID where cardNum=323435; memset(searchWhereStr, 0, strlen(searchWhereStr)); if(sqlDB->type == SQL_SQLITE3) { gseMutexLock(&dbMutex); sqlDB->db = (sqlite3 *)sqlDB->db; char **azResult; ret = sqlite3_get_table(sqlDB->db, sqlSearchTable, &azResult, &nRow, &nColumn,&errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR, "%s",sqlSearchTable); gseLog(L_ERROR,"ret = %d, search table fail: %s", ret, errmsg); } // gseLog(L_INFO,"row:%d column=%d",nRow, nColumn); for(i=nColumn;i<(nRow+1)*nColumn;i++) { // printf("azResult[%d] = %s\n", i, azResult[i]); if(azResult[i] != NULL && (*returnValue + SEARCH_MAXLEN*(i-nColumn)) != NULL) strcpy(*returnValue + SEARCH_MAXLEN*(i-nColumn), azResult[i]); } sqlite3_free_table(azResult); sqlite3_free(errmsg); gseMutexUnlock(&dbMutex); if(0 == nRow) return 1; else if(1 <= nRow) return 0; } else if(sqlDB->type == SQL_MYSQL) { #ifdef MYSQL_DEFINE sqlDB->db = (MYSQL *)sqlDB->db; ret = mysql_query(sqlDB->db, sqlSearchTable); if (ret) { gseLog(L_ERROR,"sqlSearch Error 4 %u: %s\n", mysql_errno(sqlDB->db), mysql_error(sqlDB->db)); } MYSQL_RES *result; MYSQL_ROW row; MYSQL_FIELD *field; int num_fields; result = mysql_store_result(sqlDB->db); num_fields = mysql_num_fields(result); while ((row = mysql_fetch_row(result))) { nRow++; for(i = 0; i < num_fields; i++) { if (i == 0) { while(field = mysql_fetch_field(result)) { printf("%s ", field->name); } printf("\n"); } printf("%s ", row[i] ? row[i] : "NULL"); } } printf("\n"); mysql_free_result(result); if(nRow == 0) return 0; else return nRow; #endif } return -2; } int DBDelete(dbClass *sqlDB, const char *tableName, char *searchWhereStr) { if(sqlDB == NULL || tableName == NULL || searchWhereStr == NULL) { gseLog(L_ERROR,"*db is NULL"); return -1; } char *errmsg = 0; int ret = 0; char sqlDeleteTable[MAX_SQLSTR]={0}; if(strlen(searchWhereStr) >= 3) { searchWhereStr[strlen(searchWhereStr)-3]='\0'; sprintf(sqlDeleteTable, "delete from %s where %s", tableName, searchWhereStr); memset(searchWhereStr, 0, strlen(searchWhereStr)); } else if(strlen(searchWhereStr) == 0) sprintf(sqlDeleteTable, "delete from %s", tableName); //delete from tb_cardID where cardID=1; // gseLog(L_DEBUG, "%s",sqlDeleteTable); if(sqlDB->type == SQL_SQLITE3) { gseMutexLock(&dbMutex); sqlDB->db = (sqlite3 *)sqlDB->db; ret = sqlite3_exec(sqlDB->db,sqlDeleteTable,NULL,NULL,&errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR,"ret = %d, delete table fail: %s", ret, errmsg); } sqlite3_free(errmsg); needVacuum = 1; #if 0 if (iDelCount++ > FREE_NUM) { iDelCount = 0; ret = sqlite3_exec(sqlDB->db,"vacuum",NULL,NULL,&errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR,"ret = %d, vacuum fail: %s", ret, errmsg); } sqlite3_free(errmsg); } #endif gseMutexUnlock(&dbMutex); } else if(sqlDB->type == SQL_MYSQL) { #ifdef MYSQL_DEFINE sqlDB->db = (MYSQL *)sqlDB->db; ret = mysql_query(sqlDB->db, sqlDeleteTable); if (ret) { printf("sqlDelete Error %u: %s\n", mysql_errno(sqlDB->db), mysql_error(sqlDB->db)); } #endif } else { ret = -2; } if(1 == IsUpdateAll && 0 != ret) { rollBack(sqlDB); return ret; } return ret; } int DBVacuum(dbClass *sqlDB) { if(sqlDB == NULL) { gseLog(L_ERROR,"*db is NULL"); return -1; } char *errmsg = 0; int ret = 0; if(sqlDB->type == SQL_SQLITE3 && 1 == needVacuum) { gseMutexLock(&dbMutex); sqlDB->db = (sqlite3 *)sqlDB->db; ret = sqlite3_exec(sqlDB->db,"vacuum",NULL,NULL,&errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR,"ret = %d, vacuum fail: %s", ret, errmsg); } else { needVacuum = 0; } sqlite3_free(errmsg); gseMutexUnlock(&dbMutex); } return ret; } int DBIndex(dbClass *sqlDB, const char *tableName, char *indexName, char *columnName) { if(sqlDB == NULL || tableName == NULL || indexName == NULL || columnName == NULL) { gseLog(L_ERROR,"SQLITE_Index NULL *p"); return -1; } int ret = 0; char *errmsg = 0; char sqlCreateIndex[MAX_SQLSTR]={0}; sprintf(sqlCreateIndex, "create index %s on %s(%s)", indexName, tableName, columnName); // gseLog(L_DEBUG, "%s",sqlCreateIndex); if(sqlDB->type == SQL_SQLITE3) { sqlDB->db = (sqlite3 *)sqlDB->db; ret = sqlite3_exec(sqlDB->db,sqlCreateIndex,NULL,NULL,&errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR,"ret = %d, index table fail: %s", ret, errmsg); } sqlite3_free(errmsg); } else if(sqlDB->type == SQL_MYSQL) { #ifdef MYSQL_DEFINE sqlDB->db = (MYSQL *)sqlDB->db; ret = mysql_query(sqlDB->db, sqlCreateIndex); if (ret) { printf("sqlIndex Error %u: %s\n", mysql_errno(sqlDB->db), mysql_error(sqlDB->db)); } #endif } return ret; } int DBGetIdKey(dbClass *sqlDB, const char *tableName, const char *keyName, char (*returnValue)[ID_KEYLEN]) { if(sqlDB == NULL || tableName == NULL || keyName== NULL) { gseLog(L_ERROR,"*db is NULL"); return -1; } char *errmsg = 0; int ret = 0; char sqlSearchTable[MAX_SQLSTR]={0}; int i; int nRow=0,nColumn=0; sprintf(sqlSearchTable, "select %s from %s", keyName, tableName); if(sqlDB->type == SQL_SQLITE3) { gseMutexLock(&dbMutex); sqlDB->db = (sqlite3 *)sqlDB->db; char **azResult; ret = sqlite3_get_table(sqlDB->db, sqlSearchTable, &azResult, &nRow, &nColumn,&errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR, "%s",sqlSearchTable); gseLog(L_ERROR,"ret = %d, search table fail: %s", ret, errmsg); } // gseLog(L_INFO,"row:%d column=%d",nRow, nColumn); for(i=nColumn;i<(nRow+1)*nColumn;i++) { // printf("azResult[%d] = %s\n", i, azResult[i]); if(azResult[i] != NULL && (*returnValue + ID_KEYLEN*(i-nColumn)) != NULL) strcpy(*returnValue + ID_KEYLEN*(i-nColumn), azResult[i]); else break; } sqlite3_free_table(azResult); sqlite3_free(errmsg); gseMutexUnlock(&dbMutex); if(nRow == 0) return 0; else return nRow; } else if(sqlDB->type == SQL_MYSQL) { #ifdef MYSQL_DEFINE sqlDB->db = (MYSQL *)sqlDB->db; ret = mysql_query(sqlDB->db, sqlSearchTable); if (ret) { gseLog(L_ERROR,"sqlSearch Error 4 %u: %s\n", mysql_errno(sqlDB->db), mysql_error(sqlDB->db)); } MYSQL_RES *result; MYSQL_ROW row; MYSQL_FIELD *field; int num_fields; result = mysql_store_result(sqlDB->db); num_fields = mysql_num_fields(result); while ((row = mysql_fetch_row(result))) { nRow++; for(i = 0; i < num_fields; i++) { if (i == 0) { while(field = mysql_fetch_field(result)) { printf("%s ", field->name); } printf("\n"); } printf("%s ", row[i] ? row[i] : "NULL"); } } printf("\n"); mysql_free_result(result); if(nRow == 0) return 0; else return nRow; #endif } return -2; } int DBExecSql(dbClass *sqlDB, const char *sqlStr) { if(sqlDB == NULL || sqlStr == NULL) { gseLog(L_ERROR,"*db is NULL"); return -1; } char *errmsg = 0; int ret = 0; if(sqlDB->type == SQL_SQLITE3) { sqlDB->db = (sqlite3 *)sqlDB->db; ret = sqlite3_exec(sqlDB->db,sqlStr,NULL,NULL,&errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR,"ret = %d, exec sql(%s) fail: %s", ret, sqlStr, errmsg); } sqlite3_free(errmsg); } else if(sqlDB->type == SQL_MYSQL) { } return ret; } int dbAddColumn(char *createString, const char *keyName, const char *keyClass) { if(createString == NULL || keyName == NULL || keyClass == NULL) return -1; char tmpKey[128]; sprintf(tmpKey, "%s %s,", keyName, keyClass); strcat(createString, tmpKey); return 0; } int dbInsertColumn(char *insertString, char *value) { if(insertString == NULL || value == NULL) return -1; strcat(insertString,value); strcat(insertString,","); return 0; } int dbUpdateSet(char *updateSetStr, const char *setKey, char *setValue) { if(updateSetStr == NULL || setKey == NULL || setValue == NULL) return -1; char tmpStr[128]={0}; sprintf(tmpStr, " %s=%s,", setKey, setValue); strcat(updateSetStr, tmpStr); return 0; } int dbSearchWhere(char *searchWhereStr, const char *whereKey, char *whereValue) { if(searchWhereStr == NULL || whereKey == NULL || whereValue == NULL) return -1; char tmpStr[128]={0}; sprintf(tmpStr, " %s=%s and", whereKey, whereValue); strcat(searchWhereStr , tmpStr); return 0; } int setFlagIsUpdateAll(dbClass *sqlDB, int n) { int ret= 0; IsUpdateAll = n; if(-1 == IsUpdateAll) ret = rollBack(sqlDB); return ret; } int getFlagIsUpdateAll() { return IsUpdateAll; } int beginExclusive(dbClass *sqlDB) { int ret=0; char *errmsg = 0; sqlDB->db = (sqlite3 *)sqlDB->db; ret = sqlite3_exec(sqlDB->db,"BEGIN EXCLUSIVE", NULL,NULL, &errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR,"ret = %d, BEGIN EXCLUSIVE: %s", ret, errmsg); } sqlite3_free(errmsg); return ret; } int commitSQL(dbClass *sqlDB) { int ret=0; char *errmsg = 0; sqlDB->db = (sqlite3 *)sqlDB->db; ret=sqlite3_exec(sqlDB->db,"COMMIT", NULL,NULL, &errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR,"ret = %d, COMMIT: %s", ret, errmsg); } sqlite3_free(errmsg); return ret; } int rollBack(dbClass *sqlDB) { int ret=0; char *errmsg = 0; sqlDB->db = (sqlite3 *)sqlDB->db; ret=sqlite3_exec(sqlDB->db,"ROLLBACK", NULL,NULL, &errmsg); if(ret != SQLITE_OK) { gseLog(L_ERROR,"ret = %d, ROLLBACK: %s", ret, errmsg); } sqlite3_free(errmsg); return ret; } #ifdef MAIN_TEST void main() { char createValueStr[MAX_SQLSTR]={0}; char insertString[MAX_SQLSTR]={0}; char updateSetStr[MAX_SQLSTR]={0}; char searchWhereStr[MAX_SQLSTR]={0}; char *dbFileName="lkdb"; char *tableName="tb"; char *colList[]={"ID","names","sexsex"}; int ret=0; timeLog(); dbClass sqlDB; sqlDB.type = SQL_SQLITE3; DBOpen(&sqlDB, dbFileName, NULL, NULL, NULL); // sqlDB.type = SQL_MYSQL; // DBOpen(&sqlDB, dbFileName, "localhost", "root", "mima"); #if 1 dbAddColumn(createValueStr, colList[0], PRIMAY_KEY); dbAddColumn(createValueStr, colList[1], NVARCHAR_32); dbAddColumn(createValueStr, colList[2], NVARCHAR_32); DBCreate(&sqlDB, tableName, createValueStr); dbInsertColumn(insertString, "1"); dbInsertColumn(insertString, "'y_jg'"); dbInsertColumn(insertString, "'man'"); DBInsert(&sqlDB,tableName,insertString); dbInsertColumn(insertString, "2"); dbInsertColumn(insertString, "'lk'"); dbInsertColumn(insertString, "'man'"); DBInsert(&sqlDB,tableName,insertString); dbInsertColumn(insertString, "3"); dbInsertColumn(insertString, "'fqq'"); dbInsertColumn(insertString, "'woman'"); DBInsert(&sqlDB,tableName,insertString); dbInsertColumn(insertString, "4"); dbInsertColumn(insertString, "'xf'"); dbInsertColumn(insertString, "'man'"); DBInsert(&sqlDB,tableName,insertString); #endif dbUpdateSet(updateSetStr,"names", "'xxxx'"); dbSearchWhere(searchWhereStr,"id","3"); DBUpdate(&sqlDB,tableName,updateSetStr,searchWhereStr); DBClose(&sqlDB); timeLog(); } #endif</code>

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

Atom editor mac version download
The most popular open source editor

WebStorm Mac version
Useful JavaScript development tools
