MySQL Data Type and SQL model, mysql Data Type SQL

MySQL Data Type and SQL model, mysql Data Type SQL
Myisam does not support transactions and table locks

. Frm: storage table structure

 

 

 

　　

 

　　
. MYD: storage data, short for MYDATA

 

. MYI: storage index, abbreviated as MYIndex

 

 

Innodb (one tablespace file per table) supports transactions, row locks, and foreign keys.
. Frm storage table structure
. Ibd tablespace (storing data and indexes) mysql defaults from storage engine Optimus Innodbmysql storage engine for details, see mysql storage engine connection to mysql
1. How applications connect to the server
Dynamic SQL: establishes a connection with the database service through a function or method, and then sends the statement to the server through the Protocol
Embedded SQL: directly connect to the server through APIS 2. There are multiple protocols available when the client connects to the server
-- Protocol = tcp | socket | pipe | memory)

 

Mysql is actually started using mysql_safe (mysql is started using a secure thread)
When mysql is started, it will find the configuration file in multiple locations
/Etc/my. cnf -->/etc/myql/my. cnf -->/my. cnf --> -- default-extra-file = file (specified configuration file) -->/my. cnf: if multiple configuration files are found, the final result will take effect.

# Mysqld -- help-verbose view default mysqld Parameters

Mysql. cnf configuration file
[Mysqld] Only effective for mysqld
[Client] takes effect for all clients
[Mysql] only applies to mysql

 

The SQL model has different processing mechanisms:

Ansi quotes: Double quotation marks (")" are the same as the backslash ('') and can only reference field names and table names. Single quotes ''can only reference strings
IGNORE_SPACE: Ignore Extra blank characters in the built-in Function
STRICT_ALL_TABLES: Any violation of data rules is not allowed and an error is returned.
STRICT_TARNS_TABLES: it is not allowed to insert invalid data into a table that supports transactions, and an error is returned.

View the SQL model show global variables like 'SQL _ mode' to view global variables.


Mysql Server Variables

Scope Division:
Global variable: view the global variable show global variables.
Session variable: view the session variable show [session] variables,
When a session ends, the session variable becomes invalid. When the session variable conflicts with the global variable, the session variable overwrites the global variable.

Effective Time:
Dynamic: effective immediately, only valid for the current session (modify global does not affect the current session variable, only valid for the session variable of the new connection)
Static: the restart takes effect. Static Parameters are written to the configuration file or passed to mysqld through parameters.

Call server variables (built-in variables ):@@
Call User-Defined variables :@
Display variables: select @ global. SQL _mode: displays the global SQL model.
Select @ session. SQL _mode displays the session SQL model

Set variable: set {global | session} variable name = 'value'

 

Show character set; view character sets supported by mysql
Show collaction; view mysql sorting rules

Data Type (unisined indicates the unsigned type)

Integer:
Type occupies the maximum value of the minimum byte value
TINYINT 1 signed-128, unsigned 0 signed 127, unsigned 255
SMALLINT 2 signed-32768, unsigned 0 signed 32767, unsigned 65535
MEDIUMINT 3 signed-8388608, unsigned 0 signed 8388607, unsigned 1677215
INT and INTEGER 4 are signed-2147483648, unsigned 0 is signed 2147483647, and unsigned 4294967295
BIGINT 8 signed-9223372036854775808, unsigned 0 signed 9223372036854775807, unsigned 18446744073709551615

Floating point type:
Type bytes
FLOAT (g, f) 4 or 8
G: The total number of characters.
F: number of characters after the decimal point

DOUBLE 8
Description: When the decimal point must be precise to more than 10 digits, the DOUBLE type is used.

Number of points:
Maximum Minimum value of Type bytes
DEC (M, D) and DECIMAL (M, D) M + 2
Note: Valid values are determined by M and D. Floating Point storage is an approximate value, and fixed point storage is a string.

Bit type:
Maximum Minimum value of Type bytes
BIT (M) 1-8 BIT (1) BIT (64)
Description: The number of bytes is M and the value range is 1-8.

Date and Time type:
Maximum Minimum value of Type bytes
DATE 4 1000-01-01 9999-12-31
DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59
TIMESTAMP 4 19700101080001
TIME 3-835: 59: 59 838: 59: 59
YEAR 1 1901 2155

String type:
CHAR series:
Type byte description
The length of an integer CHAR column whose char (M) M is 0-255 is fixed to the length declared during table creation.
The VARCHAR (M) M is an integer between 0 and 255. The value of the VARCHAR column is variable. If the length is smaller than 255, one more character must be used as the Terminator. If the length exceeds characters, two more characters must be used as the Terminator.

　　
TEXT Type:
Type byte description

The length of TINYTEXT 0-255 is + 2 bytes.
The length of TEXT 0-65535 is + 2 bytes.
The length of MEDIUMTEXT 0-167772150 is + 3 bytes.
The length of the LOANGTEXT 0-4294967295 value is + 4 bytes
Description: stores a large number of strings.

BINARY series:
Type byte description
BINARY (M) can be 0-M
VARBINARY (M) can be 0-M
Description: stores binary data, and VARBINARY can be of a variable length.

BLOB series:
Type bytes
TINYBLOB 0-255
BLOB 0-2 ^ 16
MEDIUBLOB 0-2 ^ 24
LOANGBLOB 0-2 ^ 32
Description: stores a large amount of binary data.

Enum ("value1", "value2",...) maximum value 65535 this type of column can only accommodate one of the listed values or is empty
Set ("value1", "value2",...) maximum value 64 this type of column can accommodate a group of values or is empty