• Choose a category
• All
• Classic-Fiction

By Root 22162 0

cannot be the ASCII value of 0 (0x00) or 255 (0xff)—these are reserved. Database names should not include single or double quotation marks or end with a space. If you want a database name to include quotes, though, you will have to enable the SQL mode of ANSI_QUOTES. This can be done with the --sql-mode server option. As of version 5.1.6 of MySQL, database names can contain backslashes, forward slashes, periods, and other characters that may not be permitted in a directory name at the filesystem level. If you use a name that is a reserved word, you must always enclose it in quotes when referring to it.

Special characters in the name are encoded in the filesystem names. If you upgrade your system to a new version of MySQL and you have a database that has special characters in its name, the database will be displayed with a prefix of #mysql50#. For instance, a database named human-resources will be displayed as #mysql50#human-resources. You won’t be able to access this database. Don’t try to change the name from within MySQL, as you may destroy data. Instead, there are a couple of methods you can use. One is to shut down MySQL, go to the MySQL data directory, and rename the subdirectory that contains the database to a name without the unacceptable character (e.g., from human-resources to human_resources) and then restart MySQL. Another method would be to use the mysqlcheck utility, like so:

mysqlcheck --check-upgrade --fix-db-names

The --fix-db-names option was added in version 5.1.7 of MySQL. For more options with this utility, see Chapter 16.

As of version 4.1.1, a db.opt file is added to the filesystem subdirectory created for the database in the MySQL server’s data directory. This file contains a couple of settings for the database. You can specify these settings as options to this SQL statement in a comma-separated list.

Currently, two options are available: CHARACTER SET and COLLATE. Here is an example of how you can create a database with both of these options:

CREATE DATABASE sales_prospects

CHARACTER SET latin1

COLLATE latin1_bin;

There is no equals sign before the value given for each option and no comma between the first and second option. Here are the contents of the db.opt file created for this statement:

default-character-set=latin1

default-collation=latin1_bin

For a list of character sets available on your system, use the SHOW CHARACTER SET statement. For a list of collation possibilities, use the SHOW COLLATION statement. MySQL occasionally adds new character sets and collations to new versions of MySQL. If you need one of the new ones, you’ll have to upgrade your server to the new version.

Name

CREATE INDEX

Synopsis

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index

[USING type|TYPE type]

[USING type|TYPE type]

ON table (column [(length)] [ASC|DESC], ...)

Use this statement to add an index to a table after it has been created. This is an alias of the clause of the ALTER TABLE statement that adds an index. You can add indexes only to MyISAM, InnoDB, and BDB types of tables. You can also create these tables with indexes, as shown in the CREATE TABLE statement later in this chapter.

To prevent duplicates, add the UNIQUE flag between the CREATE keyword and INDEX. Only columns with CHAR, TEXT, and VARCHAR data types of MyISAM tables can be indexed with FULLTEXT indexes.

Creating UNIQUE indexes

CREATE UNIQUE INDEX index

ON table (column, ...)

After the INDEX keyword, the name of the index or key is given. This name can be the same as one of the columns indexed, or a totally new name.

You can specify the type of index with the USING keyword. For MyISAM and InnoDB tables, BTREE is the default, but RTREE is also available as of version 5.0 of MySQL. The TYPE keyword is an alias for USING.

For wide columns, it may be advantageous to specify a maximum number of characters to use from a column for indexing. This can speed up indexing and reduce the size of index files on the filesystem.

Although there is an ASC option for sorting indexes in ascending order and a DESC option for sorting in descending