SQLでDBにテストテーブルを作るとき用の記事

2018.03.28

気軽にテストテーブルとか作りたい

毎回毎回テスト環境にテーブル作ってるとよく使う型とかに何書けばいいか忘れるよね一応メモしとくか…という記事です。
日本語がinsertできないときはデフォルトキャラセットがおかしいのでこのへんで設定を確認しよう。

2019-06-01　スプレッドシート画像を追加、いろいろわかりやすくした
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にしてみる

最後のカンマを外さないと下記のようなエラーが出ます
「ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)’ at line XX」

CREATE TABLE `DB名`.`テーブル名` (
	id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
	date DATETIME NOT NULL ,
	remarks VARCHAR(200) DEFAULT NULL ,
	point INT(10) DEFAULT NULL ,
	deleted TINYINT NOT NULL DEFAULT '0' ,
	created DATETIME DEFAULT NULL ,
	--下の最後の行のカンマは外さないと実行時に構文エラーになる
	modified DATETIME DEFAULT NULL
);

最後に主キー（PRIMARY KEY）設定する場合はこういう書き方になる

CREATE TABLE `DB名`.`テーブル名` (
	id INT(10) UNSIGNED AUTO_INCREMENT ,
	date DATETIME NOT NULL ,
	remarks VARCHAR(200) DEFAULT NULL ,
	point INT(10) DEFAULT NULL ,
	deleted TINYINT NOT NULL DEFAULT '0' ,
	created DATETIME DEFAULT NULL ,
	modified DATETIME DEFAULT NULL ,
	PRIMARY KEY (`id`)
);

以下、具体的な記入する値

※物理名を“で囲むのはあってもなくても動くのでどちらもよい
※DEFAULT NULLは書かない場合DEFAULT NULLになるので書かなくてもいい

●IDの書き方いろいろ

--UNSIGNED にするとマイナス符号が入らないようにできて最大値の上限が二倍になる
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`id` BIGINT UNSIGNED AUTO_INCREMENT,

--AUTO_INCREMENTつけるとinsertのたびに数字を+1増やしてくれる
--主キーひとつだけならPRIMARY KEYつけておけば主キーになる
`id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

●数値型の書き方いろいろ

※そういえばfloatとか入ってない

--普通の整数
`number_data` INT(10), 
--やたら大きい整数
`number_data` BIGINT, 
--やたら小さい整数
`number_data` TINYINT(4), 

--NOT NULLをつけるとNULLが入らなくなる
`number_data` INT(10) NOT NULL, 

--UNSIGNED にするとマイナス符号が入らないようにできる
`number_data` INT(10) UNSIGNED, 
`number_data` INT(10) NOT NULL UNSIGNED, 

--NOT NULL DEFAULT '0'とすると初期値が0になる
`number_data` INT(10) NOT NULL DEFAULT '0',

●文字型の書き方いろいろ

--CHARたと2バイト文字は入らない、アルファベットなど半角英数字のみのはず
`string_data` CHAR(200), 

--VARCHARにすると日本語のような2バイト文字が入るようになる
--VARCHARは使いたい最大文字数書けばいいって聞いた。
`string_data` VARCHAR(200), 

--URLは2000文字ぐらいでいいって聞いた
`url` VARCHAR(2000), 

--TEXTは最大文字数が指定できないがいくらでも文字が入るらしい
`text_data` TEXT,

●削除フラグ、作成日、更新日

--「DEFAULT NULL」とすると初期をnullと明示できる（なくてもDEFAULT NULLになるので書かなくてもいいけれど）
`created` DATETIME DEFAULT NULL,
`modified` DATETIME DEFAULT NULL,

--NULLを入れちゃだめで初期値を0にしたいときは「NOT NULL」「DEFAULT '0'」とする
`deleted` TINYINT NOT NULL DEFAULT '0',

●主キーと複合キーの書き方

複合キーにする場合は、カンマで区切って追加する、CREATE TABLEのとじカッコの直前に書く。
一つだけならidだけ書いたり、idカラムにPRIMARY KEYつければ使える

--主キーだけ
PRIMARY KEY (`id`)

--複合キーにしたい時
PRIMARY KEY (`id`, `sp_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
スポンサーリンク