- 12.1 Data Definition Statements
- 12.1.1 ALTER DATABASE Syntax
- 12.1.2 ALTER EVENT Syntax
- 12.1.3 ALTER FUNCTION Syntax
- 12.1.4 ALTER PROCEDURE Syntax
- 12.1.5 ALTER SERVER Syntax
- 12.1.6 ALTER TABLE Syntax
- 12.1.7 ALTER VIEW Syntax
- 12.1.8 CREATE DATABASE Syntax
- 12.1.9 CREATE EVENT Syntax
- 12.1.10 CREATE FUNCTION Syntax
- 12.1.11 CREATE INDEX Syntax
- 12.1.12 CREATE PROCEDURE and CREATE FUNCTION Syntax
- 12.1.13 CREATE SERVER Syntax
- 12.1.14 CREATE TABLE Syntax
- 12.1.15 CREATE TRIGGER Syntax
- 12.1.16 CREATE VIEW Syntax
- 12.1.17 DROP DATABASE Syntax
- 12.1.18 DROP EVENT Syntax
- 12.1.19 DROP FUNCTION Syntax
- 12.1.20 DROP INDEX Syntax
- 12.1.21 DROP PROCEDURE and DROP FUNCTION Syntax
- 12.1.22 DROP SERVER Syntax
- 12.1.23 DROP TABLE Syntax
- 12.1.24 DROP TRIGGER Syntax
- 12.1.25 DROP VIEW Syntax
- 12.1.26 RENAME TABLE Syntax
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
ALTER DATABASE enables you to
change the overall characteristics of a database. These
characteristics are stored in the db.opt file
in the database directory. To use ALTER
DATABASE, you need the
ALTER privilege on the database.
ALTER
SCHEMA is a synonym for ALTER
DATABASE.
The CHARACTER SET clause changes the default
database character set. The COLLATE clause
changes the default database collation. Section 9.1, “Character Set Support”,
discusses character set and collation names.
You can see what character sets and collations are available
using, respectively, the SHOW CHARACTER
SET and SHOW COLLATION
statements. See Section 12.4.5.4, “SHOW CHARACTER SET Syntax”, and
Section 12.4.5.5, “SHOW COLLATION Syntax”, for more information.
The database name can be omitted from the first syntax, in which case the statement applies to the default database.
The syntax that includes the UPGRADE DATA DIRECTORY
NAME clause updates the name of the directory associated
with the database to use the encoding implemented in MySQL 5.1 for
mapping database names to database directory names (see
Section 8.2.3, “Mapping of Identifiers to File Names”). This clause is for use
under these conditions:
It is intended when upgrading MySQL to 5.1 or later from older versions.
It is intended to update a database directory name to the current encoding format if the name contains special characters that need encoding.
The statement is used by mysqlcheck (as invoked by mysql_upgrade).
For example,if a database in MySQL 5.0 has a name of
a-b-c, the name contains instance of the
‘-’ character. In 5.0, the database
directory is also named a-b-c, which is not
necessarily safe for all file systems. In MySQL 5.1 and up, the
same database name is encoded as a@002db@002dc
to produce a file system-neutral directory name.
When a MySQL installation is upgraded to MySQL 5.1 or later from
an older version,the server displays a name such as
a-b-c (which is in the old format) as
#mysql50#a-b-c, and you must refer to the name
using the #mysql50# prefix. Use
UPGRADE DATA DIRECTORY NAME in this case to
explicitly tell the server to re-encode the database directory
name to the current encoding format:
ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;
After executing this statement, you can refer to the database as
a-b-c without the special
#mysql50# prefix.
MySQL Enterprise. In a production environment, alteration of a database is not a common occurrence and may indicate a security breach. Advisors provided as part of the MySQL Enterprise Monitor automatically alert you when data definition statements are issued. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
User Comments
Add your own comment.