MySQLクイック・リファレンス

この文書は、osCommerceで使用しているデータベースMySQLの基本的な使い方について解説しています。おもにデータベースの管理者よりも、ユーザに向けて書かれています。

目次

はじめに

ここでは、MySQLサーバは起動しているものとして説明します。

MySQLの文字について

MySQLでは、データベース名やテーブル名の大文字と小文字を区別しています。それ以外は区別しません。

MySQLのフィールド名やテーブル名に使える文字は、英数字と_（アンダーバー）、$、サーバのキャラクタセットにある文字です。

知っておきたいコマンド

mysqladmin（データベースの作成・削除や、MySQLサーバの情報を得る）

mysqlshow（データベース、テーブルの情報を表示する）

mysql（MySQLクライアントの起動）

mysqldump（データベース、テーブルをダンプする）

myisamchk（テーブルの検査や修復、最適化を行う）

よく使うmysqlコマンドについて

mysqlコマンドってなにするもの?

mysqlコマンドは、対話型のMySQLクライアントを起動します。

MySQLクライアントでは、SQL文を実行したりデータベースやテーブルの情報を見ることができます。

mysqlコマンドによりMySQLクライアントを起動すると、プロンプトが以下のようになります。 mysql>

mysqlコマンドのヘルプが見たい

mysqlコマンドのヘルプは、オプション「--help」や「-?」で表示されます。

$ mysql --help

MySQLサーバに接続する

ユーザとパスワードを指定して、MySQLサーバに接続します。

オプション「-p」を指定すると、パスワードの入力要求プロンプトが表示されます。

$ mysql -uユーザ名 -p データベース名 Enter password: [パスワード]

$ mysql --user=ユーザ名 --password=パスワード データベース名

*パスワードが確認できる(2)での接続は避けて、(1)の方法で接続するようにしましょう。

MySQLサーバから切断する

MySQLサーバから切断するには、「\q」、「exit」、「quit」、「Control-D」のいずれかを入力します。

mysql> \q Bye

知っておくと便利なキー操作

mysql>プロンプトでのキー操作 キー 説明 [←]か[Ctrl]+[B] カーソルを左に移動する [→]か[Ctrl]+[F] カーソルを右に移動する [Ctrl]+[A] カーソルを行頭に移動する [Ctrl]+[E] カーソルを行末に移動する [↑]か[Ctrl]+[P] 前のSQL文やコマンドを表示する（ヒストリ） [↓]か[Ctrl]+[N] 後のSQL文やコマンドを表示する（ヒストリ） [DEL]か[Ctrl]+[D] 一文字削除 [BS]か[Ctrl]+[H] カーソルの前の一文字を削除 [Tab] 途中まで入力したSQL構文や、フィールド名を補完する

操作に困ったときのヘルプ表示

MySQLクライアントのコマンド一覧を表示するには、「\h」、「\help」、「\?」のいずれかを入力します。

途中まで入力したSQL文をキャンセルする

MySQLクライアントでは、複数行にまたがりSQL文を入力することができるます。入力を途中でキャンセルするには「\c」と入力します。

mysql> select * -> from テーブル名 -> where カラム名 >= 150 -> \c mysql>

接続に関する情報を知りたい

コネクションIDや、データベース名、ユーザ名、バージョンなどを知ることができます。

mysql> \s

データベースを選択（切替）する

mysql> use データベース名; Database changed

ファイルに記述したSQL文を実行する

$ mysql [-t -N] データベース名 < ファイル名

[-t | --table]：表形式（区切り線を出力）で結果を表示する。 [-N | --skip-column-names]：カラム名を出力しない。

実行結果をファイルに出力する

$ mysql データベース名 > ファイル名 select * from table1; 入力待ちになるので、SQL文を入力する（SQL文の最後は「;」で終わらせる） \q （処理を終了する）

mysql [-t -N] データベース名 > ファイル名

*オプション「-t」、「-N」についてはファイルに記述したSQL文を実行するを参照してください。

ファイルに記述したSQL文の実行結果をファイルに出力する

$ mysql [-t -N] データベース名 < (SQL)ファイル名 > (OUT)ファイル名

*オプション「-t」、「-N」については、ファイルに記述したSQL文を実行するを参照してください。

ファイルに記述したSQL文の実行結果をファイルに追加出力する

$ mysql [-t -N] データベース名 < (SQL)ファイル名 >> (OUT)ファイル名

*オプション「-t」、「-N」については、ファイルに記述したSQL文を実行するを参照してください。

コマンドの実行結果をmysqlコマンドに渡して実行する

$ cat ファイル名 | mysql [-t -N] データベース名

$ echo "select * from テーブル名" | mysql [-t -N] データベース名

*オプション「-t」、「-N」については、ファイルに記述したSQL文を実行するを参照してください。

コマンドの実行結果をmysqlコマンドに渡して実行し、ファイルに出力する

$ cat (SQL)ファイル名 | mysql [-t -N] データベース名 > (OUT)ファイル名

$ echo "select * from テーブル名" | mysql [-t -N] データベース名 > (OUT)ファイル名

*オプション「-t」、「-N」については、ファイルに記述したSQL文を実行するを参照してください。

MySQLサーバの情報が知りたい

サーバが起動しているか確認する

aliveが返ってくれば起動しています。

$ mysqladmin ping mysqld is alive

