Summary of mysql storage engine _ MySQL

First, we should understand an important feature of the plug-in storage engine in mysql. from the name, we can see that in mysql, users can choose any storage engine based on their own needs. In fact, this is also the case, even in the same database, different tables can use different storage engines. First, we should understand an important feature in mysql-the plug-in storage engine, from the name, we can see that in mysql, users can choose the storage engine as needed. In fact, this is also the case, even in the same database, different storage engines can be used for different tables. Mysql supports many storage engines, which can be baidu or google. This article mainly describes two commonly used storage engines, myisam and innodb. Myisam features 1) separated storage of indexes and data. myisam has three files (. frm file, storage table definition ;. MYI storage index my index; MYD storage data my data) 2) does not support transactions and foreign keys 3) supports full-text indexing 4) supports data compression (myisam supports three different storage formats, static tables, dynamic tables, and compressed tables) 5) Select and Insert are highly efficient. Therefore, the myisam storage engine is generally used for Select and insert-based applications. 6) table lock
Innodb features 1) support for foreign keys and transactions 2) if many are update and delete operations, it is appropriate to select innodb. 3) data and indexes are stored together. 4) innodb does not support full-text indexing 5) InnoDB does not store the specific number of rows of the table, that is, when you execute select count (*) from table, innoDB needs to scan the entire table to calculate the number of rows, but MyISAM simply needs to read the number of rows saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same. 6) row-level lock
For string storage, if myisam is used, we recommend using a fixed-length data column. if innodb is used, we recommend using the varchar data type.
============================ ========================================================== =====
The following describes how to select character sets in mysql.
This is generally the case. if the database only needs to support General Chinese characters, gbk is enough. uft8 is not required because gbk contains two Chinese characters, utf8 contains three Chinese characters. Mysql character set has two concepts: one is character set, the other is to proofread the rule character set to define the way to store strings. The proofread rule is used to define the comparison method of strings. if the collation is ci (when compared, case insensitive) cs (case sensitive), bin (binary, related to character encoding)
How can I modify the character set in a database? If there is no data, you can directly modify it. but if there is already data in the database, how can we modify the character set? 1) export table structure 2) manually modify the exported table structure (an SQL file. SQL) 3) export All Records (B. SQL) 4) modify B. SQL set names utf8 5) use the new character set to create a database 6) create a table 7) import data