MySQL concept (login, character set, storage requirements, libmysql development environment)

Article Directory

1. Terminology

Database: A database is a collection of related tables.
Data table: A table is a matrix of data. A table in a database looks like a simple spreadsheet.
Column: A column (data element) contains the same data, such as postal code data.
Row: A row (= tuple, or record) is a group of related data, such as a piece of data subscribed by a user.
Redundancy: Store twice the data. Redundancy reduces performance but improves data security.
Primary key: The primary key is unique. A data table can only contain one primary key. You can use the primary key to query data.
Foreign key: A foreign key is used to associate two tables.
Composite key: A composite key (composite key) uses multiple columns as an index key and is generally used for composite indexes.
Index: Use the index to quickly access specific information in the database table. An index is
a structure for sorting the values of one or more columns in a database table . Similar to a catalog of books.
Referential integrity: Referential integrity requirements do not allow references to non-existent entities in the relationship. Integrity with entity is the integrity constraint condition that the relational model must meet, and the purpose is to ensure the consistency of the data.

2. Client login Mysql

[email protected]:~$ mysql -h127.0.0.1 -uroot -p

-u indicates the user name to choose to log in
-h indicates the host to be connected to
-p indicates the user password to log in

3. Some commands

1.show databases view the current database

2. use mysql to select mysql database

3. show tables view all tables in the currently selected database

4. Allow outside users to access mysql

1) After Mysql is installed, only the local computer is allowed to access Mysql by default.

use mysql
select user, host from user;
#update 修改其中某一条记录的 host 字段值为'%',或者新增一条记录且 host 字段值为'%'
update user set host='%' where user='lingsheng_test';
select user, host from user;
#显示下面的图片
Insert picture description here


Insert picture description here


2) If the above two situations do not exist or cannot be resolved, you can use the netstat command to view the ip bound to the mysql service. If the binding is 127.0.0.1, the external machine cannot access mysql either. It can be solved by modifying the Mysql configuration file. Taking the Ubuntu system as an example, using apt-get to install Mysql, the default location of the configuration file is usually
/etc/mysql/mysql.conf.d/mysqld.cnf.
Use the following command to edit the file

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Just comment out the following line:
bind-address = 127.0.0.1
3) If it is a firewall reason (if it is a cloud server, you need to check it)
4) mysql restart

sudo/etc/init.d/mysql restart

4.mysql character set

1. Check the character set

  1. Show MYSQL character set encoding
    show variables like'charact%';
  2. View MySQL database server and database character set
    show variables like'%char%';
  3. View the character set supported by the currently installed MySQL
    show charset;
    4) View the database encoding:
    SHOW CREATE DATABASE db_name;
  4. View table code:
    SHOW CREATE TABLE tbl_name;
  5. View field code:
    SHOW FULL COLUMNS FROM tbl_name;
  6. View the coded characters of the system:
    SHOW VARIABLES WHERE Variable_name LIKE'character_set_%' OR Variable_name LIKE'collation%';
  7. Set the encoding
    Add the following content in mysqld.cnf, save it, and restart mysql;
Insert picture description here

2. MySQL create and set character set and modify character set

1) Create the database character set specified database
the CREATE DATABASE the IF the NOT EXISTS mydb the DEFAULT the CHARACTER the SET UTF8 the COLLATE utf8_general_ci
2) specify the character set table creation
; CREATE TABLE table_name (id int unsigned ) DEFAULT CHARACTER SET utf8 COLLATEutf8_general_ci
specified character 3) create fields Set
CREATE TABLE table_name2 (id int,name char(10) CHARACTER SET utf8 COLLATE utf8_general_ci);
4) Modify the database character set
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE …];
5) Change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to the new character set:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE …]
For example:
ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
5) Just modify the default character set of the table :
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [ COLLATE ...];
e.g.
the ALTER TABLE LogTest the DEFAULT the CHARACTER the SET UTF8 the COLLATE utf8_general_ci;
. 6) the character set to modify the field
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
e.g.
ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

