Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Storage Engines: MyISAM, InnoDB and BDB

By: FYIcenter.com

Part:   1  2  3  4  5   6 

(Continued from previous part...)

How To Create a New Table Using the CSV Storage Engine?

CSV (Comma-Separated Values) storage engine stores table data in text files in comma-separated value format.

CSV is not the default storage engine. You need to specify "ENGINE = CSV" at the end of the "CREATE TABLE" statement to create new tables with the CSV storage engine. The tutorial exercise below shows you a good example:

>cd \mysql\bin
>mysql -u dev -piyf fyi

mysql> CREATE TABLE fyi_csv (
  id INTEGER PRIMARY KEY,
  title VARCHAR(80), 
  count INTEGER )
  ENGINE = CSV;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> SHOW CREATE TABLE fyi_csv;
  CREATE TABLE `fyi_csv` (
  `id` int(11) NOT NULL,
  `title` varchar(80) default NULL,
  `count` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
1 row in set (0.02 sec)

Apparently, the CSV storage engine is not supported in "mysqld-max" version of the MySQL server. You need to recompile MySQL server from source with the "--with-csv-storage-engine" option.

How To Create a New Table Using the MEMORY Storage Engine?

MEMORY storage engine stores table data in computer system memory. This is good for creating temporary tables.

MEMORY is not the default storage engine. You need to specify "ENGINE = MEMORY" at the end of the "CREATE TABLE" statement to create new tables with the MEMORY storage engine. The tutorial exercise below shows you a good example:

>cd \mysql\bin
>mysql -u dev -piyf fyi

mysql> CREATE TABLE fyi_memory (
  id INTEGER PRIMARY KEY,
  title VARCHAR(80), 
  count INTEGER )
  ENGINE = MEMORY;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> SHOW CREATE TABLE fyi_memory;
  CREATE TABLE `fyi_memory` (
  `id` int(11) NOT NULL,
  `title` varchar(80) default NULL,
  `count` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 
1 row in set (0.02 sec)

What Happens to MEMORY Tables When MySQL Server Is Stopped?

If you have data rows stored in a table with the MEMORY storage engine, and the MySQL server has been shutdown by the DBA, all data rows will be removed. But the table structure will remain in the server.

The tutorial exercise below shows you a good example of using MEMORY tables:

>cd \mysql\bin\mysql -u dev -piyf fyi

mysql> INSERT INTO fyi_memory (id) VALUES (-1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO fyi_memory (id, count) VALUES (-2, 987);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM fyi_memory;
+----+-------+-------+
| id | title | count |
+----+-------+-------+
| -1 | NULL  |  NULL |
| -2 | NULL  |   987 |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql> quit;

>\mysql\bin\mysqladmin -u root -pretneciyf shutdown

>\mysql\bin\mysqld-max

>\mysql\bin\mysql -u dev -piyf fyi

mysql> SELECT * FROM fyi_memory;
Empty set (0.00 sec)

(Continued on next part...)

Part:   1  2  3  4  5   6