実行中のプロセスを確認する

$ mysqladmin processlist

$ mysqladmin processlist -uユーザ名 -p Enter password:

mysql> SHOW PROCESSLIST;

サーバのステータス情報を確認する

$ mysqladmin extended-status

$ mysqladmin extended-status -uユーザ名 -p Enter password:

mysql> SHOW STATUS;

mysql> SHOW STATUS LIKE wild;

mysql> SHOW STATUS LIKE 'A%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Aborted_clients | 0 | | Aborted_connects | 1 | +------------------+-------+ 2 rows in set (0.00 sec)

サーバの変数を確認する

$ mysqladmin variables

$ mysqladmin variables -uユーザ名 -p

mysql> SHOW VARIABLES;

mysql> SHOW VARIABLES LIKE wild;

mysql> SHOW VARIABLES LIKE 'la%'; +---------------------+---------------------------+ | Variable_name | Value | +---------------------+---------------------------+ | language | /usr/share/mysql/english/ | | large_files_support | ON | +---------------------+---------------------------+ 2 rows in set (0.00 sec)

ユーザとその権限を管理するには

ユーザとその権限を管理するシステム管理者

システム管理者はMySQLのユーザ「root」で、MySQLをインストールしたときに登録されてます。

ユーザとその権限に関するデータは、データベース「mysql」内の5つのテーブルに格納されており、GRANT構文やREVOKE構文により管理します。

mysqlコマンドでデータベース「mysql」に、MySQLのユーザ「root」で接続します。

$ mysql -uroot -p mysql

ユーザを登録して権限を与えるGRANT構文について

GRANT構文によりユーザを登録します。同時に、その権限やパスワードの設定管理が行えます。（同じユーザに対して何度でも実行できるので、権限の設定を編集できます。）

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {テーブル名 | * | *.* | データベース名.*} TO ユーザ名 [IDENTIFIED BY 'パスワード'] [, ユーザ名 [IDENTIFIED BY 'パスワード'] ...] [REQUIRE [{SSL| X509}] [CIPHER cipher [AND]] [ISSUER issuer [AND]] [SUBJECT subject] ] [WITH GRANT OPTION]

GRANT構文のpriv_typeには、次のようなものがあります。

ALL PRIVILEGES(*)：すべての権限。（ALLと同義語） ALTER(*)：ALTER TABLE構文が実行できる権限。 CREATE(*)：データベースとテーブルの作成が行える権限。 DROP(*)：データベースとテーブルの削除が行える権限。 FILE(*)：LOAD DATA構文とSELECT 〜 INTO OUTFILE構文を実行できる権限。 PROCESS(*)：mysqladminコマンドのprocesslistとkill命令を実行できる権限。 RELOAD(*)：mysqladminコマンドのreload、refresh、flush-privileges、 flush-hosts、flush-logs、flush-tables命令を実行できる権限。 SHUTDOWN(*)：mysqladminコマンドのshutdown命令を実行できる権限。 DELETE：DELETE構文を実行できる権限。 INSERT：INSERT構文を実行できる権限。 SELECT：SELECT構文を実行できる権限。 UPDATE：UPDATE構文を実行できる権限。 INDEX：インデックスの作成と削除が行える権限。 REFERENCES：テーブルのリファレンスが参照できる権限。 USAGE：すべての権限がない。（"no privileges"と同義） (*)：通常のデータ操作以上の権限を与えるので、十分な検討が必要です。

ユーザ名には、ユーザとホストを指定します。 ■書式： ユーザ@ホスト名 ユーザ：登録するユーザ ホスト：登録するユーザは、どのホストからMySQLサーバにアクセスするのか （localhost | ホスト名（IPアドレス） | "%"（ワイルドカード）） ■例： ユーザ名 or ユーザ名@localhost or ユーザ名@IPアドレス or ユーザ名@"%"

* GRANT構文を実行した後に、FLUSH PRIVILEGES構文を必ず実行します。

*「*」、「%」はワイルドカードです。

*MySQL 3.22.11.以上で実行できます。

ユーザの権限を取り去るREVOKE構文について

REVOKE構文により、登録しているユーザの権限を取り去ります。（ユーザの削除は行いません）

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {テーブル名 | * | *.* | データベース名.*} FROM ユーザ名 [, ユーザ名 ...]

* REVOKE構文を実行した後に、FLUSH PRIVILEGES構文を必ず実行します。

* priv_typeについては、ユーザを登録して権限を与えるGRANT構文についてを参照してください。

管理者権限を持つユーザを登録する

MySQLのユーザ「root」と同じ、管理者権限を持つユーザ「user1」を登録するには、次のような2つのGRANT構文を実行します。MySQLのユーザ「user1」にはパスワードを設定しています。

mysql> GRANT ALL PRIVILEGES ON *.* TO user1@"%" -> IDENTIFIED BY 'パスワード' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO user1@localhost -> IDENTIFIED BY 'パスワード' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;

どのホストからでもサーバに接続できるようにするために、「user1@"%"」と「user1@localhost」を指定した、2つのGRANT構文を実行しています。（localhostから接続できるようにするためには、「user1@localhost」を指定する必要があります）

また、「WITH GRANT OPTION」によりGRANT構文の実行権限を与えています。

