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, for example, 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, 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 :

• M is the maximum number (precision) of the number. The range is 1~65. The default value for M is 10. The value of M is the length of the integer plus the length of the decimal, excluding the decimal point and the "-" minus sign.
• D is the number of digits on the right side of the decimal point (scale).

  Its scope is 0~30, but must not exceed M. Assuming that D ellipsis does not write, it defaults to 0. There is no fractional part at this time.

• The stored integer part length is m-d (error is not met). Fractional length is D (parts greater than d are truncated)

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