1, Environmental installation
The first is the installation of MySQL, which is directly installed only with apt get command (this method is recommended here, because it does not need to link the relevant path when compiling later programs)
sudo apt-get install mysql-server
After my installation, the relevant header files and dynamic libraries are in the following directory:
// Header file /usr/include/mysql/ // Dynamic library /usr/lib/x86_64-linux-gnu/
Finally, ensure that the MySQL service is running. The relevant commands are as follows:
// Start MySQL service mysqld start // Stop MySQL service mysqld stop // Restart MySQL service mysqld restart // View MySQL running status systemctl status mysql.service
2, Disposition
After installing MySQL for the first time, you can log in directly with the root user in the same way, and the error Access denied for user 'root' @ 'localhost' will be reported. At this point, we first check the MySQL configuration file to obtain the default user name and password:
// command sudo cat /etc/mysql/debian.cnf
// Profile content [client] host = localhost user = debian-sys-maint password = ***(Here I hide a string of English and numbers) socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = debian-sys-maint password = ***(Here I hide a string of English and numbers) socket = /var/run/mysqld/mysqld.sock
We directly use the user name Debian sys maint and password provided in the configuration file to log in to MySQL
$ mysql -udebian-sys-maint -p Enter password: (Enter the password in the configuration file here)
After entering MySQL, configure the password of the root account
mysql> update user set password=password('password') where user='root' and host='localhost'
mysql> flush privileges; //Don't forget to let MySQL reload the permission data in this step
At this point, exit mysql, and we can normally enter MySQL with the root account and the set password.
3, Header file
#include<mysql/mysql.h>
4, Function
1. Allocate or initialize MYSQL objects
MYSQL *mysql_init(MYSQL *mysql)
Parameter: MySQL instance
For the MYSQ object to be initialized, pass in the object address and NULL pointer. The function will allocate, initialize and return a new object. Otherwise, the object is initialized and the address of the object is returned.
MYSQL mysql; mysql_init(&mysql); // perhaps MYSQL mysql = mysql_init(NULL);
2. Connect to a mysql server
MYSQL *mysql_real_connect (MYSQL *mysql, const char*host, const char*user, const char*passwd, const char*db, unsigned intport, const char*unix_socket, unsigned long client_flag)
Parameter: mysql instance handle returned by the previous function of mysql.
Host the host of the database to which you want to connect, which can be an ip address or host name.
User indicates the user name to log in to the database.
passwd login password.
db is the database accessed.
The tcp/ip port of port mysql is 3306 by default.
unix_socket indicates the connection type.
client_flag is temporarily 0.
If the connection is successful, the MYSQL * connection handle is returned. NULL if the connection fails. For a successful connection, the return value is the same as the value of the first parameter
if(mysql_real_connect(&mysql,"localhost","root","dxm242012","Student",0,NULL,0) == NULL){
cout << "connect fial\n";
return-1;
}
3. Execute the SQL query of the specified query statement string
int mysql_query(MYSQL *mysql,const char *query)
Parameter: MySQL instance. query statement string
Return value: 0 for success and non-0 for failure
Return a result table. Assuming the query is successful, you can call mysql_num_rows() to see how many rows are returned corresponding to the SELECT statement, or call mysql_affected_rows() to see how many rows are affected by the DELETE, INSERT, REPLACE or UPDATE statement.
string sql = "Query statement; mysql_query(&mysql,sql.c_str());
4. Get the result set of the SQL query of the last query statement string
MYSQL_RES *mysql_store_result(MYSQL *mysql)
Retrieve the complete result set to the client. The most common way for clients to process result sets is by calling mysql_store_result() retrieves the entire result set at once. This function can get all the rows returned by the query from the server and save them on the client.
For each query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN, CHECK TABLE, etc.), MySQL must be called_ store_ Result () or mysql_use_result() .
For other queries, you do not need to call mysql_store_result() or mysql_use_result()，
But if MySQL is called in any case_ store_ Result(), which will not cause any damage or performance degradation.
Parameter: MySQL instance. query statement string
Return value: MySQL is returned successfully_ Res structure, which stores the query results
string sql = "Query statement; mysql_query(&mysql,sql.c_str()); MYSQL_RES * result = mysql_store_result(&mysql); if(result == NULL) cout << "fail\n";
5. Return the number of rows in the result set
int mysql_num_rows(MYSQL_RES* result)
Parameter: result result set
6. Return the number of columns in the result set
int mysql_num_fields(MYSQL_RES* result)
Parameter: result result set
7. Get the type of the next column
MYSQL_FIELD* mysql_fetch_field(MYSQL_RES *result);
Parameter: result result set
Gets the type of the next column, and returns NULL at the end.
do{
MYSQL_FIELD* field = mysql_fetch_field (result);
cout << field->name << "\t\t";
}while(row != NULL);
8. Get the next row from the result set
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
Parameter: result result set
Successfully returned an array with a value greater than 0.
do{
MYSQL_ROW row = mysql_fetch_row(result);
for(inti=0; i<mysql_num_fields(result); i++){
cout << row[i] << "\t\t";
}
cout << endl;
}while(row != NULL);
9. Get the type of the corresponding column
MYSQL_FIELD* mysql_fetch_field_direct(MYSQL_RES *result, int i);
Given the column number, return the type of the column, and return NULL at the end.
for(int i=0; i<mysql_num_fields(result); i++){
MYSQL_FIELD* field = field = mysql_fetch_field_direct(result,i);
cout << field->name << "\t\t";
}
10. Close MYSQL
MYSQL *mysql_close(MYSQL *mysql)
Parameter: MySQL instance
5, C + + encapsulates MyDB class
Later, these functions were simply encapsulated for direct use in the future.
class MyDB {
public:
MyDB();
~MyDB();
bool InitDB(string host,string user,string pwd,string dbname);
bool ExeSQL(string sql);
private:
MYSQL* mysql;
MYSQL_ROW row;
MYSQL_RES* result;
MYSQL_FIELD* field;
};
MyDB::MyDB() {
mysql = mysql_init(NULL);
if(mysql == NULL) {
cout << "Error: "<< mysql_error(mysql);
exit(-1);
}
}
MyDB::~MyDB() {
if(!mysql) {
mysql_close(mysql);
}
}
bool MyDB::InitDB(string host,string user,string pwd,string dbname) {
/*Connect database*/
if(!mysql_real_connect(mysql,host.c_str(),user.c_str(),pwd.c_str(),dbname.c_str(),0,NULL,0)) {
cout << "connect fial: "<< mysql_error(mysql);
exit(-1);
}
return true;
}
bool MyDB::ExeSQL(string sql) {
/*Execution failed*/
if(mysql_query(mysql,sql.c_str())) {
cout << "query fail: "<< mysql_error(mysql);
exit(1);
}
else {
/*Get result set*/
result = mysql_store_result(mysql);
int fieldnum = mysql_num_fields(result);
for(int i=0; i<fieldnum; i++) {
row = mysql_fetch_row(result);
if(row <= 0)
break;
for(int j=0; j<fieldnum; j++) {
cout << row[j] << "\t\t";
}
cout << endl;
}
mysql_free_result(result);
}
return true;
}