権限：ALL PRIVILEGES（すべての権限） データベース名.テーブル名：*.*（すべてのデータベースのすべてのテーブル） ユーザ名：user1@"%" （すべてのホスト「"%"」の、ユーザ「user1」） user1@localhost（ローカルホストの、ユーザ「user1」） GRANT実行権限：（GRANT構文を実行する権限）

通常のデータ操作が行えるユーザを登録する

SELECT、INSERT、UPDATE、DELETE構文により、通常のデータ操作が行えるユーザを登録します。

mysql> GRANT SELECT,INSERT,UPDATE,DELETE -> ON データベース名.* -> TO ユーザ名@localhost -> IDENTIFIED BY 'パスワード'; mysql> FLUSH PRIVILEGES;

*1つのテーブルに対して権限を持つユーザを登録するときには、「データベース名.*」を「データベース名.テーブル名」とします。

*他のホストから接続するユーザを登録するときには、「localhost」を「ホスト名（IPアドレス）」とします。

ユーザや権限を管理するテーブルについて

データベース「mysql」には、ユーザや権限を管理するための5つのテーブルがあります。5つのテーブルには、4つの権限レベルに対応した権限が格納されます。

user：Globalレベル ユーザの基本的な定義を管理するテーブルです。 サーバ上のすべてのデータベースに適用される権限について格納します。 host：Databaseレベル 接続してきたクライアントのホストに対する権限の定義を管理するテーブルです。 データベース内のすべてのテーブルに適用される権限について格納します。 db：Databaseレベル データベースに対するアクセスの権限の定義を管理するテーブルです。 データベース内のすべてのテーブルに適用される権限について格納します。 tables_priv：Tableレベル テーブルに対する権限の定義を管理するテーブル テーブル内のすべてのフィールドに適用される権限について格納します。 columns_priv：Columnレベル フィールドに対する権限の定義を管理するテーブルです。 テーブル内の一つのフィールドに適用される権限について格納します。

これらのテーブルでは、ホスト、ユーザ、パスワード、データベース、カラム、権限などについて記録しています。権限を記録するカラム名は権限と対応しています。（Y：権限がある、N：権限がない）

ユーザを削除する

ユーザを削除するには、DELETE構文によりmysqlデータベースのユーザや権限を管理する5つのテーブルから、該当するユーザのレコードを削除する必要があります。

また、5つのテーブルから直接データを削除する方法とは別に、次の手順に従ってユーザを削除する方法があります。通常はこちらの方法でユーザを削除してください。

mysql> DELETE FROM mysql.user WHERE user='ユーザ名' and host='ホスト名'; mysql> FLUSH PRIVILEGES;

また、MySQL 4.1.1.で追加されたコマンドでも権限を持たないユーザを削除することができます。このコマンドを利用するには次の手順に従ってください。

mysql> DROP USER ユーザ名;

（注意!：誤って他のユーザを消さないように注意してください。）

SET PASSWORDでパスワードを設定するには

パスワードの設定は、ユーザを登録するGRANT構文以外に、SET PASSWORDがあります。パスワードを変更したい場合は、SET PASSWORDを実行します。

mysql> SET PASSWORD FOR ユーザ名@"%" = PASSWORD('パスワード');

*ユーザ名については、ユーザを登録して権限を与えるGRANT構文についてを参照してください。

パスワードの暗号化

ユーザのパスワードは暗号化する必要があります。

GRANT構文のIDENTIFIED BYで指定したパスワードは暗号化されます。

また、SET PASSWORD構文や、UPDATE構文でmysql.userテーブルを更新するときは、PASSWORD関数を使用してパスワードを設定します。これにより、暗号化されたパスワードが設定されます。

ユーザの権限を確認するには

MySQLには、ユーザの権限を確認することができるPerlスクリプトが附属しています。

ヘルプはオプション「--help」で表示されます。また、簡単な使い方の例はオプション「--howto」で表示されます。

user、db、hostテーブルにより、権限があるときは'Y'を、ないときは'N'を表示します。

# mysqlaccess localhost ユーザ名 データベース名

また、直接mysql.userテーブルをSELECTすることで確認することもできます。このとき、ユーザをWHERE句で指定しなければすべてのユーザの権限が確認できます。

mysql> select * from mysql.user; or mysql> select * from mysql.user \G

* \Gを付けると結果が1レコードごとに垂直に表示されます。

mysql> SELECT * FROM mysql.user WHERE User='ユーザ名';

mysql> SELECT * FROM mysql.user WHERE User like '%a%';

登録されているユーザを確認するには

mysql.userテーブルをSELECTすることで登録されているユーザを確認することができます。節4.10[ユーザの権限を確認するには]でしめしたSQL文とほとんど同じですが、ホスト、ユーザ、パスワードの情報のみ取得するため、SELECTするカラムを指定します。

mysql> SELECT Host, User, Password FROM mysql.user; or mysql> SELECT Host, User, Password FROM mysql.user \G

* \Gを付けると結果が1レコードごとに垂直に表示されます。

mysql> SELECT Host, User, Password FROM mysql.user WHERE User='ユーザ名';

mysql> SELECT Host, User, Password FROM mysql.user WHERE User like '%a%';

データベースやテーブルの操作をしたい

データベースを作成する

# mysqladmin create データベース名

$ mysqladmin -uroot -p create データベース名

mysql> CREATE DATABASE データベース名