3. MySQL character set conversion process

1) MySQL Server converts the requested data from character_set_client to
character_set_connection when receiving the request ;
2) Converts the requested data from character_set_connection to the internal operation character set before performing internal operations.
The determination method is as follows:
Use the CHARACTER SET setting of each data field Value;
if the above value does not exist, use the DEFAULT CHARACTER SET setting value of the corresponding data table (MySQL extension, non-SQL standard);
if the above value does not exist, use the DEFAULT CHARACTER SET setting value of the corresponding database;
if the above value is not If it exists, the setting value of character_set_server is used.
3) Convert the operation result from the internal operation character set to character_set_results.

4. MySQL default character set

Traditional programs do not use such complicated configurations when creating databases and data tables. They use the default configuration
. So, where does the default configuration come from?
1) When compiling MySQL, a default character set is specified. This character set is latin1; (Latin1 is an alias of ISO-8859-1, written as Latin-1 in some environments.)
2) When installing MySQL, you can set it in the configuration file Specify a default character set in (my.ini), if not specified, this value is inherited from the one specified at compile time;
3) When starting mysqld, you can specify a default character set in the command line parameters, if not specified , This value is inherited from the configuration in the configuration file, at this time character_set_server is set to the default character set;
4) When a new database is created, unless explicitly specified, the character set of this database is set to
character_set_server by default ;
5) When a database is selected, character_set_database is set as the default character set of the database;
6) When a table is created in this database, the default character set of the table is set to character_set_database, which is the database The default character set;
7) When setting a column in the table, unless explicitly specified, the default character set of this column is the default character set of the table;

5. The difference between UTF8 and 4 UTF8mb4

Insert picture description here


1. MySQL added this utf8mb4 encoding after 5.5.3. mb4 means most bytes 4, which is
specifically used to be compatible with four-byte unicode. Fortunately, utf8mb4 is a superset of utf8, and
there is no need to do other conversions except to change the encoding to utf8mb4 . Of course, in order to save space, utf8 is usually sufficient .
2. Since utf8 can store most Chinese characters, why use utf8mb4? The original utf8 encoding supported
by mysql has a maximum character length of 3 bytes. If a 4-byte wide character is encountered, an exception will be inserted . The
maximum Unicode character that can be encoded by the three- byte UTF-8 is 0xffff, which is the Basic Multilingual
Plane (BMP) in Unicode . In other words, any Unicode characters that are not in the basic multi-text plane cannot be
stored in Mysql 's utf8 character set. Including Emoji expressions (Emoji is a special Unicode encoding, commonly found on ios
and android phones), many infrequently used Chinese characters, and any new Unicode characters, etc. (the
shortcomings of utf8 ).

Insert picture description here


3) It is recommended to use utf8mb4 for storage of Chinese.

6. Garbled solution

  1. First of all, you must clarify the encoding format of your client. This is the most important (IE6 generally uses utf8, the command line is generally gbk, and the general program is gb2312)
    2) Make sure your database uses utf8 format, all encodings take it all.
    3) Make sure that the connection character set is greater than or equal to the client character set, otherwise information will be lost, for example:
    latin1 <gb2312 <gbk <utf8, if set character_set_client = gb2312, then
    at least the connection character set must be greater than or equal to gb2312, otherwise Information will be lost.
    4) If the above three steps are done correctly, then all Chinese will be correctly converted into utf8 format and stored in the database. In order
    to adapt to different browsers and different clients, you can modify character_set_results to use different compilations.
    The code displays Chinese fonts. Since utf8 is the general direction, I still tend to use utf8 format to display
    Chinese in web applications .

5.mysql storage requirements

1) Numerical storage requirements

