Rechercher dans le manuel MySQL
15.6.1.1 Creating InnoDB Tables
To create an InnoDB
table, use the
CREATE TABLE
statement.
You do not need to specify the ENGINE=InnoDB
clause if InnoDB
is defined as the default
storage engine, which it is by default. To check the default
storage engine, issue the following statement:
- +--------------------------+
- | @@default_storage_engine |
- +--------------------------+
- +--------------------------+
You might still use ENGINE=InnoDB
clause if you
plan to use mysqldump or replication to replay
the CREATE TABLE
statement on a
server where the default storage engine is not
InnoDB
.
An InnoDB
table and its indexes can be created
in the system
tablespace, in a
file-per-table
tablespace, or in a
general tablespace.
When innodb_file_per_table
is
enabled, which is the default, an InnoDB
table
is implicitly created in an individual file-per-table tablespace.
Conversely, when
innodb_file_per_table
is
disabled, an InnoDB
table is implicitly created
in the InnoDB
system tablespace. To create a
table in a general tablespace, use
CREATE TABLE ...
TABLESPACE
syntax. For more information, see
Section 15.6.3.3, “General Tablespaces”.
When you create a table in a file-per-table tablespace, MySQL
creates an .ibd tablespace
file in a database directory under the MySQL data directory, by
default. A table created in the InnoDB
system
tablespace is created in an existing
ibdata file, which resides in
the MySQL data directory. A table created in a general tablespace
is created in an existing general tablespace
.ibd file. General tablespace
files can be created inside or outside of the MySQL data
directory. For more information, see
Section 15.6.3.3, “General Tablespaces”.
Internally, InnoDB
adds an entry for each table
to the data dictionary. The entry includes the database name. For
example, if table t1
is created in the
test
database, the data dictionary entry for
the database name is 'test/t1'
. This means you
can create a table of the same name (t1
) in a
different database, and the table names do not collide inside
InnoDB
.
InnoDB Tables and Row Formats
The default row format for InnoDB
tables is
defined by the
innodb_default_row_format
configuration option, which has a default value of
DYNAMIC
.
Dynamic
and
Compressed
row format allow you to take advantage of
InnoDB
features such as table compression and
efficient off-page storage of long column values. To use these
row formats,
innodb_file_per_table
must be
enabled (the default).
Alternatively, you can use
CREATE TABLE ...
TABLESPACE
syntax to create an
InnoDB
table in a general tablespace. General
tablespaces support all row formats. For more information, see
Section 15.6.3.3, “General Tablespaces”.
CREATE TABLE ...
TABLESPACE
syntax can also be used to create
InnoDB
tables with a
Dynamic
row format in the system tablespace,
alongside tables with a Compact
or
Redundant
row format.
For more information about InnoDB
row
formats, see Section 15.10, “InnoDB Row Formats”. For how to
determine the row format of an InnoDB
table
and the physical characteristics of InnoDB
row formats, see Section 15.10, “InnoDB Row Formats”.
Always define a primary
key for an InnoDB
table, specifying
the column or columns that:
Are referenced by the most important queries.
Are never left blank.
Never have duplicate values.
Rarely if ever change value once inserted.
For example, in a table containing information about people, you
would not create a primary key on (firstname,
lastname)
because more than one person can have the
same name, some people have blank last names, and sometimes
people change their names. With so many constraints, often there
is not an obvious set of columns to use as a primary key, so you
create a new column with a numeric ID to serve as all or part of
the primary key. You can declare an
auto-increment column
so that ascending values are filled in automatically as rows are
inserted:
- # The value of ID can act like a pointer between related items in different tables.
- # The primary key can consist of more than one column. Any autoinc column must come first.
Although the table works correctly without defining a primary
key, the primary key is involved with many aspects of
performance and is a crucial design aspect for any large or
frequently used table. It is recommended that you always specify
a primary key in the CREATE TABLE
statement. If you create the table, load data, and then run
ALTER TABLE
to add a primary key
later, that operation is much slower than defining the primary
key when creating the table.
To view the properties of an InnoDB
table,
issue a SHOW TABLE STATUS
statement:
- *************************** 1. row ***************************
- Name: t1
- Version: 10
- Row_format: Compact
- Rows: 0
- Data_length: 16384
- Max_data_length: 0
- Index_length: 0
- Data_free: 0
- Create_time: 2015-03-16 15:13:31
- Update_time: NULL
- Check_time: NULL
- Collation: utf8mb4_0900_ai_ci
- Create_options:
For information about SHOW TABLE
STATUS
output, see
Section 13.7.6.36, “SHOW TABLE STATUS Syntax”.
InnoDB
table properties may also be queried
using the InnoDB
Information Schema system
tables:
- *************************** 1. row ***************************
- TABLE_ID: 45
- NAME: test/t1
- FLAG: 1
- N_COLS: 5
- SPACE: 35
- ROW_FORMAT: Compact
- ZIP_PAGE_SIZE: 0
- SPACE_TYPE: Single
For more information, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-using-innodb-tables.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.