MySQL tables and data types

I View database version

select version();

II Database operation

2.1. Create database

create database if not exists test_db default charset utf8 collate utf8_general_ci;

The meaning of this SQL is: create if the database does not exist, do not create if it exists, and create test_db database, and set the character set to utf8 and the character order to utf8_general_ci.

2.2. Delete database

drop database test_db;

III Character set settings

MySQL provides different character set support for data storage. In the data comparison operation, different character order support is provided.

• character set: defines characters and their encoding.
• collation: defines the comparison rules of characters.

Take a simple example:

There are four characters: A, B, a and B. The codes of these four characters are a = 0, B = 1, a = 2 and B = 3 respectively. The character + code here constitutes a character set

If you want to compare the size of two characters, such as a, B, or a, B, the most intuitive way to compare them is to use their codes. For example, because 0 < 1, a < B; In addition, for a and a, although their codes are different, we think the upper and lower case characters should be equal, that is, A == a

Two comparison rules are defined above. The collection of these comparison rules is collection.

• If it is also uppercase and lowercase characters, compare their encoding size
• If two characters are case sensitive, they are equal

3.1. View character set

show (character set ｜ charset) [like Matching pattern]

Each character set has a corresponding default collation. It should be noted that the Maxlen attribute indicates that each character set needs at most a few bytes to represent a character. Let's take a look at the common character sets:

show character set where Charset in ('ascii', 'gb2312', 'gbk', 'utf8', 'utf8mb4')

3.2. View comparison rules

A character set corresponds to a variety of comparison rules.

show collation [like Matching pattern]

We can see that there are many attributes of comparison rules, and we choose utf8mb4 as the research object

show collation where Charset = 'utf8mb4'

Let's first look at the definition rule of the name of the sorting rule: utf8mb4_xxxx_xx, which can be roughly divided into three parts:

• The name of the comparison rule begins with the name of the associated character set
• Then the second part is the language corresponding to the comparison rules
• The last part is whether the comparison rules distinguish the stress, case and so on

  suffix	meaning	suffix	meaning
  _ai	Accent insensitive	_cs	Partition case
  _as	Accent sensitive	_bin	Binary comparison
  _ci	No partition case

General selection: xxxx_gerneral_ci is OK. This is a general comparison rule.

3.3. Set character set

MySQL can set four levels of character sets: server level, database level, table level and column level.

Here, if the database level is not specified, only the server level settings will be used, if the table level is not specified, the database level settings will be used, and if the column level is not specified, the database level settings will be used.

• Server level:
  The MySQL server configuration file can specify:

  [mysqld]
  character-set-server=utf8mb4
  collation-server=utf8mb4_0900_ai_ci
  

  To view the character set and collation in MySQL:

  show variables like 'character_set_server';  # View character set
  show variables like 'collation_server';      # View collation
  

• Database level:
  Specify the character set and comparison rules when creating the database:

  create database if not exists test_db_one default charset utf8 collate utf8_general_ci;
  

  To view character sets and comparison rules after creation:

  use test_db_one;
  show variables like 'character_set_database';  # View character set: utf8mb3(utf8 alias)
  show variables like 'collation_database';      # Collation: utf8_general_ci
  

  Modify the character set and comparison rules of the database:

  alter database test_db_one default charset gbk collate gbk_chinese_ci;
  

  If you use the default: create database xxx when creating, you will use the server to set the default character set and comparison rules.

• Table level:
  When creating a table, you can specify the character set and comparison rules

  create table test_tab (
      id integer primary key
  ) default character set gbk collate gbk_chinese_ci;
  

  Check it after it is built:

  show create table test_tab;
  

  
  Modify the character set and comparison rules of the table

  alter table test_tab character set utf8 collate utf8_general_ci;
  

• Column level:
  When creating a table, you can specify the character set and comparison rules of each column separately

  create table test_tab_1 (
      id integer primary key,
      name varchar(20) character set utf8 collate utf8_general_ci,
      ....
  )
  

  Modify the character set and comparison rules of the column

  alter table test_tab_1 modify name varchar(20) character set ascii collate ascii_general_ci;
  

  It should be noted here that if the character set is modified, it may cause errors. For example, if the original name is utf8 character set above is changed to ascii character set, if Chinese characters are stored in name, but converted to ascii character set, it cannot be expressed, resulting in errors.

