Additional Resources
1317
You can check whether the
ARCHIVE
storage engine is available with this statement:
mysql>
SHOW VARIABLES LIKE 'have_archive';
When you create an
ARCHIVE
table, the server creates a table format file in the database directory.
The file begins with the table name and has an
.frm
extension. The storage engine creates other files,
all having names beginning with the table name. The data and metadata files have extensions of
.ARZ
and
.ARM
, respectively. An
.ARN
file may appear during optimization operations.
The
ARCHIVE
engine supports
INSERT
and
SELECT
, but not
DELETE
,
REPLACE
, or
UPDATE
. It
does support
ORDER BY
operations,
BLOB
columns, and basically all but spatial data types (see
Section 12.16.4.1, “MySQL Spatial Data Types”
). The
ARCHIVE
engine uses row-level locking.
Storage: Rows are compressed as they are inserted. The
ARCHIVE
engine uses
zlib
lossless data
compression (see
http://www.zlib.net/
). You can use
OPTIMIZE TABLE
to analyze the table and pack
it into a smaller format (for a reason to use
OPTIMIZE TABLE
, see later in this section). Beginning with
MySQL 5.0.15, the engine also supports
CHECK TABLE
. There are several types of insertions that are
used:
• An
INSERT
statement just pushes rows into a compression buffer, and that buffer flushes as
necessary. The insertion into the buffer is protected by a lock. A
SELECT
forces a flush to occur,
unless the only insertions that have come in were
INSERT DELAYED
(those flush as necessary).
See
Section 13.2.5.2, “
INSERT DELAYED
Syntax”
.
• A bulk insert is visible only after it completes, unless other inserts occur at the same time, in which
case it can be seen partially. A
SELECT
never causes a flush of a bulk insert unless a normal insert
occurs while it is loading.
Retrieval: On retrieval, rows are uncompressed on demand; there is no row cache. A
SELECT
operation performs a complete table scan: When a
SELECT
occurs, it finds out how many rows are
currently available and reads that number of rows.
SELECT
is performed as a consistent read. Note
that lots of
SELECT
statements during insertion can deteriorate the compression, unless only bulk or
delayed inserts are used. To achieve better compression, you can use
OPTIMIZE TABLE
or
REPAIR
TABLE
. The number of rows in
ARCHIVE
tables reported by
SHOW TABLE STATUS
is always accurate.
See
Section 13.7.2.5, “
OPTIMIZE TABLE
Syntax”
,
Section 13.7.2.6, “
REPAIR TABLE
Syntax”
, and
Section 13.7.5.33, “
SHOW TABLE STATUS
Syntax”
.
Additional Resources
• A forum dedicated to the
ARCHIVE
storage engine is available at
http://forums.mysql.com/list.php?
112
.
14.9. The
CSV
Storage Engine
The
CSV
storage engine stores data in text files using comma-separated values format. It is unavailable
on Windows until MySQL 5.1.
The
CSV
storage engine is included in MySQL binary distributions (except on Windows). To enable this
storage engine if you build MySQL from source, invoke
configure
with the
--with-csv-storage-
engine
option.
To examine the source for the
CSV
engine, look in the
sql/examples
directory of a MySQL source
distribution.
When you create a
CSV
table, the server creates a table format file in the database directory. The file
begins with the table name and has an
.frm
extension. The storage engine also creates a data file.
Its name begins with the table name and has a
.CSV
extension. The data file is a plain text file. When
you store data into the table, the storage engine saves it into the data file in comma-separated values
format.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
