MySQL全然書けなくて笑っちゃった

Published
2023-11-13
Author
びきニキ
Tags
#tech#MySQL#php-60day-challenge

.sqlファイルを作って、以下のコマンドで実行する

cat （SQLファイルの絶対パス） | docker exec -i playground-mysql mysql -u〇〇 -p〇〇 playground-db


最初にSQLを雰囲気で書いてたやつ

// FROM mysql/mysql-server:8.0

USE playground-db

CREATE TABLE IF NOT EXISTS `User` (
    user_id UUID PRIMARY KEY NOT NULL UNIQUE,
    name VARCHAR(32) NOT NULL,
    password VARCHAR(255) NOT NULL

    PRIMARY KEY (user_id)
);

CREATE TABLE IF NOT EXISTS `Article` (
    article_id UUID PRIMARY KEY NOT NULL UNIQUE,
    title VARCHAR(64) NOT NULL,
    context VARCHAR(20000) NOT NULL,
    user_id UUID PRIMARY KEY NOT NULL UNIQUE,
    created_at timestamp notnull notnull,
    updated_at timestamp notnull notnull,

    FOREIGN KEY (user_id)
    REFERENCES users (id)
    ON DELETE CASCADE
)

CREATE TABLE IF NOT EXISTS `Image` (
    article_id UUID PRIMARY KEY NOT NULL UNIQUE,
    image_id UUID PRIMARY KEY NOT NULL UNIQUE,
    file_path VARCHAR(255) NOT NULL,
    created_at timestamp notnull notnull,
    updated_at timestamp notnull notnull,

    PRIMARY KEY (article_id, image_id)
    FOREIGN KEY (article_id, image_id)
    REFERENCES  Image(image_id)
    REFERENCES Article (article_id)
    ON DELETE CASCADE
)

CREATE TABLE IF NOT EXISTS `Thumbnail` (
    article_id UUID PRIMARY KEY NOT NULL UNIQUE,
    image_id UUID PRIMARY KEY NOT NULL UNIQUE,
    created_at timestamp notnull notnull,
    updated_at timestamp notnull notnull,

    PRIMARY KEY (user_id, image_id)
    FOREIGN KEY (user_id, image_id)
    REFERENCES User (user_id)
    REFERENCES Article (article_id)
    ON DELETE CASCADE
)

CREATE TABLE IF NOT EXISTS `Tag` (
    tag_id UUID PRIMARY KEY NOT NULL UNIQUE,
    name VARCHAR(32) NOT NULL,
    created_at timestamp notnull notnull,
    updated_at timestamp notnull notnull,

    PRIMARY KEY (tag_id)
)

CREATE TABLE IF NOT EXISTS `ArticleTag` (
    article_id UUID PRIMARY KEY NOT NULL UNIQUE,
    tag_id UUID PRIMARY KEY NOT NULL UNIQUE,
    created_at timestamp notnull notnull,
    updated_at timestamp notnull notnull,

    PRIMARY KEY (article_id, tag_id)
    FOREIGN KEY (article_id, tag_id)
    REFERENCES Article (article_id)
    REFERENCES Tag (tag_id)
    ON DELETE CASCADE
)


案の定シンタックスエラー。原因っぽかったものを箇条書きで書き残しておく。

  • Table名がパスカルケース。なんかかっこいいと思ったから…。
  • UUIDを「UUID型」として書いている。調べたところMySQLにそんなものはなさそう
  • contextがVARCHAR(20000)。そんなに大きいならTEXT型にしろって言われた。


その次に書いてみたやつ

USE playground-db

CREATE TABLE IF NOT EXISTS `user` (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL UNIQUE,
    name VARCHAR(32) NOT NULL,
    password VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS `article` (
    article_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL UNIQUE,
    title VARCHAR(64) NOT NULL,
    context TEXT(20000) NOT NULL,
    user_id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,

    FOREIGN KEY (user_id)
    REFERENCES User (user_id)
    ON DELETE CASCADE
);


CREATE TABLE IF NOT EXISTS `image` (
    article_id INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE,
    image_id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL UNIQUE,
    file_path VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,

    PRIMARY KEY (article_id, image_id),
    FOREIGN KEY (article_id)
    REFERENCES Article (article_id)
    ON DELETE CASCADE
);


CREATE TABLE IF NOT EXISTS `thumbnail` (
    article_id INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE,
    image_id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,

    PRIMARY KEY (article_id, image_id),
    FOREIGN KEY (article_id)
    REFERENCES Article (article_id),
    FOREIGN KEY (image_id)
    REFERENCES Image (image_id)
    ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `tag` (
    tag_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL UNIQUE,
    name VARCHAR(32) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS `articletag` (
    article_id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
    tag_id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,

    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id)
    REFERENCES Article (article_id),
    FOREIGN KEY (tag_id)
    REFERENCES Tag (tag_id)
    ON DELETE CASCADE
);


ダメだった。原因は以下。

  • AUTO_INCREMENT 多重利用問題


最終修正版

USE playground-db;

CREATE TABLE IF NOT EXISTS `user` (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    password VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS `article` (
    article_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(64) NOT NULL,
    context TEXT NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,

    FOREIGN KEY (user_id)
    REFERENCES user (user_id)
    ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `image` (
    article_id INT UNSIGNED NOT NULL,
    image_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_path VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,

    FOREIGN KEY (article_id)
    REFERENCES article (article_id)
    ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `thumbnail` (
    article_id INT UNSIGNED NOT NULL,
    image_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,

    PRIMARY KEY (article_id, image_id),
    FOREIGN KEY (article_id)
    REFERENCES article (article_id),
    FOREIGN KEY (image_id)
    REFERENCES image (image_id)
    ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `tag` (
    tag_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS `articletag` (
    article_id INT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,

    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id)
    REFERENCES article (article_id),
    FOREIGN KEY (tag_id)
    REFERENCES tag (tag_id)
    ON DELETE CASCADE
);


これで上手く行ったっぽい！やったぜ👏

$ docker exec -it playground-mysql mysql -u〇〇 -p〇〇 -e "SHOW TABLES FROM \playground-db\\;"

mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------+
| Tables_in_playground-db |
+-------------------------+
| article                 |
| articletag              |
| image                   |
| tag                     |
| thumbnail               |
| user                    |
+-------------------------+