Note: the character set and the comparison rule are related. Whether you modify the character set or the sorting rule, the corresponding comparison rule and character set will change accordingly.

• Only modify the character set, and the comparison rule will be modified to the default comparison rule of the character set
• If you only modify the comparison rule, the character set will be modified to the character set corresponding to the comparison rule

3.4. extend

Note the following three character set settings:

show variables like 'character_set_%';

variable	meaning
character_set_client	Character set used by client source data
character_set_connection	Connection layer character set
character_set_results	Query result character set

Setting method:

set character_set_client = utf8mb4;  
set character_set_results = utf8mb4;   
set character_set_connection = utf8mb4;
# Simple settings, the same as the above three effects
set names utf8mb4

You can also set in the configuration file:

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4

The above character sets should be consistent when used, and the conversion between various character sets of MySQL should be avoided.

IV Table operation

The operation of tables has been basically introduced above.

4.1. Create table

create table if not exists test_tab (
    id integer auto_increment primary key,
    name varchar(20) not null,
    sex binary default 0,
    birth date
) engine=InnoDB default character set utf8mb4 collate utf8mb4_unicode_ci;

engine can not be specified here. The default is InnoDB. In addition, if the character set specified by the database is not specified when creating the table here.

After completion, check the table structure:

desc test_tab;

4.2. Modify table

Modifying a table is divided into modifying table settings and modifying attributes

alter table test_tab character set utf8 collate utf8_general_ci;  # Modify table settings

alter table test_tab modify name varchar(20) character set ascii collate ascii_general_ci;  # modify attribute
alter table test_tab add column name varchar(20) character set utf8;  # Add attribute
alter table test_tab drop column name;  # Delete attribute

4.3. Delete, empty, and rename

Delete table

drop table test_tab;

Clear the table: truncate deletes the table as a whole, does not write the log, and does not activate the trigger

truncate table test_tab;

Table rename

rename table test_tab to test_aa;

V data type

5.1. integer

type	byte	Range (signed)	Range (unsigned)	remarks
tinyint	1	-128,127	0,255	Small integer value
smallint	2	-32768,32767	0,65535	Large integer value
mediumint	3	-8388608,8388607	0,16777215	Large integer value
int	4			Large integer value
bigint	8			Super large integer value

Properties signed and unsigned:

create table if not exists test_tab (
    ......
    id integer,
    age int unsigned default 0,
    ......
)

5.2. float

type	byte	remarks
float	4	Single precision floating point
double	8	Double precision floating point
decimal		Exact small value

decimal(M,D): M represents the maximum number of digits (precision), the range is 1-65, and the default value is 10; D represents the number of digits (decimal places) to the right of the decimal point. The range is 0 to 30 and cannot be less than m. It can not be specified. The default is 0.

The decimal type is the only type officially specified by MySQL that can be accurately stored. It is recommended that all types related to money should be stored as the decimal type, otherwise the accuracy will be lost.

5.3. character

type	byte	remarks
char	0-255 bytes	Fixed length string
varchar	0-65535 bytes	Variable length string
tinytext	0-255 bytes	Short text string
text	0-65535 bytes	Long text data
longtext	0-4294967295 bytes	Very long text data
tinyblob	0-255 bytes	Binary string
blob	0-65535 bytes	Long text data in binary form
longblob	0-4294967295 bytes	Extremely long text data in binary form

Note:

• blob type is mainly used to store binary large objects, such as pictures, audio and video
• Char type is of fixed length. If the length is not enough, it will fill in spaces on the right to reach the specified length. When the value of char is retrieved, the trailing spaces will be deleted
• varchar type is used to store variable length strings. When stored, characters do not reach the defined length and spaces will not be filled.

5.4. Date type

type	byte	format	remarks
date	3	yyyy-MM-dd	Store date value
time	3	HH:mm:ss	Storage hours, minutes and seconds
yaer	1	yyyy	Storage Year
datetime	8	yyyy-MM-dd HH:mm:ss	Storage date + time
timestamp	4	yyyy-MM-dd HH:mm:ss	Store date + time, which can be used as time stamp