mysql traditional_MySQL(二)：特性详解

逸格草草

于 2021-01-18 23:28:15 发布

阅读量260

点赞数

文章标签： mysql traditional

本文链接：https://blog.csdn.net/weixin_42135773/article/details/113160114

版权

一、事务隔离

事务隔离(isolation)定义了数据库系统中一个操作产生的影响什么时候以哪种方式可以对其他并发操作可见。隔离是事务ACID (原子性、一致性性、隔离性、持久性)四大属性中的一个重要属性。

并发控制(Concurrency control)

并发控制描述了数据库处理隔离以保证数据正确性的机制。为了保证并行事务执行的准确执行数据库和存储引擎在设计的时候着重强调了这一点。典型的事务相关机制限制数据的访问顺序(执行调度)以满足可序列化和可恢复性。限制数据访问意味着降低了执行的性能，并发控制机制就是要保证在满足这些限制的前提下提供尽可能高的性能。经常在不损害正确性的情况下，为了达到更好的性能，可序列化的的要求会减低一些，但是为了避免数据一致性的破坏，可恢复性必须保证。

两阶段锁是关系数据库中最常见的提供了可序列化和可恢复性的并发控制机制，为了访问一个数据库对象，事务首先要获得这个对象的锁。对于不同的访问类型(如对对象的读写操作)和锁的类型，如果另外一个事务正持有这个对象的锁，获得锁的过程会被阻塞或者延迟。

隔离级别(Isolation levels)串行化(Serializable)：基于锁机制并发控制的DBMS实现可序列化要求在选定对象上的读锁和写锁保持直到事务结束后才能释放。在SELECT的查询中使用一个“WHERE”子句来描述一个范围时应该获得一个“范围锁(range-locks)”。这种机制可以避免“幻读(phantom reads)”现象。

可重读(Repeatable reads)：基于锁机制并发控制的DBMS需要对选定对象的读锁(read locks)和写锁(write locks)一直保持到事务结束，但不要求“范围锁(range-locks)”，因此可能会发生“幻读(phantom reads)”

读提交(Read committed)：基于锁机制并发控制的DBMS需要对选定对象的写锁(write locks)一直保持到事务结束，但是读锁(read locks)在SELECT操作完成后马上释放(因此“不可重复读”现象可能会发生，见下面描述)。不要求“范围锁(range-locks)”。

读未提交(Read uncommitted)：是最低的隔离级别。允许脏读(dirty reads)，事务可以看到其他事务“尚未提交”的修改。

读现象(Read phenomena)脏读(Dirty reads)：当一个事务允许读取另外一个事务修改但未提交的数据时，就可能发生脏读(dirty reads)。

不可重复读(non-repeatable read)：在一次事务中，当一行数据获取两遍得到不同的结果表示发生了“不可重复读(non-repeatable read)”。

幻读(phantom read)：在事务执行过程中，当两个完全相同的查询语句执行得到不同的结果集。这种现象称为“幻读(phantom read)”。

隔离级别对应的读现象隔离级别脏读不可重复读幻读

读未提交可能发生可能发生可能发生

读提交-可能发生可能发生

可重读--可能发生

串行化---

二、数据类型修饰符

char,varchar和text几个字符型常用的属性修饰符：NOT NULL：非空约束

NULL：允许为空

DEFAULT 'string'：默认值；不适用text类型

CHARACTER SET '字符集'：

mysql> show variables like '%char%';

mysql> show character set;

COLLATION 'string'：排序规则

mysql> show collation;

binary,varbinary和blob几种字符型常用的属性修符：NOT NULL

NULL

DEFAULT：不适用于blob

整型的常用属性修饰符：AUTO_INCREMENT：自动增长

前提：非空且惟一；支持索引；非负值；UNSIGNED：无符号

NULL

NOT NULL

DEFAULT

浮点型常用修饰符：NOT NULL

NULL

DEFAULT

UNSIGNED

日期时间型的修饰符：NOT NULL

NULL

DEFAULT

SET和ENUM的修饰符：NOT NULL

NULL

DEFAULT

MySQL中的字符大小写：SQL关键字及函数名不区分字符大小写；

数据库、表和视图名称的大小写是否区分取决于底层OS及FS

存储过程、存储函数及事件调度器的名称不区分大小写；但触发器区分；

表的别名区分大小写；

对于字段中的数据；如果字段类型为binary类型；则区分大小写；非binary类型不区分大小写；

注意：为了提高命中率以提升效率；建议使用过程使用统一风格；要么大写；要么小写。

Examples:mysql> create table test (name char(3));

Query OK, 0 rows affected (0.14 sec)

mysql> insert into test values ('jerry');#超出定义长度

ERROR 1406 (22001): Data too long for column 'name' at row 1

#直接报错；且没有执行；是由系统变量SQL_MODE所定义

mysql> select * from test;

Empty set (0.00 sec)

mysql> insert into test values ('tom');

Query OK, 1 row affected (0.16 sec)

mysql> select * from test;

+------+

| name |

+------+

| tom |

+------+

1 row in set (0.00 sec)

mysql>

