Use C to connect to Mysql_MySQL

Use C to connect to Mysql 1. high-performance databases

---- In database application development, there is often a conflict between performance and cost. Develop the stock market by author

For example, you need to record rows with more than 1000 stocks updated every minute in real time.

Response to a large number of concurrent user data query requests. Considering cost effectiveness and maintainability

It must be implemented in a hardware and software environment based on PC Server and Windows NT platform. At first, we used MS SQL

Server 6.5 is used as the database system, and Visual C ++ 6.0 is used to develop the front-end for accessing the database, and ODBC is used.

After a lot of database configuration and program optimization, the data interface still cannot meet the performance requirements. Post-purchase

The database-Library interface of SQL Server bypasses the ODBC interpretation layer and can update the market data 30 times per second.

Supports 20-30 concurrent users to query the market, basically meeting the requirements (a single PC server, a single PII3

50 CPU, memory 128 M, SCSI hard disk ). Is it possible to further improve the system performance and load capabilities? Jing

Through analysis, the database server is the bottleneck of the system. Of course, you can use UNIX servers + large database systems

Platform, but the cost of development, operation and maintenance is several times higher than that of the microcomputer + Windows NT platform. In

In the development of other systems, such conflicts often occur. How to build large capacity and high efficiency on the microcomputer platform

Rate, easy to maintain, and cost-effective database system?

---- Evaluate the knowledge of domestic database application systems based on the microcomputer platform, typical distributed mail systems such as NetEase, using

The FreeBSD + MySQL platform provides excellent capacity, load capacity, and response speed. The author consulted MySQL's

Related documents show that MySQL is an excellent database system in GNU software (OpenSource free software,

It fully complies with SQL92 (Entry level) and ODBC (level 0-2) specifications.

A highly efficient relational database management system. According to the documents provided by MySQL, its data operations

As the most efficient of all databases, the Benchmark table is as follows:

Reading 2000000 rows by index

Database Seconds

For mysql 367

Mysql_odbc 464

Db2_odbc 1206

Informix _odbc 121126

Ms-sql_odbc 1634

Oracle_odbc 20800

Solid_odbc 877

Sybase_odbc 17614

Inserting (350768) rows

Database Seconds

For mysql 381

Mysql_odbc 619

Db2_odbc 3460

Informix _odbc 2692

Ms-sql_odbc 4012

Oracle_odbc 11291

Solid_odbc 1801

Sybase_odbc 4802

(Run on the same NT 4.0 machine)

---- From the Benchmark of MySQL, we can see that MySQL has outstanding performance (of course, the tested MySQL system

May have been optimized, the tested data may be selected for MySQL), and MySQL provides Windows NT

Yes. Can Windows NT + MySQL be an ideal choice for building high-performance database applications? The author uses MySQL

The data interface of MySQL has rewritten the program. after a period of operation, it is proved that MySQL is indeed an efficient and stable database,

It is ideal for building large-capacity, efficient, easy-to-maintain, and cost-effective database application systems. Install MySQL

Your experiences in running and development are shared with you.

II. install and run MySQL

---- First from http://www.mysql.com/(domestic users can go from http://www.freecode.com.cn/m

Irror/mysql/) to download the MySQL execution code and source code. Note: for Windows NT, you must select

Line code. the download is mysql-javasware-3.22.32-win.zip. After unpacking, execute Setup and press the screen

To complete the installation.

---- Copy the my-example.cnf under the MySQL root directory (c:/mysql) to c:/my. cnf, as prompted in the file

Edit my. cnf. (if the root directory of MySQL is c:/mysql, do not change my. cnf ). In the NT console window

, Enter the MySQL execution Directory (c:/mysql/bin), and run

---- C:/mysql/bin/mysqld-configurware -- standalone

---- The MySQL database engine is started. Open another NT console window and run

---- C:/mysql/bin/mysql

---- Create a database connection. after the prompt "mysql>" appears, run

---- Mysql> delete from user WHERE Host = 'localhost' AND User = '';

---- Mysql> QUIT

---- Delete all unauthorized users.

---- Then execute

---- C:/mysql/bin/mysqladmin reload

---- C:/mysql/bin/mysqladmin-u root password your_password

---- Your_password is the password of your database administrator and must be kept properly.

---- If you want to connect to the database as an administrator every time, edit c:/my. cnf and add

:

