このページは MySQL を使うときに毎回開いて確認している自分用のノートをまとめたものです。CLI のセットアップから root パスワード設定、データベースとテーブルの基本操作、mysqldump でのバックアップと復元、\G や pager less -S といった結果表示の小技、よく使うデータ型、INSERT・UPDATE・JOIN・GROUP_CONCAT などの SQL までを 1 ページに集めています。
特定の章だけ拾い読みできるよう小さなトピック単位で並べているので、目次から目的の項目に飛んで使う想定です。PostgreSQL を使うときの同じ粒度のノートは PostgreSQLと向き合うための 現場で使えるデータベース操作・SQLノート にまとめています。
MySQL開発環境のセットアップ
| 項目 | バージョン | インストールコマンド |
|---|---|---|
| macOS | 14.2 (Sonoma) | |
| MySQL | 8.0 | brew install [email protected] |
上記は記事執筆時に動作確認した組み合わせです。2026年5月時点では Homebrew で brew install mysql を実行すると MySQL 9.x が入り、長期サポート版の [email protected]（LTS）も選べます。新規に環境を作るならどちらかをおすすめします。バージョンを固定したい場合は [email protected] も引き続き利用できますが、旧安定系列で現在は通常サポートが終了しており、新規環境には [email protected]（LTS）などをおすすめします。macOS 15 (Sequoia) などより新しいバージョンでも、Homebrew を使う限り手順はほぼ変わりません。
インストールが完了したらCLIでmysqlコマンドを使えるように、環境パスを通しておきます。[email protected] 以外を入れた場合は、[email protected] などインストールした formula 名にパスを読み替えてください。
export PATH="/opt/homebrew/opt/[email protected]/bin:$PATH"
mysqlの起動と停止
$ mysql.server start # 起動
$ mysql.server stop # 停止
MySQLの初期設定
rootユーザーにパスワードを設定する
Homebrew でインストールした直後の MySQL 8.x は、root パスワードが未設定のまま起動するので、まずは無パスワードで入って初期パスワードを設定します。Linux ディストリビューションのパッケージ（apt / dnf など）で入れた場合は、初回起動時に一時パスワードがログ（/var/log/mysqld.log など）に出ているか、mysql_secure_installation で対話的に設定する流れになるので、環境に合わせて読み替えてください。
$ mysql -u root # rootユーザーでログインします。
以下、MySQLのプロンプトは>で表現します。
> ALTER USER 'root'@'localhost' IDENTIFIED BY '新しいパスワード'; -- MySQLのプロント上で、rootユーザーに新しいパスワードを設定します。
> FLUSH PRIVILEGES; -- 変更を反映させます。
> exit; # または quit; --MySQLのプロントを終了します。
ここまでの設定が完了したら、新しいパスワードでログインできるか試します。パスワードを入力してログインできたら成功です。
$ mysql -u root -p
後からrootのパスワードを変更したい場合
MySQLへログイン後、以下を実行することでパスワードを変更できます。
> ALTER USER 'root'@'localhost' IDENTIFIED BY '[新しいパスワード]';
> FLUSH PRIVILEGES;
MySQL 8.x では、ALTER USER ... IDENTIFIED BY ... が推奨形です。古い記事で見かける SET PASSWORD = PASSWORD('...') のような書き方は廃止されているので、置き換えて使ってください。
データベース操作
データベースの作成
> CREATE database [データベース名];
データベースを確認する
> SHOW databases;
データベースへ接続する
> USE [データベース名]
MySQLへログインすると同時に、データベースへ接続することも可能です。
$ mysql [データベース名] -u [ユーザー名] -p
テーブルを確認する
> SHOW tables;
テーブルのカラムを一覧表示する
> SHOW COLUMNS FROM テーブル名;
データベースを削除する
> DROP database [データベース名];
DROP DATABASE は中のテーブルとデータをすべて削除します。実行前に必要なものを mysqldump でバックアップしておくと安全です。
データベースのバックアップ
データベースを丸ごとバックアップ（ダンプ）する
次のコマンドで、既存のデータベースをエクスポート（ダンプ）することが可能です。
$ mysqldump -u root -p [データベース名] > mydb_dump.sql
SQLクエリとしてファイルが保存されます。
データベースをインポートする（外部ファイルのSQLの実行）
mysqlへログインして、新規でデータベースを作成しておきます。
> CREATE DATABASE [データベース名];
mysqlからログアウトして、シェルで以下コマンドを実行します。
$ mysql [データベース名] -u [ユーザー名] -p < mydb_dump.sql
これでデータベースのテーブルの構造やデータを復元できました。
特定のテーブルをバックアップする
シェル側で実行するコマンドです。
$ mysqldump -u [ユーザー名] -p [データベース名] [テーブル名] > [バックアップファイル名].sql
-p の直後にパスワードを直書きする -p[パスワード] 形式はコマンド履歴やプロセス一覧に残ってしまうので、対話入力 (-p のみ) か後述の ~/.my.cnf 設定にまとめておくのがおすすめです。
外部ファイルのSQLを読み込んで実行する
MySQLにログインした状態でも外部ファイルを取り込んでSQLを実行できます。
> source test.sql
SQLの結果表示
SQL結果を縦表示に整形する
SQLの終端のセミコロン; を\Gに変えると、縦表示が可能です。
> SELECT * FROM users\G
SQL結果をlessページャーで表示する
テーブルカラムが多い時や、SQL結果が大量な時に便利です。
下記コマンドでlessを使うようにセットアップします。
> pager less -S
これ以降、通常のSQLクエリを発行すると、結果がlessで表示されるようになります。
| 操作 | 実行結果 | 備考 |
|---|---|---|
u or w | 半ページ戻る | |
d | 半ページ進む | |
g | 先頭へ飛ぶ | |
G | 最終行へ飛ぶ | |
n | 検索 | |
F | ファイルの更新を監視して リアルタイムで反映 | ctrl+cで終了 |
q | 終了 |
ページャーを終了するときは以下を実行します。
> nopager;
MySQLクライアントの起動時にカスタム設定を反映させる
> pager less -S のように頻繁に使うコマンドはデフォルトで設定しておきたいものです。 MySQLのCLIでは、起動時にユーザー設定ファイルを読み込んでくれる ので、そこにコマンドを書き込んでおくと毎回自動で適用されます。
ファイル名は OS ごとに違います。
| OS | 既定のユーザー設定ファイル |
|---|---|
| macOS / Linux | ~/.my.cnf |
| Windows | %USERPROFILE%\my.ini（C:\Users\<ユーザー名>\my.ini） |
macOS / Linux で ~/.my.cnf が存在しない場合は、新規作成してください。中身は同じ書式です。
[mysql]
pager=less -S
[client]
# user= や password= を書くと mysql / mysqldump で共通利用できる
[mysql] セクションは mysql コマンド専用のオプションです。pager を [client] に書くと mysqldump など他のクライアントも読み込み、エラーになる場合があります。user= や password= など接続情報の共通設定は [client] に書いておくと、mysql や mysqldump のコマンドラインからユーザー名やパスワードを省略できます。パスワードを書き込む場合は chmod 600 ~/.my.cnf で権限を絞っておくと安全です。
MySQL テーブル＆コメント一覧表示
操作したいデータベースを選択:
> USE your_database_name;
テーブル一覧とそれぞれのコメントを表示する:
> SELECT TABLE_NAME, TABLE_COMMENT
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_SCHEMA = '[データベース名]';
MySQLで使用できる主なデータ型
ここでMySQLで使用できる主なデータ型を整理しておきます。
数値型
| データ型 | 説明 |
|---|---|
| TINYINT | 非常に小さい整数 |
| SMALLINT | 小さい整数 |
| MEDIUMINT | 中程度の整数 |
| INT or INTEGER | 標準的な整数 |
| BIGINT | 大きな整数 |
| DECIMAL or NUMERIC | 固定小数点数 |
| FLOAT | 単精度浮動小数点数 |
| DOUBLE | 倍精度浮動小数点数 |
| BIT | ビット値 |
日付と時刻型
| データ型 | 説明 |
|---|---|
| DATE | 日付 (‘YYYY-MM-DD’) |
| TIME | 時間 (‘HH:MM:SS’) |
| DATETIME | 日付と時間の組み合わせ (‘YYYY-MM-DD HH:MM:SS’) |
| TIMESTAMP | タイムスタンプ (‘YYYY-MM-DD HH:MM:SS’) |
| YEAR | 年 (4桁の数値) |
文字列型
| データ型 | 説明 |
|---|---|
| CHAR | 固定長文字列 |
| VARCHAR | 可変長文字列 |
| BINARY | 固定長バイナリ文字列 |
| VARBINARY | 可変長バイナリ文字列 |
| BLOB | バイナリ大オブジェクト |
| TEXT | テキストデータ |
| ENUM | 列挙型（事前定義された値のリストから選択） |
| SET | セット型（複数の値を持つことができる） |
スペーシャル型
| データ型 | 説明 |
|---|---|
| JSON | JSON形式のデータ |
| GEOMETRY | 空間データ型（GISデータ） |
ちなみに、 SQLiteはMySQLと比べてデータ型がかなり少ない ので、互換性のアプリを作りたい場合には注意が必要です。iOSアプリで SQLite を触る具体的な手順は iOSアプリ開発でSQLiteを使う FMDB にまとめています。
データ操作
レコードを挿入する
TEXT、INT、DATETIMEを挿入する例:
INSERT INTO my_table (title, num, date)
VALUES ('hoge', 1000, NOW());
レコードを更新する
UPDATE `tablename` SET age=17 WHERE id=2;
idが最大値（最後の行）のレコードを更新する
次のクエリは、id カラムがオートインクリメントされている場合に有効です
UPDATE `tablename`
SET age = 17
WHERE id IN (
SELECT MAX(id) FROM `tablename`
);
MySQLでは自身のテーブルの参照をサブクエリで実行すると、エラーになるので注意が必要です。下記のような書き方はMySQLではできません。
UPDATE `tablename` SET age = 17 WHERE id = (SELECT MAX(id) FROM `tablename`);
オートインクリメントの連番をリセット
ALTER TABLE `tablename` auto_increment = 1;
TRUNCATEでテーブルデータを全件削除
TRUNCATE TABLE `tablename`
DELETE FROM tablename でも全件削除はできますが、TRUNCATEとDELETEでは次のような違いがあります。
| SQL | 条件指定 | 処理速度 | ロールバック |
|---|---|---|---|
| TRUNCATE | 不可 | 速い | 不可 |
| DELETE | 可能 | 遅い | 可能 |
ロールバックの必要がなければ、TRUNCATEが良いでしょう。
データ検索
合計値を取得する
以下は my_table テーブルから、dateカラムが2024年1月のデータを合計する例です。
SELECT
category_id,
SUM(num) AS num_sum
FROM
my_table
WHERE
date >= '2024-01-01 00:00:00' AND date <= '2024-01-31 23:59:59'
GROUP BY
category_id;
category_id ごとの合計値が取得できます。
JOINで複数のテーブルを結合する
次は、INNER JOINを使って2つのテーブルの両方に存在する行のみをセレクトする例です。
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
department_id と departments テーブルの id が一致する行のみを返します。
GROUP_CONCATで複数の行の値を単一の文字列に結合する
次は、各従業員が持つスキルを一覧表示する例です。GROUP_CONCAT はMySQLやSQLite、MariaDBで使用可能です。
SELECT e.name, GROUP_CONCAT(s.skill ORDER BY s.skill SEPARATOR ', ') AS skills
FROM employees e
JOIN skills s ON e.id = s.employee_id
GROUP BY e.id;
テーブル、カラムの追加・変更
既存のテーブル名を変更する
RENAME TABLE old_table_name TO new_table_name;
ALTER TABLE new_table_name COMMENT = '新しいテーブル名';
後からカラムを追加して、外部キーの制約
以下はユーザーテーブルのidを別のテーブルで結びつける例です:
-- ユーザーIDの追加
ALTER TABLE some_table_name
ADD COLUMN user_id BIGINT unsigned NOT NULL COMMENT 'ユーザーID' AFTER id;
-- 外部キー制約の追加
ALTER TABLE some_table_name
ADD CONSTRAINT fk_some_table_user_id FOREIGN KEY (user_id) REFERENCES `users`(`id`);
【注意】外部キー制約の追加を実行する前に、実際にテーブルに存在するuser_idをセットしておく必要があります。また、同じデータベース内で同じ名前の外部キー制約を複数のテーブルで使用することはできません。制約名はデータベース全体で一意である必要があります。（fk_some_table_user_idの部分）。
失敗した場合は、以下のSQLで削除してやり直しましょう。
-- 失敗した時の削除用
ALTER TABLE some_table_name DROP FOREIGN KEY fk_some_table_user_id;
ALTER TABLE some_table_name DROP COLUMN user_id;
関連記事
同じ粒度で他のデータベースや、MySQL を実際に使うアプリ側の記事もまとめています。
- PostgreSQLと向き合うための 現場で使えるデータベース操作・SQLノート — PostgreSQL を使うときの初期設定や psql 操作の備忘録
- iOSアプリ開発でSQLiteを使う FMDB — モバイル側で軽量 DB を扱うときの選択肢
- はじめての Spring Boot 〜 JavaでWebアプリケーション — Spring Boot + MyBatis で MySQL を読み書きする実例
- 文章AとBの類似率を数値化するJaccard係数をシェル・sqlite・mecabで実現してみた — シェルから SQLite を呼んで集合演算する応用例
参考書籍
CLI のチートシートだけでなく、SQL の考え方や RDB 設計を体系的に学びたくなった時に手元に置きたい本を Amazon の検索結果でまとめておきます。読者の前提知識に合うものを選んでください。