脚本宝典收集整理的这篇文章主要介绍了

MySQL中存储引擎和数据类型

脚本宝典小编觉得挺不错的,现在分享给大家,也给大家做个参考,希望能帮助你少写一行代码,多一份安全和惬意。

存储引擎

一、查看存储引擎

输入show engines;

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

或者输入show variables like 'have%';

mysql> show variables like 'have%'; +------------------------+----------+ | Variable_name          | Value    | +------------------------+----------+ | have_compress          | YES      | | have_crypt             | NO       | | have_dynamic_loading   | YES      | | have_geometry          | YES      | | have_openssl           | DISABLED | | have_profiling         | YES      | | have_query_cache       | YES      | | have_rtree_keys        | YES      | | have_ssl               | DISABLED | | have_statement_timeout | YES      | | have_symlink           | YES      | +------------------------+----------+ 11 rows in set, 1 warning (0.00 sec)

一般来说MySQL默认存储引擎为InnoDB。

二、默认存储引擎

1.查看默认存储引擎

MySQL5.5版本之前默认存储引擎为MyISAM,5.5之后改为了InnoDB。

mysql>show variables like 'engine%'; +----------------------------------+--------+ | Variable_name                    | Value  | +----------------------------------+--------+ | default_storage_engine           | InnoDB | | default_tmp_storage_engine       | InnoDB | | disabled_storage_engines         |        | | internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+ 4 rows in set, 1 warning (0.00 sec)

2.修改默认存储引擎

修改默认引擎有两种方式,一种为通过图形化界面修改,一种为修改配置文件。

(1). 通过图形化界面修改(软件为mysql workbench)

通过图形化界面修改

(2). 修改配置文件

打开my.ini配置文件(如果是默认安装的话,在C:ProgramDataMySQLMySQL Server 5.7目录下),修改以下信息:

    [mysqld]      # The next three options are mutually exclusive to SERVER_PORT below.     # skip-networking      # enable-named-pipe      # shared-memory      # shared-memory-base-name=MYSQL      # The Pipe the MySQL Server will use     # socket=MYSQL      # The TCP/IP Port the MySQL Server will listen on     port=3306      # Path to installation directory. All paths are usually resolved relative to this.     basedir="C:/Program Files/MySQL/MySQL Server 5.7/"      # Path to the database root     datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data/      # The default character set that will be used when a new schema or table is     # created and no character set is defined     character-set-server=utf8      #  在这里修改默认存储引擎!     # The default storage engine that will be used when create new tables when     default-storage-engine=INNODB 

以上两种方法修改存储引擎之后,需要重启MySQL使配置生效!

三、存储引擎比较

存储引擎比较

各种存储引擎比较:

数据类型

mysql中有许多的数据类型,如下表:

  • 整数类型:
类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1 字节 (-128,127) (0,255) 小整数值
SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度/浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度/浮点数值
DECIMAL 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
  • 日期和时间类型
类型 大小(字节) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值,时间戳
  • 字符串类型
类型 大小 用途
CHAR 0-255字节 定长字符串
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字节 极大文本数据

一、CHAR和VARCHAR

char:固定长度字符类型

varchar:可变长度字符类型

char和varchar的一个区别就是检索时char删除了尾部的空格,所以char需要程序处理尾部空格的问题。如下代码:

mysql> create table vc(     -> v varchar(4),     -> c char(4)); Query OK, 0 rows affected (0.03 sec)  mysql> insert into vc values('ab  ','ab  '); Query OK, 1 row affected (0.02 sec)  mysql> select concat(v, '+'),concat(c, '+') from vc; +----------------+----------------+ | concat(v, '+') | concat(c, '+') | +----------------+----------------+ | ab  +          | ab+            | +----------------+----------------+ 1 row in set (0.00 sec)

不同的存储引擎对char和varchar的支持和使用原则也不一致,如下:

存储引擎 使用原则
MyISAM 建议使用固定长度的数据列代替可变长度的数据列
MEMORY 目前都使用固定长度的数据行存储,char和varchar没有太大区别,两者都作为char处理
InnoDB 建议使用varcahr,因为InnoDB数据表内部的行存储格式没有区分固定长度和可变长度列,主要的影响因素为数据行使用的存储总量,由于varchar平均占用空间更小,有利于最小化数据行存储总量来减少磁盘I/O读取的时间