MySQL 4.1.からデータベースのキャラクタセットを指定することができます。テーブルを作成する際のデフォルトキャラクタセットとなります。

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] データベース名 [create_specification [, create_specification] ...] create_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

CREATE DATABASE データベース名 CHARACTER SET utf8;

*ここで指定できるキャラクタセットは、utf8、ujis、sjisなどです。

データベースを削除する

削除したデータベースは二度と戻りません。もちろん、データベース内のテーブル定義やデータなどもすべて削除されます。

このコマンドは慎重に実行する必要があります。

# mysqladmin drop データベース名

$ mysqladmin -uroot -p drop データベース名

mysql> DROP DATABASE データベース名

テーブルを作成する

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] テーブル名 [(create_definition,...)] [table_options] [select_statement]

CREATE TABLE構文のcreate_definitionには、次のようなものがあります。

カラム名 type [NOT NULL | NULL] [DEFAULT デフォルト値] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition] or PRIMARY KEY (index_col_name,...) or KEY [インデックス名] (index_col_name,...) or INDEX [インデックス名] (index_col_name,...) or UNIQUE [INDEX] [インデックス名] (index_col_name,...) or FULLTEXT [INDEX] [インデックス名] (index_col_name,...) or [CONSTRAINT symbol] FOREIGN KEY [インデックス名] (index_col_name,...) [reference_definition] or CHECK (expr)

create_definitionのtypeには、次のようなものがあります。

TINYINT[(length)] [UNSIGNED] [ZEROFILL] or SMALLINT[(length)] [UNSIGNED] [ZEROFILL] or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] or INT[(length)] [UNSIGNED] [ZEROFILL] or INTEGER[(length)] [UNSIGNED] [ZEROFILL] or BIGINT[(length)] [UNSIGNED] [ZEROFILL] or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] or CHAR(length) [BINARY] or VARCHAR(length) [BINARY] or DATE or TIME or TIMESTAMP or DATETIME or TINYBLOB or BLOB or MEDIUMBLOB or LONGBLOB or TINYTEXT or TEXT or MEDIUMTEXT or LONGTEXT or ENUM(value1,value2,value3,...) or SET(value1,value2,value3,...)

create_definitionのindex_col_nameには、次のようなものがあります。

カラム名 [(長さ)] or カラム名 [(長さ)] [ASC | DESC] **(MySQL4.1〜)

create_definitionのreference_definitionには、次のようなものがあります。

REFERENCES テーブル名 [(カラム名,...)] [MATCH FULL | MATCH PARTIAL] [ON DELETE reference_option] [ON UPDATE reference_option]

reference_definitionのreference_optionには、次のようなものがあります。

RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

CREATE TABLE構文のtable_optionsは次のように指定します。また、table_optionには、次のようなものがあります。

table_options: table_option [table_option] ...

TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM } or AUTO_INCREMENT = 数値 or AVG_ROW_LENGTH = 数値 or CHECKSUM = {0 | 1} or COMMENT = "文字列" or MAX_ROWS = 数値 or MIN_ROWS = 数値 or PACK_KEYS = {0 | 1 | DEFAULT} or PASSWORD = "string" or DELAY_KEY_WRITE = {0 | 1} or ROW_FORMAT = { DEFAULT | DYNAMIC | FIXED | COMPRESSED } or RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=数値 RAID_CHUNKSIZE=数値 or UNION = (table_name,[table_name...]) or INSERT_METHOD= {NO | FIRST | LAST } or DATA DIRECTORY="absolute path to directory" or INDEX DIRECTORY="absolute path to directory" or DEFAULT CHARACTER SET character_set_name [COLLATE collation_name] **(MySQL4.1〜)

CREATE TABLE構文のselect_statementには、次のようなものがあります。

[IGNORE | REPLACE] SELECT ... (Some legal select statement)

実際にテーブルを作成するには、次のようなCREATE TABLE構文を実行します。

mysql> CREATE TABLE address_book ( -> customers_id int(5) DEFAULT '0' NOT NULL, -> address_book_id int(5) DEFAULT '1' NOT NULL, -> entry_gender char(1) NOT NULL, -> entry_company varchar(32), -> entry_firstname varchar(32) NOT NULL, -> entry_lastname varchar(32) NOT NULL, -> entry_street_address varchar(64) NOT NULL, -> entry_suburb varchar(32), -> entry_postcode varchar(10) NOT NULL, -> entry_city varchar(32) NOT NULL, -> entry_state varchar(32), -> entry_country_id int(5) DEFAULT '0' NOT NULL, -> entry_zone_id int(5) DEFAULT '0' NOT NULL, -> PRIMARY KEY (address_book_id, customers_id) -> );

テーブルを削除する

複数のテーブルを一度に削除することができます。削除したテーブル定義やデータは二度と戻りません。

この構文は慎重に実行する必要があります。

mysql> DROP TABLE IF EXISTS address_book;

DROP TABLE [IF EXISTS] テーブル名 [, テーブル名,...] [RESTRICT | CASCADE]

テーブルの構造を変更する構文

1度作成したテーブルの構造を変更するには、ALTER TABLE構文を実行します。

ALTER [IGNORE] TABLE テーブル名 alter_specification [, alter_specification ...]

ALTER TABLE構文のalter_specificationには、次のようなものがあります。

