7.1. Database Properties

MySQL Server manages data by performing storage, retrieval, and manipulation of data records. Records are organized into tables, and tables are organized into databases. In MySQL, databases are stored in a common location called the "data directory." Each MySQL server has a data directory under which it manages the contents of its databases. With respect to databases, the data directory has the following structure:

• The server represents each database using a subdirectory of the data directory. This subdirectory is called a "database directory." The data directory therefore is the parent of all database directories.

• A database directory has the same name as the database that it represents. For example, a database named world corresponds to a database directory named world under the data directory.

• MySQL uses the database directory to manage the components of the database such as its tables. A database may be empty or have one or more tables. A database directory may also contain files for other database objects such as triggers.

• Each database directory has a default character set and collation. You can specify these properties for a database when you create it. The properties are stored in a file named db.opt in the database directory.

• Databases cannot be nested; one database cannot contain another.

The preceding description of data directory organization indicates that MySQL Server can manage multiple databases, each of which may contain multiple tables. MySQL does not place any limits on the number of databases, although your operating system or filesystem might: If the filesystem on which the data directory resides has a limit on the number of subdirectories a directory may contain, MySQL can create no more than that number of database directories with which to represent databases.

Another word for "database" is "schema." In MySQL 5, statements that use the DATABASE keyword can be written with SCHEMA instead. The same is true for DATABASES and SCHEMAS. For example, CREATE SCHEMA is the same as CREATE DATABASE, and SHOW SCHEMAS is the same as SHOW DATABASES. This study guide generally uses DATABASE and DATABASES, but you should recognize that statements may use either pair of keywords. Also, GUI tools such as MySQL Query Browser and MySQL Administrator use the term "schema." You should recognize when using those programs that the two terms mean the same thing.

The structure of database directories in relation to table storage is discussed in Chapter 8, "Tables and Indexes," and Chapter 29, "Storage Engines." The structure of the database directory is discussed further in Chapter 23, "MySQL Architecture."