MySQL Technology Insider (SQL Programming)-data type

One: MySQL storage engine

The MySQL plug-in storage engine allows developers at the storage engine layer to design the storage tier they want (meeting transactional requirements, meeting data in memory medium), and the common storage Engine is as follows:

InnoDB Storage Engine: An application that supports transactions and is intended for online transaction processing. The feature is the row lock design, the support foreign key, the default read operation does not produce the lock. The most commonly used engine, the default storage engine after version 5.5.8.

MyISAM: table lock Design, support for full-text indexing , no transaction support, OLAP database application, 5.5. The default storage engine was used before version 8. Its buffer pool caches only index files and does not cache data files.

NDB: The cluster storage engine, which is the share-nothing cluster architecture, provides more advanced high availability. The data is all in memory, and the primary key lookup is extremely fast, targeting the OLTP database application type.

Memory: The data is all put in RAM, the data in the restart or crash table disappears, and the hash index is used by default instead of the B + Tree index.

Review 1: Types of closures

• Write lock (exclusive, x Lock): T adds a write lock to a, only allows T to read and write a, the other transaction cannot lock a, until T releases the lock on a, which reduces the parallelism of the whole system;
• Read lock (Shared lock, S lock): T can only read can not change a, other transactions only to a plus s lock, until T release)

Review 2: Blockade granularity

In order to implement concurrency control in the database, blocking technology is used to block the size of objects called blocking granularity (granularity).

• Table Lock: Low overhead, lock the whole table, write operation to get write lock, blocking other users read and write operations.
• Row locks: High overhead, lock on the specified record, other processes can manipulate other data in the table, to a large extent support concurrent processing.

Two: Data type 1. UNSIGNED:

The number type is not symbolized. int type range: -2^31~2^31-1 int unsigned range: 0~2^32-1

Seemingly suitable for the type of primary key self-growth, but the actual application has negative effects, for example: CREATE TABLE ' t1 ' (' a ' int (unsigned), ' B ' int (ten) unsigned);

When inserted, the SELECT a-B from T1 will error: [ERR] 1690-bigint UNSIGNED value is out of range ...

Cause: A-B has a hexadecimal result of 0xFFFFFFFF, 4294967295 for unsigned integer values (integer maximum), and for signed integers, the sign bit 1 is negative, plus 1, and the final result is-1.

How to resolve:

SET sql_mode= ' no_unsigned_subtraction ';

SELECT A-B from T1;

Recommendation: as far as possible without unsigned, which may bring some unexpected effects, and the type of int can not store data, int unsigned also may not be able to store, it is better to use bigint.

2.ZEROFILL:

' A ' int (ten) unsigned DEFAULT NULL: for int (10), there is no Zerofill this attribute, the number in parentheses is meaningless.

' A ' int (4) unsigned zerofill, inserting 1 o'clock A will result in 0001, and a width of less than 4 will automatically fill 0.

Once the Zerofill property is enabled, the MySQL database automatically adds the unsigned property for the column.

3.sql_mode settings:

The default is null, which can allow some illegal operations, such as inserting null into a NOT null field, or inserting an illegal date. Production environment It is highly recommended to set this value to strict mode (strict mode is to set Sql_mode to at least one of strict_trans_tables or strict_all_tables).

SELECT @ @session. Sql_mode (view current session Sql_mode settings)

SELECT @ @global. Sql_mode (view current global sql_mode settings)

Set to Strict mode: Set GLOBAL sql_mode= ' Strict_trans_tables ';

4. Date and Time functions:

DATETIME (8 bytes): Range "1000-01-01 00:00:00" to "9999-12-31 23:59:59"

DATE (3 bytes): Range "1000-01-01" to "9999-12-31"

TIMESTAMP (4 bytes): Range "1970-01-01 00:00:00" to "2038-01-19 03:14:07" the actual number of milliseconds to store "1970-01-01 00:00:00" to the current time.

Year (1 bytes): Can specify width, year (4) range 1901-2155,year (2) Range 1970-2070 (00-69 for 2000-2069)