ADD [COLUMN] create_definition [FIRST | AFTER カラム名 ] or ADD [COLUMN] (create_definition, create_definition,...) or ADD INDEX [インデックス名] (index_col_name,...) or ADD PRIMARY KEY (index_col_name,...) or ADD UNIQUE [インデックス名] (index_col_name,...) or ADD FULLTEXT [インデックス名] (index_col_name,...) or ADD [CONSTRAINT symbol] FOREIGN KEY インデックス名 (index_col_name,...) [reference_definition] or ALTER [COLUMN] カラム名 {SET DEFAULT literal | DROP DEFAULT} or CHANGE [COLUMN] 古いカラム名 create_definition [FIRST | AFTER 新しいカラム名] or MODIFY [COLUMN] create_definition [FIRST | AFTER カラム名] or DROP [COLUMN] カラム名 or DROP PRIMARY KEY or DROP INDEX インデックス名 or DISABLE KEYS or ENABLE KEYS or RENAME [TO] 新しいテーブル名 or ORDER BY col or table_options

テーブルの名前を変更する

ALTER [IGNORE] TABLE テーブル名 RENAME [TO] 新しいテーブル名

mysql> ALTER TABLE テーブル名 RENAME 新しいテーブル名;

カラムの型を変更する

ALTER [IGNORE] TABLE テーブル名 MODIFY [COLUMN] create_definition [FIRST | AFTER カラム名]

* create_definitionについては、テーブルを作成するを参照してください。

mysql> ALTER TABLE テーブル名 MODIFY カラム名 型 NOT NULL DEFAULT デフォルト値;

カラムの名前を変更する

ALTER [IGNORE] TABLE テーブル名 CHANGE [COLUMN] 古いカラム名 create_definition [FIRST | AFTER 新しいカラム名]

* create_definitionについては、テーブルを作成するを参照してください。

mysql> ALTER TABLE テーブル名 CHANGE 古いカラム名 新しいカラム名;

カラムの名前と型を変更する

ALTER [IGNORE] TABLE テーブル名 CHANGE [COLUMN] 古いカラム名 create_definition [FIRST | AFTER 新しいカラム名]

* create_definitionについては、テーブルを作成するを参照してください。

mysql> ALTER TABLE テーブル名 CHANGE 古いカラム名 新しいカラム名 型;

カラムを最後に追加する

ALTER [IGNORE] TABLE テーブル名 ADD [COLUMN] create_definition [FIRST | AFTER カラム名] or ADD [COLUMN] (create_definition, create_definition,...)

* create_definitionについては、テーブルを作成するを参照してください。

mysql> ALTER TABLE テーブル名 ADD 追加するカラム名 型;

カラムを先頭に追加する

ALTER [IGNORE] TABLE テーブル名 ADD [COLUMN] create_definition [FIRST | AFTER カラム名 ]

* create_definitionについては、テーブルを作成するを参照してください。

mysql> ALTER TABLE テーブル名 ADD 追加するカラム名 型 FIRST;

カラムをあるカラムの後に追加する

ALTER [IGNORE] TABLE テーブル名 ADD [COLUMN] create_definition [FIRST | AFTER カラム名 ]

* create_definitionについては、テーブルを作成するを参照してください。

mysql> ALTER TABLE テーブル名 ADD 追加するカラム名 型 AFTER カラム名;

主キー（プライマリ・キー）を追加する

ALTER [IGNORE] TABLE テーブル名 ADD PRIMARY KEY (index_col_name,...)

mysql> ALTER TABLE テーブル名 ADD PRIMARY KEY (カラム名);

ユニークインデックスを追加する

mysql> ALTER TABLE テーブル名 ADD UNIQUE (カラム名);

ALTER [IGNORE] TABLE テーブル名 ADD UNIQUE [インデックス名] (index_col_name,...)

インデックスを追加する

ALTER [IGNORE] TABLE テーブル名 ADD INDEX [インデックス名] (index_col_name,...)

mysql> ALTER TABLE テーブル名 ADD INDEX カラム名;

カラムを削除する

ALTER [IGNORE] TABLE テーブル名 DROP [COLUMN] カラム名

mysql> ALTER TABLE テーブル名 DROP 削除するカラム名;

主キー（プライマリ・キー）を削除する

ALTER [IGNORE] TABLE テーブル名 DROP PRIMARY KEY

mysql> ALTER TABLE テーブル名 DROP PRIMARY KEY;

インデックスを削除する

ALTER [IGNORE] TABLE テーブル名 DROP INDEX インデックス名

mysql> ALTER TABLE テーブル名 DROP INDEX インデックス名;

カラムを自動インクリメントに変更する

ALTER [IGNORE] TABLE テーブル名 MODIFY [COLUMN] create_definition [FIRST | AFTER カラム名]

mysql> ALTER TABLE テーブル名 MODIFY カラム名 INT AUTO_INCREMENT;

* create_definitionについては、テーブルを作成するを参照してください。

SELECTについての情報を得る構文

SELECTを実行したときの動作、テーブルの結合、インデックスの使用についての情報を表示します。カラム[rows]の値は、SELECTの実行で検査する必要があると考えているレコードの数です。大きな値になるほど、クエリの実行速度が遅くなります。テーブルを結合しているときは、それぞれのテーブルについて情報が表示されます。このとき、rowsの値を掛け算した値が、検査するレコード数となります。

