mysql 一基本数据类型和存储引擎

最新推荐文章于 2022-04-09 18:29:29 发布

无成

最新推荐文章于 2022-04-09 18:29:29 发布

阅读量362

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/y2052/article/details/81571981

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了MySQL数据库的基本操作，包括数据库的创建、查询、修改和删除，同时详细解释了各种存储引擎的特点及适用场景，并概述了MySQL支持的主要数据类型。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

简单的说明

[ ]中括号中内容为可选

{ }大括号中内容为必选

| 表示选择(可以选择其一也可以都选择)

; 结尾以表格的形式显示结果同 \g

\G 结尾以列表的形式显示结果

一数据库和存储引擎

数据库DATABASE

数据库可由字母数字下划线 $ 组成 .但不能有纯数字构成,仅有_或$构成也是可以的

名称最长64字符别名 256个字符

默认字符集 my.ini default-character-set

增: 创建数据库

1 CREATE DATABASE [IF NOT EXISTS] dbname [指定字符集];

2. CREATE SCHEMA [IF NOT EXISTS] dbname ;

指定字符集:在创建数据库语句后添加 default character set = 字符集; 或 default character utf8;

查: SHOW DATABASES ;

改: 修改相关参数(字符集校对规则),不能修改数据库的名字

ALTER { DATABASE | SCHEMA} [dbname] {修改字符集 | 修改校对规则}

若未指定dbname 则默认当前数据库

修改字符集 : [DEFAULT] CHARCTER SET [=]字符集

修改校对规则 : [DEFAULT] COLLATE [=] 校对规则名称

collation ：校对、排序规则，校对规则是依赖字符集而存在的，不同的字符集可能拥有不同的排序规则

校对规则名称由： “字符集_地区名_校对规则”

例如：big5_chinese_ci big5为字符集 ci大小写不敏感（后缀还有：cs 表大小写敏感，bin 表二进制编码比较）

要求: 其用户必选有修改权限

删:DROP DATABASE dbname ;

使用: USE dbname;

存储引擎 ENGINE

通过设置表的ENGINE属性来指定表的存储引擎 ENGINE =存储引擎类型

存储数据
为存储的数据建立索引
更新查询数据

查询存储引擎: 1.查询支持的全部引擎 SHOW DATABASES ;

2.查询默认的存储引擎 SHOW VARIABLES 语句查看系统变量及其值再经过 like

SHOW VARIABLES like '%storage_engine%'

mysql> show engines \G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions   XA Savepoints: NO  
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions   XA Savepoints: NO  
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions   XA Savepoints: NO  
*************************** 5. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: MyISAM storage engine
Transactions   XA Savepoints: NO  
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions   XA Savepoints: NO  
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions   XA Savepoints: NO  
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions   XA Savepoints: NO  
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions   XA Savepoints: NULL
9 rows in set (0.00 sec)

mysql> show variables like '%storage_engines%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| disabled_storage_engines |       |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)

InnoDB

InnoDB 给Mysql表提过了事务,回滚,崩溃修复能力和多版本并发控制的事务安全

但读写效率稍差占用的数据空间相对比较大

特性

事务处理能力
支持自动增长列 AUTO_INCREMENT ,其值必须唯一且为空
提供外键约束
表结构存储在 .frm文件数据存储在 innodb_data_home_dir 索引存储在innodb_data_file_path

适用

更新密集的表:适合处理多重并发的更新请求
事务 :InooDB是唯一支持事务的标准Mysql存储引擎
自动灾难恢复

MyISAM

占用空间小,处理速度快

但不支持事务的完整性和并发性

管理非事务的表,提供高速的存储和检索,支持全文搜索

适用于对应用的完整性并发性要求低的非事务表

.frm 存储表结构

.MYD存储数据

.MYI 存储索引

1.MyISAM 静态若不是用 xBLOB xTEXT VARCHAR类型,Mysql就会自动使用静态MyISAM格式

2.MyISAM 动态有 xBLOB xTEXT VARCHAR类型 ,动态格式的表占用空间小 ,若修改表的内容可能造成位置的移动,将会产生碎片,碎片的积累将导致性能的下降

尽可能使用静态数据结构
使用OPTIMIZE TABLE语句,整理表的碎片,恢复由更新或删除导致的空间丢失

3.MyISAM压缩

只读的表使用myisampack工具将其转换为MyISAM压缩表来减少空间

MEMORY

1.使用存储在内存中的内容来创建表,并且所以数据也存放在内存中

2.每个基于MEMORY存储引擎的表实际对应一个磁盘文件,其名与表名相同扩展名为 .frm 仅存储表的结构

而数据文件存储在内存

3.默认使用哈希(HASH)索引,速度比B型树(BTRE)索引要快

优点: MEMORY表大小受到限制,处理速度快

但数据易丢失生命周期短,安全性不高

适用于较小的数据库表

二数据类型

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

当指定不合法的MySQL不能表示的值时使用"零"值

BOOL	1字节	自动转换成TINYINT(1)
TINYINT	1字节	YEAR YYYY
SMALLINT	2 字节
MEDIUMINT	3 字节	DATE YYYY-MM-DD	TIME HH:MM:SS
INT或INTEGER	4 字节	TIMESTAMP YYYYMMDD HHMMSS	混合日期和时间值，时间戳
BIGINT	8字节	DATETIME YYYY-MM-DD HH:MM:SS	混合日期和时间值
FLOAT	4 字节FLOAT(M,D) FLOAT（M）	M最大位数,D小数精度整数部分为M-D FLOAT（M）未发现作用
DOUBLE	8 字节DOUBLE(M,D)	M最大位数,D小数精度整数部分为M-D
DEC或DECIMAL	DECIMAL(M,D) 要求M>=D	M最大位数,D小数精度整数部分为M-D

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型	大小	用途
CHAR	0-255字节	定长字符串	保存和检索的方式不同最大长度和是否尾部空格被保留等方面也不同存储或检索过程中不进行大小写转换	char (M) 固定的列
VARCHAR	0-65535 字节	变长字符串	保存和检索的方式不同最大长度和是否尾部空格被保留等方面也不同存储或检索过程中不进行大小写转换	节省空间
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据		要搜索的内容区分大小写
TEXT	0-65 535字节	长文本数据		要搜索的内容不区分大小写
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。

数据类型	最大值	说明	适用
Enum("value1","value2",....)	65535	该类型可以容纳所列值之一成为NULL	将列中的内容限定在一种选择
Set("value1","value2",....)	64	可容纳一组值或NULL	允许列中的有多于一个的条目