Use C in Ubuntu to develop MySQL database applications

From: http://www.linuxidc.com/Linux/2011-01/31263.htm

 

I. Prepare the development environment
1. install linux first. Ubuntu is used here.
10.10
2. Install the MySQL software package. MySQL 10.10 is included in the system repository:
Sudo apt-Get install mysql-Server
Sudo apt-Get install mysql-Client
Sudo apt-Get install libmysqlclient15-dev

Ii. Check the MySQL service status
1. view the current MySQL service status
Sudo/etc/init. d/MySQL status
2. You can also use the following command to check whether the MySQL service has been started. If the result is blank, the MySQL service has not been started:
Sudo netstat-tap | grep MySQL or
PS-Ef | grep MySQL

Iii. start/stop/restart the MySQL Service
1. Start: sudo/etc/init. d/MySQL start
2. Stop: sudo/etc/init. d/MySQL stop
3. Restart: sudo/etc/init. d/MySQL restart

4. Access the database using the mysql client on the command line
1. Access the local host:
Mysql-uuser-ppassword db_name
2. Access the remote host:
Mysql-hhost-uuser-ppassword db_name
Note: Because the default configuration is only accessible from the local machine, you only need to comment out the bind-address line in/etc/MySQL/My. CNF on www.linuxidc.com to allow remote host access.
3. perform management operations:
Use mysqladmin and Related Parameters
4. Back up the db_name database:
Mysqldump
-Uroot-p -- default-character-set = utf8 -- OPT
-- Extended-insert = false -- triggers-r -- Hex-blob-x db_name> Bak. SQL
5. Restore the db_name database:
Mysql-uroot-P db_name <Bak. SQL
6. Back up the tbl_name data table:
Select * into OUTFILE '/usr/local/MySQL/f.txt' fields terminated by '|' from tbl_name;
7. Import the file/home/a.txt to the tbl_name table in the database.
Mysql> load data local infile '/home/a.txt' into Table tbl_name fields terminated by ', 'Lines terminated by'/R/N ';
8. Set the initial password of the MySQL database root.
Shell> mysql-u Root
Mysql> set password for ''@ 'localhost' = PASSWORD ('newpwd ');
Mysql> set password for ''@ 'host _ name' = PASSWORD ('newpwd ');
9. Change the password for a common user:
Mysql> set password for 'user _ name' @ 'host _ name' = PASSWORD ('newpwd ');
10. Create a Super User Account with full permissions to do anything:
Mysql> grant all privileges on *. * To 'monty '@ 'localhost'
-> Identified by 'some _ pass' with grant option;
Mysql> grant all privileges on *. * To 'monty '@' %'
-> Identified by 'some _ pass' with grant option;
Note: The first statement is to create an account for local connection and the second statement is to create an account for connecting from other hosts.
11. Create an account custom, which can access the bankaccount database, but can only be accessed from the Local Machine
Mysql> grant select, insert, update, delete, create, drop
-> On bankaccount .*
-> To 'custom' @ 'localhost'
-> Identified by 'obscure ';

 

V. Brief process for developing MySQL database applications in Linux C
Compilation: gcc-O bin_name hello. C-I/usr/include/MySQL-L/usr/lib/MySQL-lmysqlclient-LZ-LM
1. Define MySQL Variables
MySQL;
2. initialize MySQL Variables
Mysql_init (& MySQL );
3. Connect to the MySQL database

Mysql_real_connect (& MySQL, "hostname", "username", "password", "Database", 0, null, 0 );
4. Specify the character set for this connection (optional, www.linuxidc.com is mainly used to confirm that the client and server communicate with each other using the consistent character set)
Mysql_query (& MySQL, "set names 'utf8 '");
5. Execute SQL statements
Mysql_real_query (& MySQL, SQL, SQL _len );
6. Obtain query results
Mysql_res * result;
Result = mysql_store_result (& MySQL );
Row_count = (INT) mysql_num_rows (result );
Field_count = (INT) mysql_num_fields (result );
7. Call mysql_fetch_row cyclically to obtain each row of the query result.
Mysql_row row;
Row = mysql_fetch_row (result); // The next call will automatically point to the next row of result
Printf ("% s/n", row [Index]); // note the type of each field to be converted
8. release resources
Mysql_free_result (result );
9. Close the MySQL connection.
Mysql_close (& MySQL );