二、TEXT和BLOB

text和blob都是用于保存较大文本数据,不过blob可以保存二进制数据(如照片,音频)
,而text只能保存字符数据(如文章、日记)。

blob和text值会引起一些性能问题,特别是当你执行了大量删除操作的时候,会在数据表中留下大量的“空洞”。建议定期使用optimize table对表进行碎片整理,避免“空洞”造成的性能问题。

下面是一个测试碎片整理optimize table命令功能的例子

mysql> create table t(id varchar(100), context text); Query OK, 0 rows affected (0.01 sec)  mysql> insert into t values(1,repeat('hello',1000)); Query OK, 1 row affected (0.00 sec)  mysql> insert into t values(2,repeat('hello',1000)); Query OK, 1 row affected (0.02 sec)  # 执行新增操作,以指数增长的方式快速增加表的数据量 mysql> insert into t select * from t; Query OK, 2 rows affected (0.02 sec) Records: 2  Duplicates: 0  Warnings: 0  mysql> insert into t select * from t; Query OK, 4 rows affected (0.01 sec) Records: 4  Duplicates: 0  Warnings: 0  # 很多步操作后...  mysql> insert into t select * from t; Query OK, 256 rows affected (0.01 sec) Records: 256  Duplicates: 0  Warnings: 0 

这个时候表的数据文件大小为:

操作后的表数据文件大小

从文件中删除id为"1"的数据,理论上能够使表的大小减小一半,但实际操作出现的却不是这样的结果。

mysql> delete from t where id = 1; Query OK, 16384 rows affected (2.30 sec)

删除一半数据后的表数据文件大小为:

删除一半数据后的表数据文件大小

可以发现文件大小根本没有减小,大小仍与之前一样,接下来对表进行optimize优化操作。

mysql> optimize table t; +-------------+----------+----------+----------+ | Table       | Op       | Msg_type | Msg_text | +-------------+----------+----------+----------+ | mysqldemo.t | optimize | status   |    OK    | +-------------+----------+----------+----------+ 2 rows in set (5.74 sec)

优化后的表数据文件大小为:

优化后的表数据文件大小

可以看到,表的大小已有明显减小,“空洞”空间已经被回收。

三、浮点数与定点数

浮点数在MySQL中用floatdouble来表示,如果插入数值精度超过该列定义的实际精度,则会被四舍五入到符合精度后进行插入存储,此过程不会报错。

而定点数实际上是用字符串形式存放的,在MySQL中用decimal表示,如果实际插入的数值精度大于实际定义的精度,则MySQL会产生警告(默认的SQLMode下),但实际数据会四舍五入后插入,但如果MySQL是在tranitional(传统模式)下,则MySQL会直接报错,拒绝执行。

两者的数据存储区别可以从下面的例子看出:

mysql> create table t(c1 float(10,2), c2 decimal(10,2)); Query OK, 0 rows affected (0.06 sec)  mysql> insert into t values(131072.32, 131072.32); Query OK, 1 row affected (0.00 sec)  mysql> select * from t; +-----------+-----------+ | c1        | c2        | +-----------+-----------+ | 131072.31 | 131072.32 | +-----------+-----------+ 1 row in set (0.00 sec)

浮点数的比较也是一个普遍存在的问题。因为二进制和计算机本身的构造原因,浮点数的表示会存在偏差,不能直接用"=="直接比较数值,而是通过两者之差小于一个特别小的数来进行比较。

例如: a - b < 0.000001

参考资料

总结

以上是脚本宝典为你收集整理的

MySQL中存储引擎和数据类型

全部内容,希望文章能够帮你解决

MySQL中存储引擎和数据类型

所遇到的程序开发问题,欢迎加入QQ群277859234一起讨论学习。如果觉得脚本宝典网站内容还不错,欢迎将脚本宝典网站推荐给程序员好友。 本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。

80%的人都看过