Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm just reading a book and in one of the examples I get notices:

  • That the engine in the information_schema.table is null, how is that possible?
  • Can I create tables without any engine??, is there any pro in that?enter image description here
share|improve this question
    
I'n not an expert in MySQL but the objects that have a "NULL engine" are all views. So maybe a view doesn't have an engine because it is not a table and it might not make sense to even discuss the "engine for a view". –  FrustratedWithFormsDesigner Apr 26 '12 at 17:12
    
I think you are right, I didn't see the relation between the "base table" and view. I learned something new today –  jcho360 Apr 26 '12 at 17:21
    
What version of MySQL is this ??? –  RolandoMySQLDBA Apr 26 '12 at 17:24
    
Server version: 5.5.9 Source distribution –  jcho360 Apr 26 '12 at 17:30
add comment

1 Answer 1

up vote 2 down vote accepted

I actually wrote a post back in July 2011 ( Modify DEFINER on Many Views ) about how to access views for modification.

It is a fact that when the storage engine is NULL, it is always a View.

The actual definition of the View is

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)

To see what the view definition is for sakila.actor_info you can either run

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

or

SHOW CREATE VIEW sakila.actor_info\G
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.