SQLアンチパターン：1章ジェイウォーク

gistfile1.md

1章 ジェイウォーク

準備

create database chapter1;
use chapter1;

1.1 目的：複数の値を持つ属性を格納する

テーブルの作成、サンプルデータの挿入

drop table Products;
drop table Accounts;

create table Accounts (
  account_id bigint unsigned not null auto_increment,
  account_name varchar(1000),
  primary key (`account_id`)
);
  
insert into Accounts values(12,'hironomiu');
  
create table Products (
  product_id  bigint not null auto_increment,
  product_name varchar(1000),
  account_id  bigint unsigned not null,
  primary key (product_id),
  index (`account_id`),
  constraint `account_id_fk` foreign key(`account_id`) references Accounts(`account_id`)
);
    
insert into Products (product_id, product_name, account_id) values (default, 'Visual TurboBuilder', 12);

確認

show create table Accounts;
show create table Products;

select * from Accounts;
select * from Products;

select * from Products where account_id = 12;
explain select * from Products where account_id = 12\G

1.2　アンチパターン

テーブル定義の変更（account_idを int -> varcharに変更）

alter table Products drop foreign key `account_id_fk`;
alter table Products drop index `account_id`;
alter table Products modify account_id varchar(100);
alter table Products add key `account_id`(`account_id`);

テーブル定義、データの確認

show create table Accounts;
show create table Products;
    
select * from Accounts;
select * from Products;

account_id "34" を追加する場合

更新(UPDATE)による実装

update Products set account_id = concat(account_id,',34') where account_id = 12;

レコードの洗い替え(DELETE & INSERT)による実装

select * from Products where account_id = 12;
delete from Products where account_id = 12;
insert into Products (product_id,product_name,account_id) values(default,'Visual TurboBuilder','12,34');

1.2.1 特定のアカウントに関する製品の検索

MySQL 5.6 Manual regexp
MySQL 5.6 Manual find_in_set

select * from Products where account_id regexp '[[:<:]]12[[:>:]]';
select * from Products where account_id regexp '[[:<:]]34[[:>:]]';
select * from Products where account_id regexp '[[:<:]]14[[:>:]]';
select * from Products where find_in_set('12',account_id);

実行計画の確認（account_id "12"で確認）

explain select * from Products where account_id regexp '[[:<:]]12[[:>:]]'\G
explain select * from Products where find_in_set('12',account_id)\G

1.2.2 特定の製品に関するアカウントの検索

select * from Products p inner join Accounts a on p.account_id regexp '[[:<:]]' || a.account_id || '[[:>:]]' where p.product_id = 1;

explain select * from Products p inner join Accounts a on p.account_id regexp '[[:<:]]' || a.account_id || '[[:>:]]' where p.product_id = 1\G
      
insert into Accounts values(34,'34desu');
      
select * from Products p inner join Accounts a on p.account_id regexp '[[:<:]]' || a.account_id || '[[:>:]]' where p.product_id = 1;

explain select * from Products p inner join Accounts a on p.account_id regexp '[[:<:]]' || a.account_id || '[[:>:]]' where p.product_id = 1\G

1.2.3 集約クエリの作成

以下はselect account_idのカウントSQL

select product_id,length(account_id) - length(replace(account_id,',','')) + 1 as contacts_per_product from Products;

1.5 解決策交差テーブル（連関エンティティ）を作成する

このまま作成するとエラーになります。

drop table Products;
      
create table Products (
  product_id  bigint not null auto_increment,
  product_name varchar(1000),
  primary key (product_id)
);
        
drop table Contacts; 

-- 何故エラーになるのでしょう？（余談を確認）
CREATE TABLE Contacts (
  product_id  BIGINT UNSIGNED NOT NULL,
  account_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, account_id),
  constraint `product_id_fk` foreign key(`product_id`) references Products(`product_id`),
  constraint `account_id_fk` foreign key(`account_id`) references Accounts(`account_id`)
);

改めて作成

drop table Products;

create table Products (
  product_id  bigint unsigned not null auto_increment,
  product_name varchar(1000),
  primary key (product_id)
);

CREATE TABLE Contacts (
  product_id  BIGINT UNSIGNED NOT NULL,
  account_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, account_id),
  constraint `product_id_fk` foreign key(`product_id`) references Products(`product_id`),
  constraint `account_id_fk` foreign key(`account_id`) references Accounts(`account_id`)
);

INSERT INTO Contacts (product_id, account_id) VALUES (123, 12), (123, 34), (345, 23), (567, 12), (567, 34);

閑話　bad FK

create table Bad_Products (
  product_id  bigint not null auto_increment,
  product_name varchar(1000),
  account_id  bigint not null,
  primary key (product_id),
  index (`account_id`),
  constraint `bad_account_id_fk` foreign key(`account_id`) references Accounts(`account_id`)
);