Time (3 bytes): Range " -838:59:59"-"838:59:59". This type can hold the time of day, and can also save the time interval

the difference between timestamp and datetime:

In addition to the time range, there are: timestamp can set the default value when the table is built, DateTime does not; When you update a table, the timestamp type of column can be automatically updated to the current time.

Build a case:

' Gmt_create ' timestamp not NULL DEFAULT ' 0000-00-00 00:00:00 ',

' gmt_modified ' timestamp not NULL DEFAULT current_timestamp on UPDATE current_timestamp

Note: The gmt_modified field will not be updated if the value has not been updated after the update operation.

Time function:

1.SELECT Now (),current_timestamp(),sysdate(), SLEEP (2), now (), Current_timestamp (), Sysdate ();

Current_timestamp, same as now, returns the time the SQL statement was executed; Sysdate returns the time it takes to execute to the current function

2.date_add and date_sub: SELECT Date_add (Now (), INTERVAL-30 Day), available data types: microsecond, SECOND, MINUTE , HOUR, WEEK, MONTH

3.date_format: SELECT date_format (now (), '%m/%d/%y '); 07/06/2016

Note: SELECT Date_format (now (), '%y-%m-%d ') = ' 2017-01-01 '; the problem is that there is usually an index on the date type in the table, and if you use the above statement, the optimizer will never use the index. It is also not possible to query data through an index, so this query is very inefficient to execute.

5. Number types and character types:

1. Single-precision float and double-precision doubles are non-precise types and do not guarantee the correctness of the operation. FLOAT (m,d): M indicates that the value displays M bits, D is the number of digits after the decimal point, and is rounded when the value is saved.

2.DECIMAL and numeric are considered the same type, which is used in calculations that require very high precision, and MySQL stores the decimal data type as strings internally, preserving their values more precisely.

3. Setting of the character set for the column: a VARCHAR (ten) CHARSET GBK;

4. Naming of collations: _ci end indicates size insensitivity (case insensitive) _cs is case sensitive (sensitive) _bin represents a binary comparison. The default collation for the UTF8 character set is utf_general_ci, which is case insensitive. When a and a are inserted, they are returned as a consistent character (SELECT ' a ' = ' a '; shown as 1). "Fields should be case sensitive when the user fills in the registration name in the relevant scenario." Modify Collation: ALTER TABLE t MODIFY COLUMN a VARCHAR (ten) COLLATE Utf8_bin; Collations also affect indexes: When using UTF_GENERAL_CI, column T contains a and a, then creating a unique index prompts for duplicate data.

5.CHAR (N): holds a fixed-length string. The range of n is 0-255. VARCHAR (N): Saves the variable-length character type. The range of n is 0-65535. n all represent the length of the character, not the length of the byte. For char, the database automatically fills the right side of the storage column until the length is N, and the populated string is automatically deleted when it is read. The length function represents the number of bytes occupied by a string, and char_length represents the length of the character.

6.VARCHAR needs to be stored in the prefix length list plus the actual stored characters, when less than 255 bytes required 1 bytes of space, greater than 255 bytes required 2 bytes of space. So the maximum amount of space for varchar (10) is 11 bytes, with 1 bytes to hold the character length.

7.BINARY and varbinary: similar to char and varchar, binary and varbinary store binary strings, rather than character strings, which do not have the concept of a character set, sorting and comparison by binary values. N in BINARY (n) and varbinary (n) all represent byte lengths. The binary padding character is 0x00, and the char padding character is 0x20.

8.BLOB is used to store binary large data types, in most cases the BLOB type is considered to be large enough for varbinary type columns, and columns of the same text type are considered large enough for varchar type columns. Blob and text types cannot have default values. When sorting, only the first max_sort_length bytes of the column are used (default 1024, set GLOBAL max_sort_length=2048). To effectively store a large data type of type BLOB or text, the value of the column is generally stored in the row overflow page, and the row data stored by the data page contains only the first part of the data for BLOB or text type data.

Both 9.ENUM and set types are collection types, enums can enumerate up to 65,535 elements, and set types can enumerate up to 64 elements.

MySQL Technology Insider (SQL Programming)-data type