User = root

Password = your_password

To stop the MySQL database engine, run

C:/mysql/bin/mysqladmin-u = root-p shutdown

After entering the administrator password as prompted, the MySQL database engine stops.

III. MySQL client application development

---- MySQL provides a wide range of data interface APIs, including C, C ++, Perl, PHP, Python, TCL, and other APIs and

JDBC and ODBC interfaces. For the sake of performance, we use the MySQL c api for development. Now we use Visual C ++

Environment as an example.

---- Create a Project for the Win32 Console Application and add "c:/mysql/include" to the compilation

Included path of translation Options (add/I "d:/mysql/include & quounter.cgol.net in Project Options?

# Include

# Include

# Include

# Include

Int main (int argc, char * argv [])

{

Char szTargetDSN [] = "test ";

Char szSqlText [500] = "";

Char aszFlds [25] [25];

MYSQL * myData;

MYSQL_RES * res;

MYSQL_FIELD * fd;

MYSQL_ROW row;

Int I, j, k;

BOOL bCreate = TRUE;

If (myData = mysql_init (MYSQL *) 0 ))

// Initialize the data structure

& Mysql_real_connect (myData, NULL,

// Connect to the database

"Root", "your_password", szTargetDSN,

MYSQL_PORT, NULL, 0 ))

{

If (bCreate)

{

Sprintf (szSqlText, // Construct an SQL statement

"Create table mytable"

// Create a new table

"(Time datetime, s1 char (6 ),"

"S2 char (11), s3 int, s4 int )");

If (mysql_query (myData, szSqlText ))

// Execute the SQL statement

{// An error occurred while executing the SQL statement

ErrLog ("Can't create table ");

Mysql_close (myData );

Return FALSE;

}

}

Sprintf (szSqlText,

"Insert into mytable"

// Insert data into the table

"Values ('2017-3-10 21:01:30 ',"

// Pay attention to the time format

"'Test', 'mysqltest )");

If (mysql_query (myData, szSqlText ))

{// An error occurred while executing the SQL statement

ErrLog ("Can't insert data to table ");

Mysql_close (myData );

Return FALSE;

}

Sprintf (szSqlText, "select * from mytable ");

If (mysql_query (myData, szSqlText ))

// Perform data retrieval

{

// An error occurred while executing the SQL statement

Mysql_close (myData );

Return FALSE;

}

Else

{

Res = mysql_store_result (myData );

// Obtain the query result

I = (int) mysql_num_rows (res );

// Obtain valid records

Printf ("Query: % s/n % ld records found:

/N ", szSqlText, I );

For (I = 0; fd = mysql_fetch_field (res );

I ++)

Strcpy (aszFlds [I], fd-> name );

// Obtain the field names

For (I = 1; row = mysql_fetch_row (res );)

// Read each record in sequence

{J = mysql_num_fields (res );

// Obtain the number of fields in the record

Printf ("Record # % ld:-/n", I ++ );

For (k = 0; k <j; k ++)

// Output the values of each field

Printf ("watermark # % d (% s): % s/n", k + 1, aszFlds [k],

(Row [k] = NULL) |

(! Strlen (row [k])? "NULL": row [k]);

Puts ("=================================/n ");

}

Mysql_free_result (res );

}

}

Else

{// Database connection error

ErrLog ("Can't connect to the mysql server ");

Mysql_close (myData );

Return FALSE;

}

Mysql_close (myData );

Return TRUE;

}

---- Briefly describe several functions. for details, refer to the MySQL documentation:

---- 1. MYSQL * mysql_init (MYSQL * mysql)

---- Initialize a data structure of the MYSQL type to prepare for executing mysql_real_connect. Parameters

Mysql is a pointer to this structure. if mysql is NULL, a new MYSQL data structure is created and initialized.

The new structure will be released in mysql_close.

---- If the connection succeeds, a pointer to the initialized MYSQL data structure is returned. otherwise, NULL is returned.

---- 2. 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 int client_flag)

---- Establish a connection with the MySQL database engine. Before performing further data operations, make sure that mysql_re

Al_connect () is returned successfully.

---- The mysql parameter is the return value of mysql_init;

---- The host parameter is the TCP/IP host name of the machine running the MySQL database engine. if it is NULL, the default value is "lo

Calhost ";

---- The user and passwd parameters are valid users and passwords of the MySQL database;