MySQL数据类型、数据模型和变量

存储引擎

存储引擎是将表示层转换为物理层的数据，
存储引擎指的是表类型，每一个表都可以独立的自由的选择一个存储引擎

存储引擎	MyISM	Innodb
事务	不支持事务	支持事务
锁	表锁	行锁
表结构	.frm:表结构定义文件 ；.MYD 表数据文件 ; *.MYI 表索引文件	.frm:表结构文件 ; .idb : 表空间（数据和索引）

在mysql库中的表使用的存储引擎是MyISM，自己穿件的库使用的是默认的存储引擎

查看默认的存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine （引擎的名称)| Support（当前数据库是否支持） | Comment                                    | Transactions （是否支持事务） | XA（分布式事务）| Savepoints（保存点） |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.01 sec)

查看表的存储引擎（查表的状态信息）

mysql> show table status like 'user' \G
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 112
    Data_length: 448
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-06-21 10:52:34
    Update_time: 2018-06-21 11:09:48
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: Users and global privileges
1 row in set (0.00 sec)

数据类型

意义：

• 1.存入的值的类型
• 2.占据的存储空间
• 3.定长还是变长
• 4.如何比较以及排查
• 5.是否支持索引

字符类型

字符类型名称	格式	举例	含义
int	int(n)	int(5)	n代表的是显示的位数，此处显示5位
float	float(b,f)	float(5,3)	浮点数是5位，3个小数位
double	double（b,f）	double（5,3）	双精度浮点型5位数，3个小数位
year	year（2）或者year(4)

属性

属性名称	含义	定义规范	示例
auto_increment	整数类型， 自动增长，可以设置步长，必须是索引，不能为null	必须是无符号整型、主键、索引、not null	create table test (id int unsigned auto_increment not null primary key ,name char(20));
not null	非空
null	空
default	默认
unsigned	无符号

存储函数(使用select调用)
select last_insert_id()；查找自动增长的值

存储过程（使用call调用）

查看数据库支持的字符集

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)

查看排序规则

mysql> SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
| cp850_bin                | cp850    |  80 |         | Yes      |       1 |
| hp8_english_ci           | hp8      |   6 | Yes     | Yes      |       1 |
| hp8_bin                  | hp8      |  72 |         | Yes      |       1 |
| koi8r_general_ci         | koi8r    |   7 | Yes     | Yes      |       1 |
| koi8r_bin                | koi8r    |  74 |         | Yes      |       1 |
| latin1_german1_ci        | latin1   |   5 |         | Yes      |       1 |
| latin1_swedish_ci        | latin1   |   8 | Yes     | Yes      |       1 |
| latin1_danish_ci         | latin1   |  15 |         | Yes      |       1 |
| latin1_german2_ci        | latin1   |  31 |         | Yes      
| eucjpms_bin              | eucjpms  |  98 |         | Yes      |       1 |
+--------------------------+----------+-----+---------+----------+---------+
197 rows in set (0.00 sec)

SQL 模型

SQL 模型	含义
ANSI QUOTES	双引号和反引号的标识是相同的，只能表示字段名称，字符串只能使用单引号
IGNORE_SPACE	内建函数中忽略多余的空白字符
STRICT_ALL_TABLES	违反数据规则的都不允许填入，如果没有设置该模型，违反数据规则 只是有一个警告。
STRICT_TRANS_TABLES	向支持事务的表中插入非法数据时，是允许的。
TRADITIONAL

查询数据库的数据模型

mysql> show global variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

MySQL的变量

分类

全局变量和会话变量

按照作用域分为全局变量和会话变量

查看全局变量

show global variables

查看会话变量

show [session] variables

动态变量和静态变量

按照生效时间划分：动态和静态

动态：

• 可以即使修改，

静态：

• 写在配置文件中
• 通过参数传递给mysqld

动态生效方式：：

• 全局：对当前会话无效，只对新建会话有效
• 会话：及时生效，但是只对当前会话有效

变量调用

调用服务器变量：

• 服务内置的变量：@@变量名，
• 自定义的变量：@变量名

查看当前全局变量(sql_mode)

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

查看当前的会话变量(sql_mode)

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

设置变量

设置动态的全局变量，在当前会话中不生效，在新的会话中生效。

mysql> set global sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

设置动态会话变量，只在当前会话生效，

mysql> select @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)