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, for example, 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, and comment represents a comment about the engine. The transactions ratio indicates whether the engine supports transactions. XA indicates whether the distribution supported by this engine complies with the XA specification, and savepoints indicates whether the engine supports savepoint in the transaction.
can also be viewed through show variables commands such as the following
show variables like ‘have%‘;
Show results such as the following:
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%‘;
Query results such as the following:
variable_name |
Value |
Default_storage_engine |
InnoDB |
Default_tmp_storage_engine |
InnoDB |
Disabled_storage_engines |
|
Internal_tmp_disk_storage_engine |
InnoDB |
1.2 Changing the default engine
Method 1: Change the My.ini configuration file
Add or change default-storage-engine in the My.ini file under the installation folder, 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 created, we want to change the engine of the table to be able to run such as the following command:
alter table table_name engine= engine_name;
For example, we want to set the engine of the table test to MyISAM and be able to run:
1.3 Selection of storage engines
Choose a suitable storage engine, ability to play the database better function. Because various data engine has different characteristic superiority and the application occasion. Therefore, we need to understand their characteristics first. Talent helps 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. However, the access is fast, the transaction integrity is not required, suitable for the transaction is not required, concurrency is relatively low, to visit the main occasions.
InnoDB
Support transactions, with commit, rollback, crash recovery transaction security. Compared to MySQL, write processing is less powerful and takes up more disk space to preserve data and indexes. Frequent updates are required to delete the operation. Implement concurrency control, which is appropriate for applications that need 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 applications where the volume of data involved is small and requires high-speed 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
type |
bytes |
Range (signed) |
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 stored. For example, when the size of the data to be stored does not exceed 255, we choose tinyint to be OK. Both meet the requirements and save memory.
When we use it, when we don't know the scope of that type, we can see it through a help command. such as looking at type int, can we type? int (or help Int.) ) to view the specific information for the Int. For example, see the following:
Zerofill (0 fill)
Shaping the 0 padding makes the type self-unsigned, and the individual shaping types have a default fill bit. For example, with:
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+308) |
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 :
about how to use :
For the choice of floating-point type and decimal, when we have very high decimal precision requirements. The decimal type is recommended at this time. Because the decimal type stores a string, the floating-point type stores an approximate value. For cases where the accuracy requirement is not high. Depends on the extent of the data stored.
do for example one of the following tests :
Create a table that has a float type of f_data, a double type of db_data, and a decimal type of dm_data. and insert between 123.456 and three fields at the same time.
Then we query 123.456 F_data when we find that the query is empty, and query ' 123.456 ' dm_data when the results are queried. The experiment verifies the conclusion that float stores approximate values. So the query is not. Decimal stores a string, so it can be queried exactly.
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.
Ability to write bit field values using the B ' value ' notation.
Value is a binary value written in 0 and 1. For example, the following:
2.3 String
2.3.1 Char Series string type
type |
bytes |
Descriptive narrative |
CHAR (M) |
M |
M is an integer between 0~255 |
VARCHAR (M) |
M |
M is an integer between 0~65535 |
When the data storage range is appropriate, the recommended varchar is used when the stored data is often changed. Otherwise, use char.
2.3.2 Binary string
type |
bytes |
Descriptive narrative |
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 is stored in binary data (slices, video, music). The char system can only store character data.
2.3.3 Text system string Type
type |
bytes |
Descriptive narrative |
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 number of character data, you can choose the text string, as to which one to look at the length of the stored characters.
2.3.4 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 difference is that the BLOB system can store binary data (slices, video, music), while the text system stores only character data
2.3.5 enumeration Types
ENUM (' value1 ', ' value2 ',...)
An enumeration type can only have a string of values, from the Value column ' value1 '. ' value2 ', ..., NULL in or special "error value selected. An enum column can have a maximum of 65,535 distinct values.
Enum values are internally represented by integers, so we can use integer insertions or queries to replace strings.
For example, we create a table that includes the enumeration field day, and we insert the enumeration value
We can also insert and query by integer values
An error occurs when we insert a non-set enumeration value, such as the following error:
into t_enum values(‘星期天‘);ERROR1265 (01000for‘day‘ at row 1
2.3.6 Set Type
SET (' value1 ', ' value2 ',...)
A set set. The string object can have a combination of 0 or more values. Each value must come from a column value of ' value1 ', ' value2 '. ... The set column can have a maximum of 64 members. Set values are internally represented by integers.
The order of the data at the time of insertion is independent of the order in which the tables are finally made.
Use the Demo sample :
A t_set table was created. Day is a set-type field that can be inserted into a combination of arbitrary values in a set. The insertion order does not affect the data that is finally inserted into the table. Can be queried by specifying a string, the order of this string has an effect on the query result, which is a strange place
Insert, query, delete, update using integers
The set internally uses integers to represent the corresponding relationships of value, binary, and decimal, such as the following
When we insert the number 15 o'clock, that is 1+2+4+8. Value corresponding to each set (Monday ~ Thursday)
When we insert the number 8 o'clock, which is the corresponding value 8 ' Thursday ', the other combinations and so on
Delete the specified value (the update is similar to, not listed)
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
- Suppose you want to represent a month and a day, typically using date
- If you want to represent the day of the month and seconds, you typically use datetime
- Suppose you want to update the current time frequently. You can choose to use timestamp
- Assume that you want to represent years, be able to use year, consume less bytes
- If you want to represent seconds and minutes, you can use the time
Using the Demo sample
PostScript: summarizes the storage engine and data types under MySQL. More specific ways to use it again slowly intact ~
MySQL storage engine with data type