EXPLAIN tbl_name or EXPLAIN SELECT select_options

* create_definitionについては、テーブルを作成するを参照してください。

mysql> EXPLAIN select countries_id,countries_name -> from countries -> where countries_id>100 and countries_id>200; +-----------+------+---------------+-----+--------+-----+-----+-----------+ | table | type | possible_keys | key | key_len| ref | rows| Extra | +-----------+------+---------------+-----+--------+-----+-----+-----------+ | countries | ALL | PRIMARY | NULL| NULL| NULL| 239| where used| +-----------+------+---------------+-----+--------+-----+-----+-----------+ 1 row in set (0.00 sec)

データベースやテーブルの情報が知りたい

データベース一覧を表示する

MySQLサーバに作成されたデータベースを一覧表示します。

$ mysqlshow

mysql> SHOW DATABASES;

mysql> SHOW DATABASES LIKE wild;

mysql> SHOW DATABASES LIKE 'ca%'; +----------------+ | Database (ca%) | +----------------+ | catalog | +----------------+ 1 row in set (0.00 sec)

データベースを作成するステートメントを表示する

MySQLサーバに作成したデータベースのCREATE DATABASEステートメントをしめします。データベースのデフォルト・キャラクタセットなども表示されます。

mysql> SHOW CREATE DATABASE データベース名

mysql> SHOW CREATE DATABASE データベース名\G *************************** 1. row *************************** Database: データベース名 Create Database: CREATE DATABASE `データベース名` /*!40100 DEFAULT CHARACTER SET \ utf8 */ 1 row in set (0.00 sec)

* \Gを付けると結果が垂直に表示されるので読みやすくなります。* \Gの後に';'は付けません。（';'を付けるとERROR:が表示されます。）

テーブル一覧を表示する

データベースに作成されたテーブルを一覧表示します。

$ mysqlshow データベース名

mysql> SHOW TABLES;

mysql> SHOW TABLES LIKE wild;

mysql> SHOW TABLES LIKE 'a%'; +---------------------------+ | Tables_in_catalog (a%) | +---------------------------+ | address_book | | address_book_to_customers | | address_format | +---------------------------+ 3 rows in set (0.00 sec)

テーブルを作成するステートメントを表示する

テーブルのCREATE TABLEステートメントをしめします。

mysql> SHOW CREATE TABLE テーブル名;

mysql> SHOW CREATE TABLE address_book\G *************************** 1. row *************************** Table: address_book Create Table: CREATE TABLE `address_book` ( `customers_id` int(11) NOT NULL default '0', `address_book_id` int(11) NOT NULL default '1', `entry_gender` char(1) NOT NULL default '', `entry_company` varchar(32) default NULL, `entry_firstname` varchar(32) NOT NULL default '', `entry_lastname` varchar(32) NOT NULL default '', `entry_street_address` varchar(64) NOT NULL default '', `entry_suburb` varchar(32) default NULL, `entry_postcode` varchar(10) NOT NULL default '', `entry_city` varchar(32) NOT NULL default '', `entry_state` varchar(32) default NULL, `entry_country_id` int(11) NOT NULL default '0', `entry_telephone` varchar(32) default '', `entry_zone_id` int(11) NOT NULL default '0', PRIMARY KEY (`address_book_id`,`customers_id`) ) ENGINE=MyISAM DEFAULT CHARSET=ujis 1 row in set (0.00 sec)

* \Gを付けると結果が垂直に表示されるので読みやすくなります。* \Gの後に';'は付けません。（';'を付けるとERROR:が表示されます。）

テーブルの情報を表示する

データベース内の各テーブルの統計情報を一覧表示します。テーブルが確保している物理サイズを除いて、値は正確ではありません。レコードのカウントはSQLの最適化で使用される大まかな推定値に過ぎません。

*バージョン3.23で導入

# mysqlshow --status データベース名

mysql> SHOW TABLE STATUS [FROM データベース名] [LIKE wild]

一覧表示されるテーブルの情報 カラム 意味 Name テーブル名。 Type テーブル型。 Row_format レコードの保存形式（Fixed、Dynamic、Compressed）。 Rows レコードの数。 Avg_row_length レコードの平均長。 Data_length データファイルの長さ。 Max_data_length データファイルの最大長。 Index_length インデックスファイルの大きさ。 Data_free 割り当てられているが未使用のバイト数。 Auto_increment 次の自動インクリメント値。 Create_time テーブル作成時刻。 Update_time 前回のデータファイル更新時刻。 Check_time 前回のテーブルチェック時刻。 Collation テーブルのキャラクタセットと照合順序（4.1.1 で導入）。 Checksum チェックサム値（ある場合）（4.1.1 で導入）。 Create_options CREATE TABLE で使用される拡張オプション。 Comment テーブル作成時のコメントなど。

カラムの情報を表示する

指定されたテーブルのカラムを表示します。

DESCRIBEはSHOW COLUMNS FROMの簡略形です。DESCRIBEは更にDESCと略すことができます。

SHOW COLUMNSとSHOW FIELDSは同義語です。どちらも同じ結果を得ることができます。

mysql> DESCRIBE テーブル名; or mysql> DESC テーブル名;

mysql> DESCRIBE テーブル名 [col_name | wild];