Column type storage requirements
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT, INTEGER 4 bytes
BIGINT 8 bytes
FLOAT§ If 0 <= p <= 24 is 4 bytes, if 25 < = p <= 53 is 8
bytes
FLOAT 4 bytes
DOUBLE [PRECISION], item REAL 4 bytes
DECIMAL(M,D), NUMERIC(M,D) variable length (0-4 bytes)
BIT (M) about (M+7)/8 bytes
int(10) where 10 refers to the width of the value, not bytes

2) Storage requirements for date and time types

Column type storage requirements
DATE 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
TIME 3 bytes
YEAR 1 byte

3) Storage requirements of string type

Column type storage requirements
CHAR(M) M bytes, 0 <= M <= 255
VARCHAR(M) L+1 bytes, where L <= M and 0 <= M <= 65535
BINARY(M) M Bytes, 0 <= M <= 255
VARBINARY(M) L+1 bytes, where L <= M and 0 <= M <= 255
TINYBLOB, TINYTEXT L+1 bytes, where L <28
BLOB, TEXT L+2 bytes, where L <216
MEDIUMBLOB, MEDIUMTEXT L+3 bytes, where L <224
LONGBLOB, LONGTEXT L+4 bytes, where L <232
ENUM('value1','value2', …) 1 or 2 bytes, depending on the number of enumerated values ​​(up to 65,535 values)
SET('value1','value2',...) 1, 2, 3, 4 or 8 bytes, depending on Number of set members (
up to 64 members)

The M in the above table is just to illustrate the size of the occupied space. In the actual creation table, char(20), varchar(20), 20 refers to characters instead of bytes (from version 4.0, the following refers to bytes); then the character The conversion between and byte depends on the character set, under utf-8, 1 character=3 bytes; under gbk, 1 character=2 bytes.

6. The development environment of libmysql

When compiling the code, you need to link the mysql library. You can compile
g++ -Wall mysql_test.cpp -o mysql_test -lmsqlclient
(lack of libmysqlclient-dev, just use the following command to install it.)
sudo apt-get install libmysqlclient-dev

1) Function prototype

1. MYSQL *mysql_init(MYSQL *mysql)

mysql: There are two cases, generally the method
⑴ is adopted. ⑴ The parameter passes NULL value.
In this case, it is obvious that the mysql_init() function internally applies for a piece of memory and then returns the first
address.
MYSQL *ms_conn = mysql_init(NULL);
Remember to release
mysql_close(ms_conn), ms_conn = NULL; ⑵The
parameter is passed to the object address.
In this case, stack memory is used. Obviously, the mysql_init() function should not allocate heap memory.
MYSQL ms_eg;
MYSQL *ms_conn = mysql_init(&ms_eg);
Remember to release
mysql_close(ms_conn), ms_conn = NULL;

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 long client_flag)

effect:

mysql_real_connect() attempts to establish a connection with the MySQL database engine running on the host. Before you can execute any other API functions that require a valid MySQL connection handle structure, mysql_real_connect() must complete successfully

parameter:

a) mysql: The address of the MYSQL structure. Before calling mysql_real_connect(), you must call
mysql_init() to initialize the MYSQL structure.
b) host: Must be a host name or IP address. If "host" is NULL or the string "localhost", the
connection will be treated as a connection to the local host.
c) passwd: the user's password. If "passwd" is NULL, only
the entry in the user table of the user (with an empty password field) will be checked for matching. In this way, the database administrator can
set up the MySQL permission system in a specific way. According to whether the user has a specified password, the user will have different
permissions.
d) db: database name. If db is NULL, the connection will set the default database to this value.
e) port: If "port" is not 0, its value will be used as the port number for TCP/IP connection. Note that the "host"
parameter determines the type of connection.
f) unix_socket: If unix_socket is not NULL, this string describes the socket or
named pipe that should be used . Note that the "host" parameter determines the type of connection.
g) client_flag: usually 0, but it can also be set to a combination of the following flags to allow specific functions
:
Insert picture description here


