MySQL storage engine with data type

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:

Support

Engine		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