mysql支持的sql_mode模式：ANSI、TRADITIONAL、STRICT_ALL_TABLES和STRICT_TRANS_TABLES。

ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。

TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。

STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。只对支持事务的表有效。

STRICT_ALL_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。对所有表都有效。mysql> set session sql_mode=ansi;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_mode;

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

| @@session.sql_mode |

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

| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |

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

1 row in set (0.00 sec)

mysql> insert into test values ('aaaaa');

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;

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

| Level | Code | Message |

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

| Warning | 1265 | Data truncated for column 'name' at row 1 |

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

1 row in set (0.00 sec)

mysql> select * from test;

+------+

| name |

+------+

| tom |

| aaa |

+------+

2 rows in set (0.00 sec)

设定服务器变量的值：(仅用于支持动态的变量)

支持修改的服务器变量：动态变量：可以MySQL运行时修改；

静态变量：于配置文件中修改其值；并重启后方能生效；

服务器变量冲其生效范围来讲；有两类：全局变量：服务器级别；修改之后仅对新建立的会话生效；

会话变量：会话级别；仅对当前会话生效；退出失效；

会话建立时；从全局继承各变量；

查看服务器变量：

mysql> show [{global|session}] variables [like ''];mysql> show global variables like 'sql%';

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

| Variable_name | Value |

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

| sql_auto_is_null | OFF |

| sql_big_selects | ON |

| sql_buffer_result | OFF |

| sql_log_bin | ON |

| sql_log_off | OFF |

| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

| sql_notes | ON |

| sql_quote_show_create | ON |

| sql_safe_updates | OFF |

| sql_select_limit | 18446744073709551615 |

| sql_slave_skip_counter | 0 |

| sql_warnings | OFF |

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

mysql> select @@{global|seesion}.variable_name;mysql> select @@global.sql_mode;

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

| @@global.sql_mode |

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

| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

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

1 row in set (0.00 sec)

mysql>

mysql> select * from information_schema.{global|session}_variables where variable_name='some_variable_name';mysql> select * from information_schema.global_variables where variable_name='sql_mode';

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

| VARIABLE_NAME | VARIABLE_VALUE |

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

| SQL_MODE | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

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

1 row in set (0.00 sec)

mysql>

修改变量：

前提：默认修改全局变量仅管理员有权限

mysql> set {global|session} bariable_name='value';mysql> select @@session.sql_mode;

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

| @@session.sql_mode |

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

| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |

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

1 row in set (0.00 sec)

mysql> set session sql_mode=strict_all_tables;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_mode;

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

| @@session.sql_mode |

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

| STRICT_ALL_TABLES |

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

1 row in set (0.00 sec)

mysql>

三、创建库和表

库创建和删除：mysql> help create database

Name: 'CREATE DATABASE' #创建库

Description:

Syntax:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification] ...

#IF NOT EXISTS 判断库是否存在；存在报错；但不会终止语句

create_specification:

[DEFAULT] CHARACTER SET [=] charset_name #设定字符集

| [DEFAULT] COLLATE [=] collation_name #设定排序规则

#Example:

mysql> create database test;

ERROR 1007 (HY000): Can't create database 'test'; database exists

mysql> create database if not exists test;

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;

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

| Level | Code | Message |

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

| Note | 1007 | Can't create database 'test'; database exists |

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

1 row in set (0.00 sec)

mysql> #适用于脚本中mysql> help drop database;

Name: 'DROP DATABASE'

Description:

Syntax:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

#删除库

#IF EXISTS 判断是否存在

#Examples:

mysql> drop database mydb;

ERROR 1008 (HY000): Can't drop database 'mydb'; database doesn't exist

mysql> drop database if exists mydb;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;

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

| Level | Code | Message |

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

| Note | 1008 | Can't drop database 'mydb'; database doesn't exist |

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

1 row in set (0.00 sec)

mysql>mysql> help alter database;

Name: 'ALTER DATABASE' #修改库

Description:

Syntax:

ALTER {DATABASE | SCHEMA} [db_name]

alter_specification ...

ALTER {DATABASE | SCHEMA} db_name

UPGRADE DATA DIRECTORY NAME

alter_specification:

[DEFAULT] CHARACTER SET [=] charset_name #更改字符集

| [DEFAULT] COLLATE [=] collation_name #更改排序规则

表创建：mysql> help create table;

Name: 'CREATE TABLE'

Description:

Syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...) #字段的定义、和指定键或索引

[table_options] #指定存储引擎选项等...

[partition_options]

#TEMPORARY 创建临时表；存储于内存中

#ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} 表格式

#TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] 指定表空间

Or:

#第二种方式(复制表数据)

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

[table_options]

[partition_options]

select_statement

Or:

#第三种方式(复制表结构)

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

{ LIKE old_tbl_name | (LIKE old_tbl_name) }

#LIKE old_tbl_name 根据某张表创建一张空表

#Examples

mysql> create table t1 (id int unsigned not null unique key,name char(30) not null,age tinyint unsigned not null);

Query OK, 0 rows affected (0.05 sec)

mysql> desc t1;

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

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

| id | int(10) unsigned | NO | PRI | NULL | |

