Example of using MySQL in Windows

I. Selection of 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.
Data interfaces are configured in a large number of databases and Program After optimization, it is found that the performance 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/) download 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), run
---- C: \ mysql \ bin \ mysqld-policware -- Standalone
---- The MySQL database engine is started. Open another nt Console window and run
---- C: \ mysql \ bin \ mysql 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 <windows. h>
# Include <stdio. h>
# Include <string. h>
# Include <mysql. h>
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;
---- The parameter database is the name of the connected database;
---- The port, unix_socket, and client_flag parameters take the default values.
---- 3. Int mysql_query (MySQL * MySQL, const char * query)
---- Run the SQL statement in the query string. The query must end with 0. If the call succeeds, 0 is returned.
---- 4. mysql_res * mysql_store_result (MySQL * MySQL)
---- Return the execution results of select, show, describe, and explain statements. Create a new MySQL _
The res Data Structure stores the results in this structure. If no matching result is returned, an empty dataset is returned.
After processing the final result set, you must call mysql_free_result ().
---- If an error occurs, null is returned; otherwise, a pointer to the mysql_res structure is returned.
---- 5. mysql_row mysql_fetch_row (mysql_res * result)
---- Retrieve the next record in the result set. If there is no record or error, null is returned. Fields in a record
The number can be obtained using mysql_num_fields (result), and the values of each field can be obtained using row [0] to row [mysql_nu
M_fields (result)-1] array to access.
---- Add the c: \ mysql \ Lib \ libmysql. Lib interface library to the link options of the project, and set libmysql. d
Copy ll to the system directory (C: \ winnt \ System32) of the operating system to compile and run the program.
---- Here, a simple database application is developed. Of course, MySQL has a set of AP Functions
I. You can view the documentation. In addition, if cross-platform portability is required, you can use the ODBC interface of MySQL.
. You can configure the ODBC connection of mysql, or download the mydbc-2.50.29-nt.zip toolkit to configure
.

Iv. Summary
---- The author used MySQL to build a high-performance, high-stability database system on Windows NT. In this way, you can
Using the friendly interfaces of Windows NT and Visual C ++, you can also obtain the powerful functions of MySQL. Practice has proved that,
The cost of this solution is low (don't forget that MySQL is free of charge), high efficiency, short development cycle, and stable operation (MySQL's
Stability has been well evaluated on sites such as Yahoo, 163, and 263 ).
---- At present, my stock market query and trading system have obtained 70-records per second
Response to 50 concurrent users for data query performance, while the system development and operation costs are greatly reduced
. If you are worried about the efficiency of the database on the PC, try MySQL.