mysql storage engine and data types (2)_MySQL

Storage engine:
1. View supported storage engines:
Show engines G;
Show variables like ‘have%’;
2. View the default storage engine:
Show variables like ‘storage_engine%’;
3. Modify the default storage engine:
① The installation version can be installed through the wizard:
“Start” —> “Program” —> “MySQL” —> “MySQL Server 5.5” —> “MySQL Server Instance Configuration Wizard” to enter the welcome page of the graphical instance configuration wizard. In the graphical instance configuration wizard, click the "next" button to enter "MySQL Select Usage Type". If you select the "Multifunctional Database" radio button on this page, the default storage engine is InnoDB. If you select the "Non-Transaction Database Only" radio button, the default storage engine is MyISAM.
② Installed version or installation-free version Another way to modify the default engine:
Open the my.ini configuration file and add the configuration default-storage-engine=INNODB

to the content of the [mysqld] group

Data type:
The database management system provides integer types, floating point types, fixed point types and bit types, date and time types, and string types.

1. Integer type:
tinyint(1 byte) smallint(2 byte) mediumint(3 byte) int and integer(4 byte) bigint(8 byte)
If you cannot distinguish the representation range of each integer, you can view the relevant information by viewing the mysql system help.
•mysql> help contents;
• mysql> help Data Types;
•mysql> help int;

2. Floating point type, fixed point type, bit type:
•Floating point number type: float (4 bytes) double (8 bytes) If you need to be accurate to more than ten digits after the decimal point, you need to select double
•Fixed-point type: dec(M,D)(M+2 bytes) decimal(M,D)(M+2 bytes)
Choose when very high decimal accuracy is required, usually indicating amounts, etc. Decimal is preferred
•Bit type: bit (1-8 bytes)
Explanation: decimal(18,4) has a total length of 18 digits, including 1 decimal point and 4 decimal digits, that is to say, 18-1-4=13 integer digits and only 13 digits M: Total length D: Number of decimal digits

3. Date and time type:
•data(4 bytes)
•datatime(8 bytes)
•timestamp(4 bytes)
•time(3 bytes)
•year(1 byte)
Each date and time data type has a range of values, and if a value inserted exceeds the range of that type, a default value is inserted.
•If you want to express the year, month and day, you will generally use date
•If you want to express the year, month, day, hour, minute and second, you will generally use datetime
•If you need to frequently insert or update the date to the current system time, you will generally use the timestamp type
•If you want to express hours, minutes and seconds, you usually use time
•If you want to represent the year, you will generally use year, because this type takes up less space than date

4. String type:
char series string:
•char(M) M bytes M is an integer between 0-255
•varchar(M) M bytes M is an integer between 0-65535 and the length is variable
Note: UTF-8: One Chinese character = 3 bytes, English is one byte; GBK: One Chinese character = 2 bytes, English is one byte

text series string type:
•tinytext (0-255 bytes)
•text(0-65535 bytes)
•mediumtext(0-167772150 bytes)
•longtext(0-4294967295 bytes)

binary series string type:
•binary(M) M is bytes and the allowed length is 0-M
•varbinary(M) M is bytes and the allowed length is 0-M

Remarks: The difference between these two types and the previous char and varchar types is that the char group stores character data, while binary can store binary data (pictures, music, videos)
blob series string type:
•tinyblob 0-255 bytes
•blob 0-2 raised to the 16th power
•mediumblob 0-2 to the 24th power
•longblob 0-2 to the 32nd power
Note: The difference between this group and text type is that this group can store binary data (pictures, music, videos), while the text group can only store character data

The above is the entire content of this article. I hope it will be helpful to everyone’s study and I hope you will support me a lot.