MySQL data types, variable modifiers, and variable differences

Client programs: MySQL, Mysqladmin, mysqldump, Mysqlimport, Mysqlcheck

Server-side programs: mysqld, Mysqld_safe, Mysqld_multi

MY.CNF configuration file Lookup order, whichever is the last one found

/ETC/MY.CNF--/ETC/MYSQL/MY.CNF-$MYSQL _home/my.cnf----default-extra-file=/path/to/somefile--~/ . MY.CNF (under User's home directory)

The my.cnf file is a segmented configuration file

[MYSQLD] is only valid for MYSQLD applications

[Mysqld_safe] is only valid for Mysqld_safe applications

[Client] is valid for all MySQL client programs

[MySQL] is only valid for the client's MySQL program

mysql> use MySQL

View status information for a table, including the engine used

Mysql> Show Table status like ' user ' \g

1. Row ***************************

Name:user

Engine:myisam

Version:10

Row_format:dynamic

Rows:4

avg_row_length:143

data_length:572

max_data_length:281474976710655

index_length:2048

data_free:0

Auto_increment:null

Create_time:2016-12-23 19:51:38

Update_time:2017-02-11 17:39:16

Check_time:null

Collation:utf8_bin

Checksum:null

Create_options:

Comment:users and Global Privileges

Data type:

Numeric exact values

int integral type

TINYINT 2^8 SMALLINT 2^16 mediaint 2^24 INT 2^32 BIGINT 2^64

Decimal exact decimal

Bit bitwise STORAGE

Approximate values

Float 2^4 single-precision floating-point type

Double 2^8 dual precision floating point type

Real

Float (g,f)

G: How many people are there altogether?

F: Indicates the number of floating-point numbers

Example 1.36 g=3 f=2

Character type

Fixed length: CHAR (#), BINARY

CHAR 2^8

VARCHAR 2^16 Grow longer

BINARY is case-sensitive, fixed-length

VARBINARY case-sensitive, variable-length

Tinyblob 2^8

BLOB 2^16 Binary Large Object

Mediumblob 2^24

Longblob 2^32

Tinytext 2^8

Text 2^16 type

Mediuntext 2^24

Longtext 2^32

Enum enum

Example: ENUM (' m ', ' f ') can only write M or F

Set Set

Example: SET (' m ', ' f ') can only write M, F, MF, FM

Date-Time Type

DATE "Ccyy-mm--dd"

Time ' Hh:mm:ss '

DATETIME ' Ccyy-mm-dd hh:mm;ss '

Year CCYY or YY

Example: Year (4) means that the time is expressed in 4 digits

Year (2) means time is expressed in 2 digits

Note: When occupying more than 8 bits (2^8), each 2^8 must have one as the Terminator

Decoration of String Type:

Not NULL cannot be empty

NULL can be NULL

Default value is given when the user does not enter a value

CHARACTER set (character set)

COLLATION (Sorting rules)

>show character set; Displays all the character sets supported on the current server

>show COLLATION; To display collations under individual character sets

Plastic retouching

Auto_increment (auto-Grow)

1. Must not be 0

2. Be sure to create a primary key index or a unique key index

3. Be sure to be plastic and unsigned

ungigned (unsigned) cosmetic shaping

LAST_INSERT_ID () function that returns the most recent growth value

Mysql> select last_insert_id ();

+------------------+

| last_insert_id () |

+------------------+

| 0 |

+------------------+

1 row in Set (0.10 sec)

mysql> Use test

Mysql> CREATE TABLE test (id int unsigned auto_increment NOT null primary key,name char (20));

Query OK, 0 rows affected (0.80 sec)

Mysql> Show tables;

+----------------+

| Tables_in_test |

+----------------+

| Test |

+----------------+

1 row in Set (0.00 sec)

MySQL variables are divided into:

Global variables: Valid for all sessions, but only by the root user of MySQL

Session variables: Only valid for the current session, all users can modify

Effective time

Dynamic variables: Instant modification, immediate effect

Static variables: To be written in the configuration file, the restart takes effect

How dynamic variables are effective

Global: Invalid for current session, valid for new session

Session: Immediate effect, but only valid for current session

>show global variables;

>show Global variables like ' sql_mode ';

>show variables like ' Sql_mode ';

Display of variables

@ Show user-defined variables

@@ 显示 Server Bianliang

A variable is typically displayed with select

>show @ @session. Sql_mode;

>show @ @global. Sql_mode;

Setting of variables

General Set variables

>set global sql_mode= ' strict_all_tables '; Modifying global Variables Sql_mode

Cases

Mysql> SELECT @ @global. sql_mode; No definition

+--------------------------------------------+

| @ @global. Sql_mode |

+--------------------------------------------+

| strict_trans_tables,no_engine_substitution |

+--------------------------------------------+

1 row in Set (0.03 sec)

mysql> set global sql_mode= ' Strict_all_tables '; Change Global to Strict_all_tables

Query OK, 0 rows affected (0.04 sec)

Mysql> SELECT @ @global. sql_mode; Global is nearly modified

+-------------------+

| @ @global. Sql_mode |

+-------------------+

| Strict_all_tables |

+-------------------+

1 row in Set (0.00 sec)

Mysql> SELECT @ @session. sql_mode; But the current session is still not set

+--------------------------------------------+

| @ @session. Sql_mode |

+--------------------------------------------+

| strict_trans_tables,no_engine_substitution |

+--------------------------------------------+

1 row in Set (0.00 sec)

and current session > Global

So it's still not in effect.

Re-Login

Mysql>

Mysql> SELECT @ @global. sql_mode; Global still in effect

+-------------------+

| @ @global. Sql_mode |

+-------------------+

| Strict_all_tables |

+-------------------+

1 row in Set (0.00 sec)

Mysql> SELECT @ @session. sql_mode; Current session also takes effect with global and re-login

+--------------------+

| @ @session. Sql_mode |

+--------------------+

| Strict_all_tables |

+--------------------+

1 row in Set (0.00 sec)

Mysql> set session sql_mode= ' Strict_trans_tables '; modifying Session Variables

Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT @ @session. sql_mode; Session variable has been modified and is already in effect

+---------------------+

| @ @session. Sql_mode |

+---------------------+

| Strict_trans_tables |

+---------------------+

1 row in Set (0.00 sec)

Mysql> SELECT @ @global. sql_mode; Global variables remain unchanged when overridden by session variables

+-------------------+

| @ @global. Sql_mode |

+-------------------+

| Strict_all_tables |

+-------------------+

1 row in Set (0.00 sec)

Re-login again

Mysql> SELECT @ @global. sql_mode; Global variables are still unchanged

+-------------------+

| @ @global. Sql_mode |

+-------------------+

| Strict_all_tables |

+-------------------+

1 row in Set (0.00 sec)

Mysql> SELECT @ @session. sql_mode; The session variable has also been changed back

+--------------------+

| @ @session. Sql_mode |

+--------------------+

| Strict_all_tables |

+--------------------+

1 row in Set (0.00 sec)

This article is from the "11097124" blog, please be sure to keep this source http://11107124.blog.51cto.com/11097124/1897009

MySQL data types, variable modifiers, and variable differences