first of all, we should understand one of the important features of MySQL-plug-in storage engine, from the name can be seen in MySQL, users can choose the storage engine according to their own needs, in fact, even in the same database, different tables can use different storage engine. There are many kinds of storage engines supported in MySQL, this can go to Baidu or Google, this article mainly said two more commonly used storage engines, MyISAM and InnoDB. MyISAM is characterized by1)indexes and data are stored separately, MyISAM has three files (. frm files, storage table definitions;. myi store index my index; myd store data My2) does not support transactions and foreign keys3) Support full-text indexing4) Support data compression (MyISAM supports 3 different storage formats, static tables, dynamic tables, compressed tables)5) Select and insert are more efficient, so the MyISAM storage engine is typically used for select and insert-based applications. 6) Table lock
Features of InnoDB1) Support for foreign keys and transactions2) If many are update and delete operations, then choosing InnoDB is appropriate. 3) Data and indexes are stored together. 4) InnoDB does not support full-text indexing5) InnoDB does not save the exact number of rows in the table, that is, when the select count (*) from table is executed, InnoDB will scan through the entire table to calculate how many rows, but MyISAM simply reads out the number of rows saved. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same. 6) Row-level lock
in the case of string storage, it is recommended to use a fixed-length data column if it is MyISAMif using InnoDB, it is recommended to use the varchar data type
================================ Ornate split line ===================================================
Let's briefly mention the selection of the character set in MySQL
generally, if the database just needs to support the general Chinese, then use GBK is enough, do not need to use UFT8, because GBK a Chinese character 2 bytes, utf8 a Chinese character 3 bytes. the MySQL character set has two concepts, one is the character set, the other is the proofing rulescharacter sets are used to define how strings are storedproofing rules are used to define how strings are comparedIf the proofing rules are CI (comparison, case insensitive) CS (case sensitive compared), bin (two, and character encoding)
How do I modify a character set in a database? If there is no data, the direct modification is, but if the data already exists in the database, then how to modify the character set? 1) Export table structure2) Manually modify the exported table structure (one SQL file a.sql)3) Export all records (B.SQL)4) Modify the set names UTF8 in the B.sql5) Create a database with a new character set6) Create a table7) Import data
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL storage engine A little learning experience summary