Return value
If the connection is successful, the MYSQL* connection handle is returned. If the connection fails, NULL is returned. For a successful
connection, the return value is the same as the value of the first parameter.
Error
CR_CONN_HOST_ERROR
Unable to connect to the MySQL server.
CR_CONNECTION_ERROR
Unable to connect to the local MySQL server.
CR_IPSOCK_ERROR
Could not create IP socket.
CR_OUT_OF_MEMORY
memory overflow.
CR_SOCKET_CREATE_ERROR
Unable to create Unix socket.
CR_UNKNOWN_HOST
could not find the IP address of the host name.
CR_VERSION_ERROR
protocol mismatch, caused by: trying to connect to a server with a specific client library (the client library uses a different
protocol version). This can happen if a very early client library is used to establish a connection with a newer server (not
started with the "–old-protocol" option).
CR_NAMEDPIPEOPEN_ERROR
Cannot create named pipe on Windows platform.
CR_NAMEDPIPEWAIT_ERROR
failed to wait for named pipe under Windows platform.
CR_NAMEDPIPESETSTATE_ERROR
Failed to get pipeline handler under Windows platform.
CR_SERVER_LOST
If connect_timeout> 0, and the time used to connect to the server is longer than connect_timeout
seconds, or the server disappears when the init-command is executed.

3. int mysql_query(MYSQL *mysql, const char *stmt_str)

Function
mysql_query() sends a
query to the currently active database in the server associated with the specified connection identifier . If link_identifier is not specified, the last opened connection is used. If there is no open
connection, this function will try to call the mysql_connect() function without parameters to establish a connection and use it.
The query results will be cached.
Parameters
mysql: The pointer returned by the mysql_init function.
stmt_str: query statement.
Return value
Return 0 if successful, non-zero if an error occurs.
Error
CR_COMMANDS_OUT_OF_SYNC
command execution sequence is incorrect.
CR_SERVER_GONE_ERROR
MySQL server has disappeared. The connection to the server was lost during the
CR_SERVER_LOST
query.
CR_UNKNOWN_ERROR
An unknown error occurred.

4. MYSQL_RES *mysql_use_result(MYSQL *mysql)

Role
For each query (SELECT, SHOW, DESCRIBE, EXPLAIN) that successfully retrieves data, mysql_store_result() or mysql_use_result() must be called. mysql_use_result() will initialize the result set retrieval, but it does not actually read the result set to the client like mysql_store_result(). It must retrieve each row separately through a call to mysql_fetch_row(). This will read the result directly from the server instead of storing it in a temporary table or local buffer. Compared with mysql_store_result(), it is faster and uses less memory. The client only allocates memory for the current line and communication buffer, and the allocated memory can be increased to max_allowed_packet bytes.
Parameters
mysql: The pointer returned by the mysql_init function.
Return value
MYSQL_RES result structure. If an error occurs, NULL is returned.

5. MYSQL_RES *mysql_store_result(MYSQL *mysql)

Role
For each query (SELECT, SHOW, DESCRIBE, EXPLAIN, CHECK TABLE, etc.) that successfully retrieved data, mysql_store_result() or mysql_use_result() must be called. For other queries, there is no need to call mysql_store_result() or mysql_use_result(), but if mysql_store_result() is called under any circumstances, it will not cause any harm or performance degradation. By checking whether mysql_store_result() returns 0, you can check whether the query has no result set (more on that later). If you want to know whether the query should return a result set, you can use mysql_field_count() to check "mysql_field_count()". mysql_store_result() reads all the results of the query to the client, allocates a MYSQL_RES structure, and places the result in this structure. If the query does not return a result set, mysql_store_result() will return a Null pointer (for example, if the query is an INSERT statement). If reading the result set fails, mysql_store_result() will also return a Null pointer. By checking whether mysql_error() returns a non-empty string, whether mysql_errno() returns a non-zero value, or whether mysql_field_count() returns 0, you can check whether an error has occurred. If no rows are returned,
an empty result set will be returned . (The empty result set setting is different from the null pointer as the return value). Once you call mysql_store_result() and get a result that is not a Null pointer, you can call mysql_num_rows() to find
The number of rows in the result set. You can call mysql_fetch_row() to get the row in the result set, or call mysql_row_seek() and mysql_row_tell() to get or set the current row position in the result set. Once you have completed the operation on the result set, you must call the mysql_free_result()
parameter
mysql: the pointer returned by the mysql_init function.
Return value
MYSQL_RES result collection with multiple results. If an error occurs, NULL is returned.