* [col_name | wild]には、カラム名やSQLのワイルドカード文字‘%’と‘_’を含む文字列を指定できます。

mysql> DESC address_book '______s%'; +----------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-------------+------+-----+---------+-------+ | address_book_id | int(11) | | PRI | 1 | | | entry_street_address | varchar(64) | | | | | | entry_suburb | varchar(32) | YES | | NULL | | | entry_state | varchar(32) | YES | | NULL | | +----------------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM テーブル名;

mysql> SHOW FIELDS FROM テーブル名;

mysql> SHOW COLUMNS FROM テーブル名 FROM データベース名;

mysql> SHOW COLUMNS FROM テーブル名 LIKE wild;

mysql> SHOW COLUMNS FROM address_book LIKE 'a%'; +-----------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------+------+-----+---------+-------+ | address_book_id | int(11) | | PRI | 1 | | +-----------------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)

キャラクタセットを表示する

使用可能なキャラクタセットをすべて表示します。

mysql> SHOW CHARACTER SET;

mysql> SHOW CHARACTER SET LIKE wild;

mysql> SHOW CHARACTER SET LIKE '_j%'; +---------+--------------------+-------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+--------------------+-------------------+--------+ | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | +---------+--------------------+-------------------+--------+ 2 rows in set (0.00 sec)

インデックスの情報を表示する

指定されたテーブルのインデックス情報を表示します。

mysql> SHOW INDEX FROM テーブル名;

mysql> SHOW INDEX FROM テーブル名 FROM データベース名;

データのバックアップ

MySQLでは、データをダンプしてバックアップします。また、データベースのファイルをコピーしてバックアップをすることもできます。

MySQLでは、1つのデータベースを1つのディレクトリとしています。データベースのディレクトリ内に、テーブルと対応したファイルが格納されます。1つのテーブルは、3つのファイルで構成されています。それぞれのファイル名はテーブル名.拡張子で、ファイルの内容により拡張子が異なります。

データベース関連のファイル 拡張子 説明 MYD データ MYI インデックスのデータ frm テーブルの構造

矛盾のないバックアップをとるには

FLUSH TABLES構文で開いているテーブルをすべて閉じます。次に、関連するテーブルをLOCK TABLES構文でロックします。

バックアップが終了したら、UNLOCK TABLES構文でテーブルのロックを解除します。（mysqlコマンドで、MySQLクライアントを起動して実行します。）

データベースのディレクトリをコピーするmysqlhotcopyコマンド

指定された出力先ディレクトリに、データベースをコピーします。

出力先ディレクトリには、データベースと同じ名前のディレクトリが作成されます。ディレクトリ内には、それぞれのテーブルに対応した3つのファイルが作成されます。

$ mysqlhotcopy データベース名 [出力先ディレクトリパス]

$ mysqlhotcopy データベース名1 データベース名2 ... 出力先ディレクトリパス

データベース、テーブルをダンプするmysqldumpコマンド

テーブル定義をCREATE TABLE構文に、データをINSERT INTO構文に変換して表示します。

$ mysqldump [OPTIONS] データベース名 [テーブル名]

$ mysqldump [OPTIONS] --databases [OPTIONS] データベース名1 [データベース名2 ...]

$ mysqldump [OPTIONS] --all-databases [OPTIONS]

mysqldumpコマンドのヘルプが見たい

$ mysqldump [-? | --help]

すべてのデータベースをバックアップする

MySQLサーバのすべてのデータベースをファイルに出力する。

$ mysqldump --all-databases > 出力ファイル名

データベースを指定してバックアップする

$ mysqldump --databases データベース名1 データベース名2 > 出力ファイル名

ひとつのデータベースをバックアップする

データベース内のすべてのテーブルデータをファイルに出力する。

$ mysqldump データベース名 > 出力先ファイル名

テーブルを指定してバックアップする

データベース内の1つのテーブルデータをファイルに出力する。

$ mysqldump データベース名 テーブル名 > 出力先ファイル名

いくつかの基本的なオプションを指定してバックアップする

$ mysqldump --opt -c -uユーザ名 -p データベース名 [テーブル名] > 出力先ファイル名

--opt：--add-drop-table、--add-locks、-a、-e、-q、-lを指定したのと同じです。 [-c | --complete-insert]：完全なINSERT構文を生成します。 （カラム名が記述されているINSERT構文） [-u | --user=]：サーバに接続するユーザを指定します。 [-p | --password=]：サーバに接続するパスワードを指定します。

--add-drop-table：DROP TABLE構文をCREATE TABLE構文の前に追加します。 --add-locks：INSERT構文の前後にLOCK TABLES構文とUNLOCK TABLES構文を追加します。 [-a | --all]：MySQL特有の作成オプションを含みます。 [-e | --extended-insert]：複数の値を1文に与えるINSERT構文を編集します。 [-q | --quick]：クエリをバッファしないで、直接標準出力に出力します。 [-l | --lock-tables]：読み出しの際にすべてのテーブルをロックします。

カラムごとに区切ったデータをファイルに出力する

このオプションはMySQLサーバでmysqldumpを実行する場合に指定できます。

1つのテーブルに対応する、2つのファイル（.sqlと.txt）を出力先ディレクトリに生成します。

テーブル名.sql：CREATE TABLE構文

テーブル名.txt：カラムごとに区切ったデータ

