気軽にテストテーブルとか作りたい
毎回毎回テスト環境にテーブル作ってるとよく使う型とかに何書けばいいか忘れるよね一応メモしとくか…という記事です。
日本語がinsertできないときはデフォルトキャラセットがおかしいのでこのへんで設定を確認しよう。
2018/08/30 ダミーデータ作成を追加
2018-08-08 insert文の存在を忘れていたので追加
2018-06-21 主キー一つだけの場合を追加、外部キーの解説がめちゃくちゃわかりにくなったので修正
2018-06-19 いろいろ追加した
2018-04-02 deletedに初期値0を設定、nullを不許可に
全般
●空のテーブル
CREATE TABLE `DB名`.`テーブル名` ( --ここにカラムの設定を書く --主キー設定は複合キーとかでも対応できるよう考えると一番最後にまとめて設定したほうがよさそう );
●DB設計にスプレッドシートを使う
こういう形式でDB設計のスプレッドシート作れば、物理名含めて右側の文字だけコピーして、タブを半角スペースに変換して最後にカンマつけて，主キー設定だけ書いて上の空のテーブルに突っ込めばSQLができる。
------------------------------------------------------------------- | カラム名 | 物理名 | 型、長さ | null不可 | ------------------------------------------------------------------- | ID | id | INT(10) UNSIGNED | NOT NULL PRIMARY KEY | | 数値 | number | INT(10) | NOT NULL DEFAULT '0' | | 数値 | string | VARCHAR(2000) | | -------------------------------------------------------------------
●上記をsqlにしてみる
最後の行のカンマは確か外さないとエラーになるはず…
CREATE TABLE `DB名`.`テーブル名` ( id INT(10) UNSIGNED NOT NULL PRIMARY KEY, number INT(10) NOT NULL DEFAULT '0', string VARCHAR(2000) );
最後に主キー（PRIMARY KEY）設定する場合はこういう書き方になる
CREATE TABLE `DB名`.`テーブル名` ( id INT(10) UNSIGNED NOT NULL, number INT(10) NOT NULL DEFAULT '0', string VARCHAR(2000), PRIMARY KEY (`id`) );
以下、具体的な記入する値
※物理名を“で囲むのはあってもなくても動くのでどちらもよい
※DEFAULT NULLは書かない場合DEFAULT NULLになるので書かなくてもいい
●id
UNSIGNED にするとマイナス符号が入らないようにできて最大値の上限が二倍になる
AUTO_INCREMENTつけるとinsertのたびに数字を+1増やしてくれる
複合キーにしないのなら（主キーひとつだけなら）PRIMARY KEYつけておけば主キーになる
`id` INT(10) UNSIGNED AUTO_INCREMENT, `id` BIGINT UNSIGNED AUTO_INCREMENT, `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY, `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
●数値型
UNSIGNED にするとマイナス符号が入らないようにできる
NOT NULL DEFAULT ‘0’とすると初期値が0になる
`number_data` INT(10), `number_data` INT(10) NOT NULL, `number_data` INT(10) UNSIGNED, `number_data` INT(10) NOT NULL UNSIGNED, `number_data` INT(10) NOT NULL DEFAULT '0', `number_data` BIGINT, `number_data` TINYINT(4),
●文字型
VARCHARにすると日本語入るようになる
VARCHARは使いたい最大文字数書けばいいって聞いた。
URLは2000文字ぐらいでいいって聞いた
TEXTは最大文字数が指定できない
`string_data` VARCHAR(200), `url` VARCHAR(2000), `text_data` TEXT,
●削除フラグ、作成日、更新日
DEFAULT NULLとすると初期をnullと明示できる（なくてもDEFAULT NULLになるので書かなくてもいい）
`deleted` TINYINT NOT NULL DEFAULT '0', `created` DATETIME DEFAULT NULL, `modified` DATETIME DEFAULT NULL,
●主キー設定（複合キー対応）
複合キーにする場合は、カンマで区切って追加する、CREATE TABLEのとじカッコの直前に書く。
一つだけならidだけ書いたり、idカラムにPRIMARY KEYつければ使える
PRIMARY KEY (`id`, `sp_id`) PRIMARY KEY (`id`)
外部キーを使用したテーブルを作る
外部キー（FK、参照先に入ってる値しか入らないカラムのこと）
ようするにseigen_data.moto_data_idにmoto_data.idにあるデータしか入らないようにする。
外部キーを使う場合は ENGINE=InnoDBをいれないといけないとのこと、外部キー使わないならいらない。
わかりやすくidしか作ってないテーブルで書いてみる。
元データテーブル、idが主キー
CREATE TABLE `test`.`moto_data` ( `id` BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
FKで制限されるテーブル、moto_data_idが制限されるカラム（外部キー）
CREATE TABLE `test`.`seigen_data` ( `id` INT UNSIGNED NOT NULL, `moto_data_id` BIGINT UNSIGNED, PRIMARY KEY (`id`, `moto_data_id`) ) ENGINE=InnoDB;
外部キーの設定
下記のようにalter tableで後から追加する（CONSTRAINTとかでCREATE TABLE追加できるみたいだけど…）
外部キーにするカラムはUNSIGNEDなどの型も揃えないと設定時に失敗する
(INTとINT UNSIGNEDなどの同じINTでも型が違うカラムは外部キーとして設定できないということ)
ALTER TABLE `test`.`seigen_data` ADD FOREIGN KEY (moto_data_id) REFERENCES `test`.`moto_data`(id);
テーブル構造確認
実際に設定された長さやカラムなどは下記コマンドで構造を確認するとわかりやすい
show columns from 確認したいテーブル名;
データの入れ方
データ入れる文を書き忘れていました
INSERT INTO `DB名`.`テーブル名` (id,カラム名1,カラム名2) VALUES (1,"入力したいデータとか", "数字とかはダブルクォートつけなくていい"), (2,"入力したいデータとか2こめ", "追加したければこうして増やす"), (3,"入力したいデータとか3こめ", "idにオートインクリメントついてるならカラムには追加しなくても自動入力されるよ");
phpでダミーデータを作る場合
test.phpとかに書いてアクセスする方式
$db_name = 'データベース名';
$table_name = 'テーブル名';
$cols = 'id,カラム1,カラム2';
$max_count = 100000;//作成したい行数
// 中央部分のデータ作成
$sql = '';
for ($i = 1; $i <= $max_count; $i++) {// SQLの規定の部分作成
$sql .= <<<EOt
INSERT INTO `{$db_name}`.`{$table_name}` ({$cols}) VALUES ('{$i}', 'カラム1のデータ', 'カラム2のデータ');
EOt;
}
echo '<pre>';
echo $sql;
echo '</pre>';
ユーザー作成とログイン
ユーザー作るやつ
grant all on テーブル名.* to ユーザー名@localhostなどホスト名 identified by 'パスワード';
ログイン
# mysql -u ユーザー名 -p