6. MYSQL_ROW mysql_fetch_row(MYSQL_RES*result)

Function
Retrieve the next row of a result set. When used after mysql_store_result(), if there are no more
rows to retrieve, mysql_fetch_row() returns NULL. When used after mysql_use_result(), when there are no more rows to retrieve or if an error occurs, mysql_fetch_row() returns NULL.
The number of values ​​in the row is given by mysql_num_fields(result). If row stores the
value returned from a call to mysql_fetch_row(), the pointer to the value
is accessed as row[0] to row[mysql_num_fields(result)-1]. The NULL value in the row is indicated by the NULL pointer.
The length of the field value in the row can be obtained by calling mysql_fetch_lengths().
Both empty fields and fields containing NULL have a length of 0; you can distinguish them by checking the pointer of the value. If the pointer is NULL, the
field is NULL; otherwise, the field is empty.
Parameter
result: returns a result of the next row with a structure of MYSQL_ROW, or
NULL if there is no row to be retrieved or an error occurs .
Return value
Returns a result of the next row with a structure of MYSQL_ROW. If there is no row to be retrieved or an error occurs
, NULL is returned.
Errors
Calling mysql fetch_row() will not reset the error
CR_SERVER_LOST
The connection to the server is lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

7. void mysql_free_result(MYSQL_RES *result)

Function
Release the
memory allocated for the result set by mysql_store_result(), mysql_use_result(), mysql_list_dbs(), etc. After using the result set, you must release the memory it used by calling mysql_free_result().
After releasing the result set, do not try to access the result set.
Parameters
result: result set
Return value
None

8. void mysql_close(MYSQL *mysql);

Function
Close the previously opened connection. If the handler is automatically allocated by mysql_init() or mysql_connect()
, mysql_close() will also release the connection handler pointed to by mysql.
Parameters
mysql: The pointer returned by the mysql_init function.
Return value
None

2) Environmental installation

apt-get install libmysqlclient-dev

Then mysql.h in the /usr/include/mysql/ directory is the main API interface header file

Insert picture description here

3) Data structure prototype

MYSQL: mysql database connection handle. Before performing any database operations, you first need to create a
MYSQL structure.
MYSQL_RES: The result returned by the execution of the query statement (SELECT, SHOW, DESCRIBE, EXPLAIN).
MYSQL_ROW: Used to represent a row of data in the returned result. Since the data format of each row is inconsistent, this
structure is used for unified representation. Call mysql_fetch_row() to return a
MYSQL_ROW structure from MYSQL_RES .
MYSQL_FIELD: Metadata used to represent a field information (metadata, that is, data describing data), including
field name, field type, and field size. MYSQL_FIELD does not contain the value of
the field (MYSQL_ROW actually saves the value of each field).
MYSQL_FIELD_OFFSET: The index value of field in row, starting from 0.