$ mysqldump -T出力先ディレクトリパス --fields-terminated-by=, --fields-optionally-enclosed-by=\" データベース名 [テーブル名]

[-T | --tab=]：ファイルの出力先ディレクトリパスを指定します。

--fields-terminated-by=：カラムを区切る文字を指定します。（指定しないときはタブ区切り） --fields-optionally-enclosed-by=：CHAR、VARCHAR型のカラムを囲む文字を指定します。 --fields-enclosed-by=：すべてのカラムを囲む文字を指定します。 --fields-escaped-by=：エスケープ文字を指定します。 --lines-terminated-by=：1レコードの区切り文字を指定します。

WHERE条件を指定してバックアップする

このオプションを使用するときは、テーブルを指定します。

$ mysqldump -w"条件式" データベース名 テーブル名 > 出力先ファイル名

[-w | --where=]：データを抽出するWHERE条件を指定します。 条件式は「"（ダブルクォーテーション）」か「'（シングルクォーテーション）」で囲みます。

-w"id<=100 and name like 'abc%'" or --where="id<=100 and name like 'abc%'"

INSERT構文のみ出力する

$ mysqldump -t データベース名 > 出力先ファイル

$ mysqldump --opt -t データベース名 > 出力先ファイル

[-t | --no-create-info]：CREATE TABLE構文を出力しない。 --opt：--add-drop-table、--add-locks、-a、-e、-q、-lを指定したのと同じです。

*オプション「--opt」で暗に指定されるオプションについては、基本的なオプションを指定してバックアップするを参照してください。

*オプション「-T」を指定して、カラムごとに区切ったデータをファイルに出力するときに、オプション「-t」を指定すると.txtファイルのみ作成されます。

CREATE TABLE構文のみ出力する

$ mysqldump -d データベース名 > 出力先ファイル

[-d | --no-data]：INSERT構文を出力しない。

*オプション「-T」を指定して、カラムごとに区切ったデータをファイルに出力するときに、オプション「-d」を指定すると.sqlファイルのみ作成されます。

データの取り込み

ファイルに記述されたINSERT構文を実行する

この操作は、MySQLサーバで実行する場合に限り実行できます。mysqldumpコマンドでファイルに出力したINSERT構文や、エディタで編集したINSERT構文を実行できます。

$ mysql データベース名 < ファイル名

$ mysql -uユーザ名 -p データベース名 < ファイル名

テキストファイルからデータを取り込むmysqlimportコマンド

ファイル名（拡張子は無視）と同じ名前のテーブルに、データをインポートします。mysqldumpコマンドで出力した.txtファイルや、エディタで編集したタブ区切りのデータをインポートできます。

$ mysqlimport [options] データベース名 ファイル名1 [ファイル名2 ....]

mysqlimportコマンドのヘルプが見たい

$ mysqlimport --help

ユニークキーの値が重複した場合に、取り込むデータを無視する

$ mysqlimport -i -uユーザ名 -p データベース名 ファイル名1

[-i | --ignore]：ユニークキーの値が既に存在するときは、取り込むデータを無視する。 [-u | --user=]：サーバに接続するユーザを指定します。 [-p | --password=]：サーバに接続するパスワードを指定します。

*「-i」、「-r」オプションのどちらも指定しないときや、ユニークキーの値が重複した場合はエラーが表示され、取り込み処理は中断されます。

ユニークキーの値が重複した場合に、取り込むデータで上書きする

$ mysqlimport -r -uユーザ名 -p データベース名 ファイル名1

[-r | --replace]：ユニークキーの値が既に存在するときは、取り込むデータで上書きする。 [-u | --user=]：サーバに接続するユーザを指定します。 [-p | --password=]：サーバに接続するパスワードを指定します。

*「-i」、「-r」オプションのどちらも指定しないときや、ユニークキーの値が重複した場合はエラーが表示され、取り込み処理は中断されます。

テーブルを空にしてデータを取り込むには

$ mysqlimport -d データベース名 ファイル名1

[-d | --delete]：データを取り込む前に、テーブルに記録されているデータをすべて削除します。

カンマで区切ったデータを取り込むには

「--fields-terminated-by」オプションに「,」を指定します。

テーブルのカラム順にデータが記録されている必要があります。

mysqldumpコマンドで作成した.txtファイルを取り込むときは、mysqldumpで指定したオプションと同じものを指定してください。

$ mysqlimport --fields-terminated-by=, --fields-optionally-enclosed-by=\" データベース名 ファイル名

*指定するオプションについては、カラムごとに区切ったデータをファイルに出力するを参照してください。

カラムを指定してデータを取り込むには

データを記録している順番がテーブルのカラム順でないときには、この方法でデータを取り込めます。

また、カラムを指定してデータを取り込めるので、必要なカラムについてのみデータを取り込むことができます。例えば、「NULL」やデフォルト値が設定されているカラムについては省略することができます。

$ mysqlimport -cカラム名2,カラム名6,カラム名4 データベース名 ファイル名1

[-c | --columns]：取り込むデータの順番に合わせてカラム名を指定します。

参考文献

変更点

このドキュメントについて

このドキュメントは、osCommerce日本語化プロジェクトの運営サポートを行っている株式会社ビットスコープが、osCommerceで採用しているMySQLの利用をサポートする目的で公開しています。