6. A simple example
// Print the first and second columns of the classmate data table in the PIM database of the local host. Username: Kitty, password: A1.
// Compile: gcc-O bin_name hello. C-I/usr/include/MySQL-L/usr/lib/MySQL-lmysqlclient-LZ-LM
# Include <stdio. h>
# Include <string. h>
# Include <stdlib. h>
# Include <mysql. h>

Int main (INT argc, char * argv [])
{
MySQL;

// Connect to MySQL
If (connect_mysql (& MySQL ))
{
Return 1;
}

// Load data
If (load_classmate (& MySQL ))
{
Mysql_close (& MySQL );
Return 1;
}

// Do something here.

// Close MySQL
Mysql_close (& MySQL );

Return 0;
}

// Connect to the MySQL database
Int connect_mysql (MySQL * MySQL)
{
Printf ("Initializing MySQL .................");

If (! Mysql_init (MySQL ))
{
Return 1;
}

Printf ("done/R/N ");

Printf ("connectiong to MySQL ...............");

If (! Mysql_real_connect (MySQL, "localhost", "kitty", "A1", "PIM", 0, null, 0 ))
{
Fprintf (stderr, "error: % S/R/N", mysql_error (MySQL ));
Return 1;
}

// Set the character set connected to MySQL
Mysql_query (MySQL, "set names 'utf8 '");

Printf ("done/R/N ");

Return 0;
}

// Load the data table
Int load_classmate (MySQL * MySQL)
{
Int ret, field_count, row_count, I;
Int * lengths;
Char * query = "select * From classmate ";
Mysql_res * result;
Mysql_row row;

Ret = mysql_real_query (MySQL, query, strlen (query ));

If (Ret! = 0)
{
Printf ("classmate data cannot be loaded. /N ");
Return 1;
}

Result = mysql_store_result (MySQL );
Row_count = (INT) mysql_num_rows (result );
Field_count = (INT) mysql_num_fields (result );

If (result = NULL & field_count = 0)
{
Printf ("no data found. ");
Return 1;
}

For (I = 0; I <row_count; I ++)
{
Row = mysql_fetch_row (result );
Printf ("% 2D | % 8 S/R/N", atoi (row [0]), row [1]);
}

Mysql_free_result (result );

Return 0;
}

 

VII. Solutions to Chinese garbled characters in MySQL Databases
MySQL has four default settings for character sets and verification rules: Server-level, database-level, table-level, and connection-level. The methods for solving Chinese garbled characters are set separately at these levels. Generally, garbled characters are caused by the absence of connection-level character sets.

1. Server-level Character Set
Server-level load: when the server is started, it is loaded according to the character set in the configuration file. The current server Character Set and verification rules can be used as the value of character_set_server and collation_server system variables. The value of these variables can be changed at runtime. At the server level, we have two solutions.

A. recompile MySQL
One way to change the set value is by recompiling. If you want to change the default server Character Set and verification rules when building from the source program, use -- With-charset and -- With-collation as configure parameters. For example:
Shell>./configure -- With-charset = utf8

B. Do not re-compile, only modify the current configuration file
Open/etc/MySQL/My. CNF and modify the encoding:
Add: default-character-set = utf8 under [mysqld]
Add: default-character-set = utf8 under [client]
Add: default-character-set = utf8 under [MySQL]
Restart MySQL!

