mysql 的 NO_ENGINE_SUBSTITUTION
思韵闪耀
2022-02-25
0

NO_ENGINE_SUBSTITUTION 是干啥的,这个还真没注意过以前。

研究了一下,原来这个是在创建表指定engine子句的时候,让mysql对此DDL做判断用的。看一个例子:

mysql> show variables like '%sql_mode%';

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

| Variable_name | Value |

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

| sql_mode | NO_ENGINE_SUBSTITUTION |

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

1 row in set (0.01 sec)


mysql> show engines;

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

| Engine | Support | Comment | Transactions | XA | Savepoints |

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

| CSV | YES | CSV storage engine | NO | NO | NO |

| 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 |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

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

9 rows in set (0.00 sec)


mysql> use test ;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A


Database changed

mysql> create table t_e (it int ) engine=FEDERATE1;

ERROR 1286 (42000): Unknown storage engine 'FEDERATE1' #FEDERATE1引擎不存在,直接报错。



当前 sql_mode="NO_ENGINE_SUBSTITUTION",表示不进行引擎替换。就是说,如果DDL的engine子句指定的引擎不存在,直接报错。

再看下面的例子。


mysql> set sql_mode='';

Query OK, 0 rows affected (0.00 sec)


mysql> create table t_e (it int ) engine=FEDERATE1;

Query OK, 0 rows affected, 2 warnings (0.04 sec)



1 row in set (0.00 sec)

mysql> show warnings ;

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

| Level | Code | Message |

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

| Warning | 1286 | Unknown storage engine 'FEDERATE1' |

| Warning | 1266 | Using storage engine InnoDB for table 't_e' |

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

2 rows in set (0.00 sec)


mysql> show create table t_e;

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

| Table | Create Table |

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

| t_e | CREATE TABLE `t_e` (

`it` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

#引擎已被替换成InnoDB

去掉 NO_ENGINE_SUBSTITUTION ,此时,依然指定一个不存在的引擎,mysql自动将引擎替换成默认的innodb引擎。

去掉 NO_ENGINE_SUBSTITUTION ,对于alter操作的行为是:alter操作并不成功。

mysql> show create table t_e;

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

| Table | Create Table |

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

| t_e | CREATE TABLE `t_e` (

`it` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

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

1 row in set (0.00 sec)

#以前是MyISAM引擎。


mysql> alter table t_e engine=FEDERATE1; #修改成不存在的FEDERATE1引擎

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

Records: 0 Duplicates: 0 Warnings: 1


mysql> show warnings ;

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

| Level | Code | Message |

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

| Warning | 1286 | Unknown storage engine 'FEDERATE1' |

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

1 row in set (0.00 sec)


mysql> show create table t_e;

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

| Table | Create Table |

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

| t_e | CREATE TABLE `t_e` (

`it` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 | #引擎并没修改。

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

1 row in set (0.00 sec)

如下是官网解释:

 NO_ENGINE_SUBSTITUTION

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.

The default SQL mode includes NO_ENGINE_SUBSTITUTION.

Because storage engines can be pluggable at runtime, unavailable engines are treated the same way:

With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable.


【版权声明】
本站部分内容来源于互联网,本站不拥有所有权,不承担相关法律责任。如果发现本站有侵权的内容,欢迎发送邮件至masing@13sy.com 举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。

相关内容

docker-compos...
1.Compose介绍 DockerCompose是一个用来定义和...
2024-04-26
Mysqldump 时出现...
Mysqldump 时出现错误及解决方案:mysqldump: C...
2024-01-16
MySQL数据库”mysq...
MySQL数据库”mysql SQL Error:1146,SQL...
2023-12-02
mysql关闭binlog...
linux编辑my.cnf,windows编辑my.ini在[my...
2023-12-02
20道Mysql面试题
1. 什么是 MySQL?它与其他数据库管理系统有何不同? 答:M...
2023-11-23
PostgreSQL和My...
PostgreSQL和MySQL对比 下面将从以下几个方面阐述My...
2022-11-03

热门资讯

MySQL权限篇之REPLIC... MySQL权限篇之REPLICATION CLIENT及REPLICATION SLAVE REPL...
configure: erro... php编译后出现这样的信息 以上略 checking for MySQLi support... y...
MySQL清屏命令 在Linux系统下MySQL的清屏命令 MySQL在Linux系统下,输入system clear即...
Skip-External-L... MySQL的配置文件my.cnf中默认存在一行skip-external-locking的参数,即跳...
MySQL 的复合查询或者嵌套... MySQL 的复合查询或者嵌套查询,有表两张,要以 tmpa 表两张为表列,将 tmpb 横向列出,...
MySQL创建用户与授权 一. 创建用户 命令: CREATE USER username@host IDENTIFIED B...
mysql 如何跟踪_MySQ... 在项目开发中,难免会遇到在数据库服务器端跟踪sql执行语句的需求,通过跟踪sql执行语句,我们可以确...
Mysql服务器无法启动 一、Mysql服务器无法启动,错误日志中提示: ^G/usr/local/mysql/bin/mys...
MySql ManifestU... 解决 MySql ManifestUpdate 通过执行计划关闭的方式,不适用超级权限管理人员(公司...
给MySQL的备份账户添加权限 给MySQL的备份账户添加权限 MySQL的备份可以说是重中之重,毕竟数据是一个网站的命脉。 但是备...