1 Data storage Engine
The concept of a storage engine is a feature of MySQL that specifies the type of table (such as how the table stores and indexes data, whether transactions are supported, foreign keys, and so on), and how the table is stored on the computer.
1.1 MySQL-supported data storage engine
View engine Information
To view engine information by command
show engines;
The default storage engine is InnoDB, as listed below:
Engine |
| Support
Comment |
Transactions |
XA |
savepoints |
InnoDB |
DEFAULT |
Supports transactions, Row-level locking, and foreign keys |
YES |
YES |
YES |
Mrg_myisam |
YES |
Collection of identical MyISAM tables |
NO |
NO |
NO |
MEMORY |
YES |
Hash based, stored in memory, useful for temporary tables |
NO |
NO |
NO |
Blackhole |
YES |
/dev/null Storage Engine (anything you write to it disappears) |
NO |
NO |
NO |
MyISAM |
YES |
MyISAM Storage Engine |
NO |
NO |
NO |
Csv |
YES |
CSV Storage Engine |
NO |
NO |
NO |
ARCHIVE |
YES |
Archive Storage Engine |
NO |
NO |
NO |
Performance_schema |
YES |
Performance Schema |
NO |
NO |
NO |
Federated |
NO |
Federated MySQL Storage Engine |
Null |
Null |
Null |
Support Indicates whether the engine is supported, comment represents a comment about the engine, transactions indicates whether the engine supports transactions, and XA indicates whether the distribution supported by this engine complies with the XA specification, savepoints indicates whether the engine supports savepoint in the transaction.
It can also be viewed through show variables, with the following commands
show variables like ‘have%‘;
The results appear as follows:
variable_name |
Value |
Have_compress |
YES |
Have_crypt |
NO |
Have_dynamic_loading |
YES |
Have_geometry |
YES |
Have_openssl |
DISABLED |
Have_profiling |
YES |
Have_query_cache |
YES |
Have_rtree_keys |
YES |
Have_ssl |
DISABLED |
Have_statement_timeout |
YES |
Have_symlink |
YES |
Yes for support, no for unsupported, disabled for support but not open
View the default storage engine
show variables like ‘%storage_engine%‘;
The query results are as follows:
variable_name |
Value |
Default_storage_engine |
InnoDB |
Default_tmp_storage_engine |
InnoDB |
Disabled_storage_engines |
|
Internal_tmp_disk_storage_engine |
InnoDB |
1.2 Modifying the default engine
Method 1: Modify the My.ini configuration file
Add or modify Default-storage-engine in the My.ini file in the installation directory, such as setting the default engine to InnoDB, and adding the following line to the My.ini file:
Restart the MySQL service in effect.
Mode 2: Set by command
Generally we can set the engine when we create the table
CREATE TABLE `test` ( `db` double DEFAULT NULL, `dm` decimal(10,0) DEFAULT NULL, `gender` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
When the creation is complete and we want to modify the table's engine, we can execute the following command:
alter table table_name engine= engine_name;
For example, if we want to set the engine of table test to MyISAM, we can execute:
1.3 Selection of storage engines
Choose a suitable storage engine to perform better database functions. Because the various data engines have different characteristics advantages and applications, it is necessary to understand their characteristics before they can help us to better choose the right engine.
Several common engines and their features are listed below:
MyISAM
This storage engine does not support transactions, foreign keys, but is fast, does not require transactional integrity, is suitable for transactions without requirements, and concurrency is relatively low to access the primary occasion.
InnoDB
Transactional, transactional security with commit, rollback, and crash recovery, with less write processing and more disk space to preserve data and indexes than MySQL. It is suitable for use in situations where it is necessary to perform frequent update deletions, to implement concurrency control, and to support transactions.
MEMORY
The storage engine uses content that exists in memory to create tables, each of which actually corresponds to a disk file, in the form of. frm; This type of table is very fast because the data is in memory and the hash index is used by default, but once the service is closed, the data in the table is lost. This engine is suitable for situations where the volume of data involved is small and requires quick access.
2 data types
MySQL provides data types that define the types of data we store, with different data types that have different storage capabilities and the amount of space they occupy. The data types provided by MySQL are: integer type, floating-point type and fixed-point number type, bit type, string type, date and time type.
2.1 Integer Type
|
|
|
range (unsigned) |
TINYINT |
1 |
(-128,127) |
(0,255) |
SMALLINT |
2 |
(-32 768,32 767) |
(0,65 535) |
Mediumint |
3 |
(-8 388 608,8 388 607) |
(0,16 777 215) |
int or integer |
4 |
(-2 147 483 648,2 147 483 647) |
(0,4 294 967 295) |
BIGINT |
8 |
(-9 233 372 036 854 775 808,9 223 372 036 854 775 807) |
(0,18 446 744 073 709 551 615) |
For integer types, we need to determine which type we choose to store based on the actual size of the data being stored. For example, when the size of the data to be stored does not exceed 255, we choose tinyint to be OK, both to meet the requirements and save memory.
When used, when we do not know the scope of that type, we can see through the help command, such as view int type, we can type? int (or help int;) to view the details of the Int. As shown below:
2.2 Floating-point type, fixed-point number type, bit type
Floating-point type
type |
bytes |
Range (signed) |
Range (unsigned) |
function |
FLOAT |
4 |
( -3.402 823 466 e+38,1.175 494 351 E-38), 0, (1.175 494 351 e-38,3.402 823 466 351 e+38) |
0, (1.175 494 351 e-38,3.402 823 466 e+38) |
Single-precision floating point value |
DOUBLE |
8 |
(1.797 693 134 862 315 7 e+308,2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+3 08) |
0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308) |
Double-precision floating-point value |
DECIMAL (M,D) |
M+2 |
Values that depend on M and D |
Values that depend on M and D |
High precision Small value |
With regard to decimal, special mention :
- M is the maximum number (precision) of the number. The default value with a range of 1~65,m is 10. The value of M is the length of the integer plus the length of the decimal, and does not contain the decimal point with the "-" minus sign.
- D is the number of digits to the right of the decimal point (scale). Its scope is 0~30, but must not exceed M. If d omit does not write then the default is 0, when there is no fractional part.
- Storage integer part length is m-d (error not satisfied), fractional length is D (parts greater than d are truncated)
About usage :
For the choice of floating-point type and decimal, when we ask for decimal precision, we recommend using the decimal type, because the decimal type stores the string, and the floating-point type stores the approximate value. For low precision requirements, depending on the range of stored data.
Bit type
bit type |
bytes |
maximum value (bit) |
minimum value (bit) |
BIT (M) |
1~8 |
BIT (64) |
BIT (1) |
The number of bytes occupied by the bit type BTM is M, and the value is between 1~8. The storage size of a bit type depends on its precision.
You can use the B ' value ' notation to write bit field values. Value is a binary value written in 0 and 1. As follows:
2.3 String
Char Series String type
type |
bytes |
Description |
CHAR (M) |
M |
M is an integer between 0~255 |
VARCHAR (M) |
M |
M is an integer between 0~65535 |
Use varchar when stored data changes frequently, otherwise char is used
Binary string
type |
bytes |
Description |
BINARY (M) |
M |
Length 0~m bytes of fixed length byte string |
VARBINARY (M) |
M |
Length 0~m bytes Long byte string, length of Value + 1 bytes |
Similar to the char system, the binary can store binary data (slices, video, music), whereas Char systems can store only character data.
text system String Type
type |
bytes |
Description |
Tinytext |
0~255 bytes |
Length of Value + 2 bytes |
TEXT |
0~65535 bytes |
Length of Value + 2 bytes |
Mediumtext |
0~167772150 bytes |
Length of Value + 3 bytes |
Longtext |
0~4294967295 bytes |
Length of Value + 4 bytes |
When you need to store a large amount of character data, you can select the text string, and the length of the stored character to choose which one to look at.
Blob system string
type |
bytes |
Tinyblob |
0~255 bytes, length of Value + 1 bytes |
Blob |
0~65535 bytes, length of Value + 2 bytes |
Mediumblob |
0~167772150 bytes, length of Value + 3 bytes |
Longblob |
0~4294967295 bytes, length of Value + 4 bytes |
Similar to the text system, the BLOB system can store binary data (slices, video, music), while the text system can store only character data
2.4 Date and Time type
type name |
bytes |
format |
Range |
Date |
3 |
Yyyy-mm-dd |
1000-01-01 ~ 9999-12-31 |
Datetime |
8 |
YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
Timestamp |
4 |
YYYY-MM-DD HH:MM:SS |
1970-01-01 00:00:01 ~ 2038 |
Year |
1 |
YYYY |
1901 ~ 2155 |
Time |
3 |
|
-838:59:59 ~ 838:59:59 |
by command select Curdate (); or select Now (); To view the current date
Use of date and time types
- If you want to represent year and month days, use date generally
- If you want to represent the day of the month and seconds, you typically use datetime
- If you want to update the current time frequently, you can choose to use the timestamp
- If you want to represent years, you can use year and consume less bytes
- If you want to represent a minute or seconds, you can use the time
Using the example
PostScript: Summarize the next MySQL storage engine and data type, more specific usage and then gradually improve it ~
MySQL storage engine with data type