typedef struct st_mysql {
	NET net; //通讯参数,网络相关
	gptr connector_fd; //加密套接字协议层
	//主机名, 数据库用户名,密码,Unix 套接字,版本,主机信息
	char *host,*user,*passwd,*unix_socket,*server_version,*host_info,*info,*db;
	unsigned int port,client_flag,server_capabilities;
	unsigned int protocol_version;
	unsigned int field_count; //字段个数
	unsigned int server_status; //数据库状态
	unsigned long thread_id; //数据库服务器中的连接 ID
	my_ulonglong affected_rows;
	my_ulonglong insert_id; //下一条记录的 ID
	my_ulonglong extra_info;
	unsigned long packet_length;
	enum mysql_status status;
	MYSQL_FIELD *fields; //字段列表
	MEM_ROOT field_alloc;
	my_bool free_me; //是否关闭
	my_bool reconnect; //是否自动连接
	struct st_mysql_options options;
	char scramble_buff[9];
	struct charset_info_st *charset;
	unsigned int server_language; //数据库语言
} MYSQL; //MYSQL 句柄
typedef struct st_mysql_res
{
	my_ulonglong row_count; // 结果集的行数
	unsigned int field_count, current_field;// 结果集的列数,当前列
	MYSQL_FIELD *fields; // 结果集的列信息
	MYSQL_DATA *data; // 结果集的数据
	MYSQL_ROWS *data_cursor; // 结果集的光标
	MEM_ROOT field_alloc; // 内存结构
	MYSQL_ROW row; // 非缓冲的时候用到
	MYSQL_ROW current_row; // mysql_store_result 时会用到,当前行
	unsigned long *lengths; // 每列的长度
	MYSQL *handle; // mysql_use_result 会用。
	my_bool eof; // 是否为行尾
} MYSQL_RES; //查询结果集
typedef char **MYSQL_ROW; // 以字符串数组的形式返回数据
typedef unsigned int MYSQL_FIELD_OFFSET; // 当前字段的偏移量量
typedef struct st_mysql_rows
{
	struct st_mysql_rows *next; // 下一条记录
	MYSQL_ROW data; // 当前行的数据
	unsigned long length; // 数据的长度
} MYSQL_ROWS; //mysql 的数据的链表节点。可见 mysql 的结果集是链表结构
typedef struct st_mysql_data
{
	my_ulonglong rows;
	unsigned int fields;
	MYSQL_ROWS *data;
	MEM_ROOT alloc;
} MYSQL_DATA; // 数据集的结构
typedef struct st_mysql_field
{
	char *name; //列名称
	char *table; //如果列是字段,列表
	char *def; //默认值(由 mysql_list_fields 设置)
	enum enum_field_types type; //类型的字段。Se mysql_com。h 的类型
	unsigned int length; //列的宽度
	unsigned int max_length; //选择集的最大宽度
	unsigned int flags; //div 标记集
	unsigned int decimals; //字段中的小数位数
} MYSQL_FIELD; //列信息的结构
typedef struct st_used_mem //结构为 once_alloc
{
	struct st_used_mem *next; //下一个块使用
	unsigned int left; //记忆留在块
	unsigned int size; //块的大小
} USED_MEM; //内存结构
typedef struct st_mem_root
{
	USED_MEM *free;
	USED_MEM *used;
	USED_MEM *pre_alloc;
	unsigned int min_malloc;
	unsigned int block_size;
	void (*error_handler)(void);
} MEM_ROOT; //内存结构

4) Sample code

#include <mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>
int main()
{
	MYSQL *conn;
	MYSQL_RES *res;
	MYSQL_ROW row;
	char server[] = "localhost";
	char user[] = "lingsheng_mysql";
	char password[] = "lingsheng";
	char database[] = "mysql";
	conn = mysql_init(NULL);
	if (!mysql_real_connect(conn, server,user, password, database, 0, NULL, 0))
	{
		fprintf(stderr, "%s\n", mysql_error(conn));
		exit(1);
	}
	if (mysql_query(conn, "show tables"))
	{
		fprintf(stderr, "%s\n", mysql_error(conn));
		exit(1);
	}
	res = mysql_use_result(conn);
	printf("MySQL Tables in mysql database:\n");
	while ((row = mysql_fetch_row(res)) != NULL)
	{
		printf("%s \n", row[0]);
	}
	mysql_free_result(res);
	mysql_close(conn);
	printf("finish! \n");
	return 0;
}