| age | tinyint(3) unsigned | NO | | NULL | |

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

3 rows in set (0.00 sec)

mysql>

MyISAM表：每表有三个文件；都位于数据库目录中；tb_name.frm：表结构定义

tb_name.MYD：数据文件

tb_name.MYI：索引文件

InnoDB表：有两种存储方式innodb_file_per_table = ON

1、默认；每表有一个独立文件和一个多表共享的文件tb_name.frm：表结构的定义；位于数据库目录中

ibdata#：共享的表空间文件；默认位于数据目录(datadir指向的位置)中；

2、独立的表空间：(建议使用该项)tb_name.frm:每表有一个表结构文件

tb_name.ibd:一个特有的表空间文件mysql> show global variables like "innodb_file_per_table";

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

| Variable_name | Value |

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

| innodb_file_per_table | ON |

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

1 row in set (0.01 sec)

mysql>

[root@localhost test]# pwd

/mydata/data/test

[root@localhost test]# ls

db.opt t1.frm t1.ibd

[root@localhost test]#

表修改：mysql> help alter table;

Name: 'ALTER TABLE'

Description:

Syntax:

ALTER [IGNORE] TABLE tbl_name

[alter_specification [, alter_specification] ...]

[partition_options]

alter_specification:

table_options

| ADD [COLUMN] col_name column_definition

[FIRST | AFTER col_name ] #可以直接ADD并指定在某字段后面

| ADD [COLUMN] (col_name column_definition,...) #多个字段

| ADD {INDEX|KEY} [index_name] #添加索引

[index_type] (index_col_name,...) [index_option] ...

| ADD [CONSTRAINT [symbol]] PRIMARY KEY #添加主键

[index_type] (index_col_name,...) [index_option] ...

| ADD [CONSTRAINT [symbol]]

UNIQUE [INDEX|KEY] [index_name] #唯一键

[index_type] (index_col_name,...) [index_option] ...

| ADD FULLTEXT [INDEX|KEY] [index_name] #全文索引

(index_col_name,...) [index_option] ...

| ADD SPATIAL [INDEX|KEY] [index_name]

(index_col_name,...) [index_option] ...

| ADD [CONSTRAINT [symbol]]

FOREIGN KEY [index_name] (index_col_name,...) #外键

reference_definition

| ALGORITHM [=] {DEFAULT|INPLACE|COPY}

| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

| CHANGE [COLUMN] old_col_name new_col_name column_definition

[FIRST|AFTER col_name] #修改字段名和其他属性

| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

| MODIFY [COLUMN] col_name column_definition #修改字段类型和属性

[FIRST | AFTER col_name]

| DROP [COLUMN] col_name #删除一个字段；直接指定字段名

| DROP PRIMARY KEY #删除主键

| DROP {INDEX|KEY} index_name #删除索引

| DROP FOREIGN KEY fk_symbol #删除外键

| DISABLE KEYS #禁用键

| ENABLE KEYS #启用键

| RENAME [TO|AS] new_tbl_name #改名

| ORDER BY col_name [, col_name] ...

| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] #改变字符集

| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name] #排序规则

| DISCARD TABLESPACE

| IMPORT TABLESPACE

| FORCE

index_col_name:

col_name [(length)] [ASC | DESC]

index_type:

USING {BTREE | HASH}

index_option:

KEY_BLOCK_SIZE [=] value

| index_type

| WITH PARSER parser_name

| COMMENT 'string'

table_options:

table_option [[,] table_option] ... (see CREATE TABLE options)

四、实例

Examples

新建如下表(包括结构和内容)：

ID Name Age Gender Course

1 Ling Huchong 24 Male Hamogong

2 Huang Rong 19 Female Chilian Shenzhang

3 Lu Wushaung 18 Female Jiuyang Shenggong

4 Zhu Ziliu 52 Male Pixie Jianfa

5 Chen Jialuo 22 Male Xianglong Shiba Zhang

6 Ou Yangfeng 70 Male Shenxiang Bannuo Gong

mysql> create table students (ID int(10) unsigned auto_increment not null,Name char(30) not null,Age tinyint unsigned not null,Gender enum('Male','Female') default 'Male',Course varchar(50) not null,primary key(ID));

Query OK, 0 rows affected (0.26 sec)

mysql> desc students;

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

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

| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |

| Age | tinyint(3) unsigned | NO | | NULL | |

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

5 rows in set (0.00 sec)

mysql>

mysql> insert into students (Name,Age,Gender,Course) values ('Ling Huchong',24,'Male','Hamogong'),('Huang Rong',19,'Female','Chilian shenzhang'),('Lu Wushang',18,'Female','Jiuyang Shenggong'),('Zhu Ziliu',52,'Male','Pixie Jianfa'),('Chen Jialou',22,'Male','Xiangling Shiba Zhang'),('Ou Yangfeng',70,'Male','Shenxiang Bannou Gong');

Query OK, 6 rows affected (0.05 sec)

Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from students;

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

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

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

6 rows in set (0.00 sec)

mysql>

1、新增字段：Class 字段定义自行选择；放置于Name字段后；mysql> alter table students add Class char(30) default null after Name;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from students;