1. /
  2. MySQL

MySQL

[MySQL] 外部キー制約を一時的に無効にする

MySQLで外部キー制約を適用しているテーブルにはDROP TABLEができません。

本番ではその挙動でもちろん良いのですが、開発中にテーブルをまるごと作り直したいときにはこの制約が邪魔になることがあります。またmysqldumpなどで出力したファイルをインポートする際にも、テーブルを順番通りインポートする必要が出てきます（外部キー制約があるとインポートに時間もかかりますよね）。

そういったときにforeign_key_checksの値に「0」をセットすることで一時的に外部キー制約を無効にすることができます。

SET foreign_key_checks = 0;

再び有効にするには「1」を代入します。

SET foreign_key_checks = 1;

続きを読む

[MySQL] アップデート時にGPGキーのエラーで停止してしまう場合

小ネタです。
踏み台サーバであるAmazon Linux2にMySQLのクライアントを入れRDSを操作しているのですが、ある日yum updateをしたら途中で停止してしまいました。

エラーメッセージを眺めるとMySQLをアップデートする際にGPGが原因でコケているっぽい。

$ sudo yum update
(中略)
warning: /var/cache/yum/x86_64/2/mysql57-community/packages/mysql-community-libs-5.7.37-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql から鍵を取得中です。

The GPG keys listed for the "MySQL 5.7 Community Server" repository are already installed but they are not correct for this package.
Check that the correct key URLs are configured for this repository.

 Failing package is: mysql-community-libs-5.7.37-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

RPMやyumではパッケージが改ざんされているか検証するためにGPGキーを利用していることがあるのですが、このGPGキーには有効期限が設定されている関係で一定期間が経つと検証が行えなくなりインストールが停止してしまいます。パッケージを作成する際にGPGキーを別の物に変更した場合も同様の現象になります。
続きを読む

[MySQL] テーブルにカラムを追加/変更/削除する – ALTER TABLE

MySQLでテーブルのカラム(列)の変更を行うにはALTER TABLEを利用します。ALTER TABLEはカラム以外にもテーブルのあらゆる変更が行える万能選手ですが今回はカラムの追加,変更,削除に特化して見ていきます。

カラムを追加する

-- テーブルの末尾に追加
ALTER TABLE tablename ADD colname varchar(64);

-- テーブルの先頭に追加
ALTER TABLE tablename ADD colname varchar(64) FIRST;

-- 指定カラムの後ろに追加
ALTER TABLE tablename ADD colname varchar(64) AFTER id;  -- idの後ろに追加

カラムを変更する

-- カラム名とデータ型を変更
ALTER TABLE tablename CHANGE old_colname new_colname integer;

-- カラム名だけを変更(MySQL8.0〜)
ALTER TABLE tablename RENAME COLUMN old_colname TO new_colname;

カラムを削除する

ALTER TABLE tablename DROP colname;

続きを読む

[MySQL] テーブルにファイルをインポートする – mysqlimport編

MySQLへCSVなどのテキスト形式のデータファイルを一括でインポートします。
今回はLinuxなどのコマンドとして提供されているmysqlimportを利用します。通常MySQLをインストールした際に自動的に付いてきますのですぐに実行することが可能です。

$ mysqlimport -u username -p --local dbname tablename.tsv

シェルスクリプト内で実行する場合はこちらが手軽ですね。また大抵の場合、INSERT文を大量に発行するよりも高速にデータを入れることができます。
続きを読む

[MySQL] パスワードを変更する – SET PASSWORD

MySQL上のユーザーのパスワードを変更するにはSET PASSWORD文を利用します。

SET PASSWORD FOR katsube@localhost = 'password here';

MySQLのバージョンによって記述方法が異なります（上記は5.7以降の書き方）。また同様のSQLでALTER USER文があり、こちらは有効期限などより細かい設定が可能です。
続きを読む

[MySQL] 権限を付与する – GRANT

作成済みのユーザーに権限を付与するにはGRANT文を利用します。実行にはGRANTを利用できる権限が必要です。

GRANT ALL On db1.* To katsube@localhost;

上記はdb1という名前のデータベース内にあるすべてのテーブルに全権限を付与しています。

続きを読む

[MySQL] テーブル名を変更する

MySQLでテーブル名を変更するには、通常RENAME TABLEを利用します。

RENAME TABLE old_table TO new_table;

テーブル名の変更はSQLの標準規格には存在しません。そのため各DBが独自の実装をしているのが現状です。これもMySQLでの利用方法ですので、他のDBを使う際にはご注意ください。

続きを読む

[MySQL] AUTO_INCREMENTの値を設定/確認する

MySQLのAUTO_INCREMENTの値を確認したり設定します。

値を設定する

ALTER TABLE tbl AUTO_INCREMENT = 100;

値を確認する

SELECT AUTO_INCREMENT
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'test'  /* testデータベース */
AND    TABLE_NAME   = 'foo';  /* fooテーブル */

それぞれ詳しく見ていきます

続きを読む

[MySQL] テーブルをコピーする

MySQLでテーブルをコピーします。
以下の2つのパターンのそれぞれの特徴についてメモします。

パターン1

CREATE TABLE newtable LIKE oldtable;
INSERT INTO newtable SELECT * FROM oldtable;

パターン2

CREATE TABLE newtable SELECT * FROM oldtable;

続きを読む

MySQLのテーブルにPartitionを追加/削除/確認する

2013年に作成した個人的なWebサービスがいつの間にか動かくなってる…？と思って調べて見るとPHP経由で動かしているMySQLが以下のようなエラーを吐いていました。

PHP Fatal error:  Uncaught Exception: [_runsql] HY000 1526 Table has no partition for value 20180827

どうやら開発当初に設定したMySQLのpartitionをすべて使い切ってしまったようです。アクセスログ用のテーブルで5年分くらいのpartitionをまとめて切っていたのですが、まさか5年後も動かしているとは夢にも思わずｗ (もしくは将来の自分が何とかするだろうと思っていたらしいｗ)

今回はこいつを動くようにします。
続きを読む