2. Database-level Character Set
The character set and collation rules of the default database can be used as character_set_database and
Collation_database system variable. The server sets the values of these two variables whenever the default database is changed. If no
Default database. These two variables have the same value as the corresponding server-level variables (character_set_server and collation_server.
A. Recreate the database and specify the default Character Set
At the database level, the default Character Set of the database is specified when the database is created.
Mysql> Create Database db_name Character Set charset_name;
For example:
Mysql> Create Database db_name Character Set utf8;

B. Do not re-create a database. Use alter database to modify the default Character Set of the database.
If Character Set charset_name is not specified during table creation, you can use the following to re-specify mysql> alter database db_name Character Set charset_name;
For example:
Mysql> alter database db_name Character Set utf8;

3. Table-level Character Set
If the table Character Set and collation are not specified in the table definition, the database character set and collation are used by default.
A. Create a new table and specify the default character set.
The table-level Character Set specifies the default character set when creating a table.
Mysql> Create Table tbl_name (column_list) default Character Set charset_name;

B. Use ALTER TABLE to modify the default character set of a new table.
If Character Set charset_name is not specified during table creation, you can use the following to re-specify mysql> alter table tbl_name default Character Set charset_name;

Note: The column-level character set is similar to the table-level character set rules. You can also specify the column-level character set in the statements in the table to be created. If the column-level character set is not specified, the table-level character set is used by default, if the table does not specify the default Character Set, push up.

 

4. Connection-level Character Set
Understanding the connection-level character set from the following aspects: (similar to a network protocol, pre-configured communication rules between servers and clients)
A. Which character set is used in the query after the query leaves the client?
The server uses the character_set_client variable as the character set used in the query sent by the client.
B. Which character set should the server convert to after receiving the query?
Transfer
In other words, the server uses character_set_connection and collation_connection system variables. It
Convert the query sent by the client from character_set_client to character_set_connection (unless
String text has a preference like _ Latin1 or _ utf8 ). Collation_connection is important for comparing text strings.
It is not important to compare the column value strings because the column has a higher priority for checking rules.
C. Which character set should the server convert to before sending the result set or returning the error message to the client?
The character_set_results variable indicates that the server returns the query result to the character set used by the client. Includes result data, such as column values and result metadata (such as column names ).

We can adjust the settings of these variables. There are two statements that affect the connected character set:
Set names 'charset _ name'
Set Character Set charset_name

Set names displays the character sets used in the SQL statements sent by the client. Therefore, set names
The 'cp1251 'statement tells the server that "the information sent from this client will use the character set cp1251 ". It also specifies the character set for the results sent from the server back to the client. (For example, if you
Use a SELECT statement to indicate the character set used by the column value .)
The Set names 'X' statement is equivalent to the three statements:
Mysql> set character_set_client = X;
Mysql> set character_set_results = X;
Mysql> set character_set_connection = X;

To set a connection-level character set, you can execute a statement when the client program initiates a connection to the server when compiling a MySQL database application, as shown in the following situation:
Mysql_real_connect (& MySQL, "hostname", "username", "password", "Database", 0, null, 0 );
Mysql_query (& MySQL, "set names 'utf8 '");
In this way, you can specify the default character set used for this connection as utf8.

VIII. Common MySQL commands
1. List all character sets supported by MySQL:
Show Character Set;
2. Current MySQL Server Character Set
Show variables like 'character _ SET _ % ';
3. Current MySQL Server character set verification settings
Show variables like 'collation _ % ';
4. display character set settings of a Database
Name of the show create database;
5. display character set settings of a data table
Show create table table name;
6. Modify the database Character Set
Alter database name default Character Set 'utf8 ';
7. Modify the character set of a data table
Alter table table name default Character Set 'utf8 ';
8. Specify the character set during database creation
Create Database database name Character Set GBK collate gbk_chinese_ci;
9. Specify the character set during table Creation
Create Table 'mysqlcode '(
'Id' tinyint (255) unsigned not null auto_increment primary key,
'Content' varchar (255) not null
) Type = MyISAM Character Set GBK collate gbk_chinese_ci;

IX. References
MySQL 5.1 official Simplified Chinese version Reference Manual