主页 / dba / 问题 / 17114
Accepted
jcho360
Asked: 2012-04-27 09:07:04 +0800 CST

mysql存储引擎怎么可能为NULL？

  • 772

我只是在读一本书，在其中一个例子中我得到了通知：

  • information_schema.table 中的引擎为空，这怎么可能？
  • 我可以在没有任何引擎的情况下创建表格吗？？那有什么专业人士吗？在此处输入图像描述
mysql storage-engine

2 个回答

  1. Best Answer

    实际上，我早在 2011 年 7 月就写了一篇关于如何访问视图以进行修改的帖子（在许多视图上修改 DEFINER ）。

    事实是，当存储引擎为 NULL 时，它始终是一个 View。

    View 的实际定义是

    MySQL 5.1/5.5

    mysql> desc information_schema.views;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG        | varchar(512) | NO   |     |         |       |
| TABLE_SCHEMA         | varchar(64)  | NO   |     |         |       |
| TABLE_NAME           | varchar(64)  | NO   |     |         |       |
| VIEW_DEFINITION      | longtext     | NO   |     | NULL    |       |
| CHECK_OPTION         | varchar(8)   | NO   |     |         |       |
| IS_UPDATABLE         | varchar(3)   | NO   |     |         |       |
| DEFINER              | varchar(77)  | NO   |     |         |       |
| SECURITY_TYPE        | varchar(7)   | NO   |     |         |       |
| CHARACTER_SET_CLIENT | varchar(32)  | NO   |     |         |       |
| COLLATION_CONNECTION | varchar(32)  | NO   |     |         |       |
+----------------------+--------------+------+-----+---------+-------+
10 rows in set (0.02 sec)

    MySQL 5.0

    mysql> desc information_schema.views;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       |
| TABLE_NAME      | varchar(64)  | NO   |     |         |       |
| VIEW_DEFINITION | longtext     | NO   |     | NULL    |       |
| CHECK_OPTION    | varchar(8)   | NO   |     |         |       |
| IS_UPDATABLE    | varchar(3)   | NO   |     |         |       |
| DEFINER         | varchar(77)  | NO   |     |         |       |
| SECURITY_TYPE   | varchar(7)   | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

    要查看 sakila.actor_info 的视图定义，您可以运行

    SELECT * FROM information_schema.views
WHERE table_schema='sakila' AND table_name='actor_info'\G

    或者

    SHOW CREATE VIEW sakila.actor_info\G
    • 6

  2. 我想我会添加一些有趣的东西。根据一些测试，我发现如果 MyISAM 表损坏，引擎为 NULL：

    mysql> repair table TEST.test_myisam;
^CCtrl-C -- sending "KILL QUERY 10" to server ...
Ctrl-C -- query aborted.
+---------------------+--------+----------+------------------------------+
| Table               | Op     | Msg_type | Msg_text                     |
+---------------------+--------+----------+------------------------------+
+---------------------+--------+----------+------------------------------+
2 rows in set (0.37 sec)

mysql> select table_type, engine, table_rows from information_schema.tables where table_schema = 'TEST' and table_name = 'test_myisam';
+------------+--------+------------+
| table_type | engine | table_rows |
+------------+--------+------------+
| BASE TABLE | NULL   |       NULL |
+------------+--------+------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                |
+---------+------+----------------------------------------------------------------------------------------+
| Warning |  144 | Table './TEST/test_myisam' is marked as crashed and last (automatic?) repair failed |
+---------+------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    这是在 5.6.21 完成的。

    我还测试了手动破坏数据文件，例如

    echo "1" >> /path/to/db/TEST/test_myisam.MYD

    然后做：

    mysql> check table TEST.test_myisam;
+------------------+-------+----------+---------------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                                |
+------------------+-------+----------+---------------------------------------------------------+
| TEST.test_myisam | check | warning  | Size of datafile is: 41943042       Should be: 41943040 |
| TEST.test_myisam | check | error    | got error: 127 when reading datafile at record: 2097152 |
| TEST.test_myisam | check | error    | Corrupt                                                 |
+------------------+-------+----------+---------------------------------------------------------+
3 rows in set (0.28 sec)

    产生相同的结果：

    mysql> select table_type,engine, table_rows from information_schema.tables where table_schema = 'TEST' and table_name = 'test_myisam';
+------------+--------+------------+
| table_type | engine | table_rows |
+------------+--------+------------+
| BASE TABLE | NULL   |       NULL |
+------------+--------+------------+
1 row in set, 1 warning (0.00 sec)
    • 4

相关问题