MySQL service-mysql column type, SQL schema, data dictionary

The role of the MySQL column type:

A column type can be simply understood to be used to "qualify" a user to store data in a column, which defines the valid values (characters, numbers, etc.) of the data, the maximum amount of storage space to occupy, the length of the character (fixed or long), whether it can be indexed, how it is indexed, and how to sort. In a relational database system, in order to make the data easy to manage, you must specify the corresponding column type for the table field (column). MySQL's column types can be divided into 4 categories: character, numeric, date and time, own type.

1. Character Type:

Character-qualified values can only be characters, and the MySQL character type is as follows:

type maximum character length name and attributesChar           255fixed-length string, case-sensitive varchar65535variable length string, case-insensitive binary255binary Development Long string, case sensitive varbinary65535Binary variable-length string, case-sensitive tinytext255The text type is a large object store, which means that it does not store the data itself, but rather a pointer to the location where the data is stored; text is case-insensitive text65535Mediumtext16777215Longtext4294967295Tinyblob255blob types are binary large object stores, which are binary forms of text, case-sensitive BLOBs65535Mediumblob16777215Longblob4294967295

2. Numerical Type:

Numeric limits only store numeric data, and MySQL has the following numeric types:

 type name value range (signed, unsigned)TINYINT minimum integer-128To127，0 to 255SMALLINT Small integer-32768 to 32767，0 to 65535integer in Mediumint-8388607 to 8388607，0 to 16777215int integer-2147483647 to 2147483647，0 to 4294967295BIGINT Large integer-9223372036854775807 to 9223372036854775807，0 to 18446744073709551615Decimal Decimalfloat single-precision floating-point doubal double-precision floating point

3. Date and Time type:

DateTime type is used to record the date or time, and the MySQL date and time types are as follows:

Type                name                value range
Date                '                1000-01-01 '  to '9999-12-31'                time                '-838:59:59' to ' 838:59:59 ' DATETIME            Date Time             '1000-01-01 00:00:00' to '9999-12-31 23:59:59' TIMESTAMP           timestamp ' 
    
     1970-01-01 00:00:00' to '
     2037 sometime '                year                  4-bit format:
     1901 to 
     2155, 2-bit format: 
     1970-2069 (
     70-69), or:
     0000
    

4. Own type:

The own type is MySQL built-in type, there are two kinds:

Set: A collection of strings, given in advance of multiple elements (up to 64) for the user to choose, the user can select multiple elements to fill in
Enum: enumeration, given in advance of multiple elements (up to 65,536) for the user to choose, the user can only select one element to fill in

Second, MySQL SQL mode:

MySQL servers can operate in different SQL modes, and different modes can be applied to different clients. This allows each application to customize the operating mode of the server according to its own needs. Such patterns define the SQL syntax that MySQL should support, and what validation checks should be performed on the data. This makes it easier to use MySQL in many different environments, along with other database servers. MySQL is commonly used in three SQL modes, traditional mode, strict mode, and ANSI mode. MySQL's SQL mode defaults to NULL, which is non-strict mode.

You can view the current SQL mode of MySQL using the following command:

MySQL>SHOW GLOBAL VARIABLESlike'sql_mode';  MySQL > SELECT @ @sql_mode;

You can set the SQL mode for MySQL using the following command:

MySQL>SET[global| SESSION] Sql_mode='modes';

or specify the SQL mode when you start MySQL:

mysqld--sql-mode= "Modes"

1.TRADITIONAL, Traditional mode:

A simple description of the pattern is "give an error instead of a warning" when an incorrect value is inserted in the column. Note: Once you find an error, discard insert/update immediately. If you use a non-transactional storage engine, the data changes made before the error will not "roll back" will cause the results to be updated "only partially".

SET [global| SESSION] sql_mode=' traditional ';

2.STRICT, Strict mode:

If the given value cannot be inserted into the table, the statement is discarded. There are two types of strict modes: Strict_trans_tables and Strict_all_tables. Strict_trans_tables enforces strict patterns on the tables that support transactions, and Strict_all_talbes performs strict mode on all tables.

SET [global| SESSION] sql_mode=' strict_trans_tables '; MySQLSET [global| SESSION] sql_mode=' strict_all_tables ';

3.ANSI mode:

Change the syntax and behavior of the operation to make it more appropriate for standard SQL.

Third, the data dictionary:

A data dictionary is similar to a system catalog or roster (unlike a directory), which holds metadata information (the database's overall attribute information) on the database server. Metadata information includes the name of the relationship (table), the name of each field in each relationship, the data type and length constraints of each field, the name of the view on each relationship, and the definition of the view, the name of the authorized user, the user's authorization and account information, the statistics class data:, the number of each relationship field, The storage method of each relationship;

The database for storing metadata on MySQL is: MySQL library, Infomation_schema Library, and Performance_schema Library, Performance_schema Library is a virtual library, similar to the Linux/ Proc and other pseudo-file systems.

MySQL service-mysql column type, SQL schema, data dictionary