mysqlの主キー設定について

まだまだ初心者ですが、よろしくお願い申し上げます。
これまで実行した作業は以下の通りです。

①csvファイルをmysqlへインポート。ここで機種依存文字等で文字化けが発生してしまい、うまくインポートができなかったため、UTF-８に変換し、なんとかインポートまでこぎつけました。
②上記の結果、構造を見る限り、すべての文字列がそれぞれのカラムに適切にインポートされていることを確認いたしました。
③すべての文字列の照合順序はutf-8_general_ci担っております。
④テーブルのイメージは下記の通りで、およそ４万行存在します。
ご覧の通り、ユニークなカラムがないため、csv段階で&関数によりuniqのカラムを
事前に作っておりました。当該カラムはcsv段階でピボットで重複していないことを確認済みです。

num com per bul uniq
1 A社第1期 aビル A社第1期aビル
2 B社第1期 bビル B社第1期bビル
3 D社第１期 cビル D社第１期cビル
4 A社第２期 aビル A社第２期aビル
5 C社第１期 dビル C社第１期dビル

ここで、uniqカラムに主キー設定を実施いたしました。
そうしたところ、以下の表示が出てまいりました。
'#1062-Duplicate entry'XXXXXXXX' for key 'PRIMARY'
XXXの箇所はなぜかの文字化して表示されております。

原因はわかりませんが
上記の症状にが発生しており、目的であるuniqへの主キー設定が実行できません。

途方に暮れており、どなたかお力添えを頂けませんでしょうか。
何卒、よろしくお願い申し上げます。

行動規範の内容に同意します

回答4件

ベストアンサー

ユニークだと推定されるカラムがuniqだとすれば

SQL
1SELECT uniq,count(*) as count FROM テーブル GROUP BY uniq HAVING count>1;

としてみてください。重複箇所していればcountが2以上になっていると思います

投稿2016/08/09 13:58

yambejp

総合スコア114812

SugiuraY

2016/08/09 16:13

ご指摘ありがとうございます、おっしゃる通りSQL文を実行した場合、重複があることが判明いたしました。実はCSV段階では間違いなく重複がないことを確認したのですが、主キー設定しようとした場合、文字列が長すぎというエラー表示があったため、vercharを300から50に縮めたことで、足が切れて後から重複が生まれていることが判明いたしました。失礼いたしました。 ①重ねてで恐縮ですが、実際にuniqは長い文字列となるため、今後れこーどを追加していく際、相当な文字数になるケースも０％ではありません。この場合、MYSQLでは主キー設定は不能なのでしょうか？（そもそも何文字までの制限かが調べても出てきませんでした。。。） ②また、varcharで文字列を一度、縮めてしまった場合、その後長くしても２度と切れてしまった部分は戻ってこないのでしょうか？ ③実は、このvarcharが非常に曲者でして、指定をしなくてはいけない点は存じ上げております。ただし、csvファイルがかなりの量のため、複数回に分けてインポートしております。はじめのファイルでテーブルがない状態からインポートした場合、それぞれのカラムにvarcharの値が自動で割り当てられていると思います。（おそらくレコードの中のmax値に合わせて割り当てられる）ここで、２回目の追加インポートした場合、そのmax値を超えるものが出てきてしまった場合、カラムの文字列の文字数が定義されてしまっているため、自動的に既定のmax値を超えてしまう部分は足が切られてしまいます。そのために、カラムのすべてのvarcharについて、できるだけ大きい値をそれぞれphpmyadmin上、それぞれ100カラム分手打ちで設定しています。（実際に文字列が長いのはuniqだけではないため）これらの一括して、揃える方法や後からインポートした場合でも一括して設定する方法等があるのでしょうか？（もしかしたら、必ずしも余裕を持ってvarcharの数値をいたづらに大きく取ることは上記の主キー設定の弊害のように正しいことではないのかもしれませんが、皆様はどのような感覚で設定されていらっしゃるのか、、、）重ねてで本当に申し訳ございません。よろしくお願い申し上げます。

SugiuraY

2016/08/09 16:20

すみません、最後の③に関して、もう少々端的に申し上げますと、すでに文字列の既定値を持ってるカラムについて、後から追加されるレコードでどのくらいに文字数になるかわからない場合、皆様どのように対応されていらっしゃいますでしょうか。恐れ入りますが、よろしくお願い申し上げます。

takotakot

2016/08/10 01:05

SubiuraY 様追記の質問は、コメントではなくて、質問自体を編集しましょう。

yambejp

2016/08/10 01:17

1番について、主キーとは基本的にはそのレコードを特定するために使うIDです。名称のような変更の可能性のあるものは主キーの本質に合いません。・必ずしも主キーを設定する必要はないが、つけておくとレコード特定が高速・主キーはなるべく意味を持たない数字の羅列（unsignedなint型またはbigint型）・意味をもたせる場合でも数字の羅列がよい以下のどちらかで運用すると良いでしょう・主キーカラムを作った上でauto_increment設定をしておいてあとからデータを投入する・データ投入したテーブルに主キーカラムを追加し、強制的にユニークな番号をふる

yambejp

2016/08/10 01:20

2番について、覆水盆に返らずです投入された時点で縮まっているのでそのデータは無効ですもしやるなら該当カラムをtext型にして検索性は少しさげてしまうことです varchar型は検索が速い反面若干の制限がありますのでテーブルを拡張して再取り込みをすれば良い気がします

yambejp

2016/08/10 01:36

３番について、考え方が逆、「データを投入したらこんなテーブルになりました」ではなく、「テーブルを設計して投入できるデータはこの範囲」が原則。そのテーブルをつかってどんな検索や集計をしたいか要求定義して、それにあわせた要件定義をした上で、テーブルの仕様を確定してください。いまの感じではデータをとりあえずなんでもかんでも流し込んでいる状況だとお見受けします。この後正規化で相当つまづきそうですけどがんばってください

SugiuraY

2016/08/10 02:47

ご鞭撻を頂き、誠に有難うございます。不案内な中、細やかにご教示頂き、大変勉強になりました。しっかりとデータベースの目的を整理し要件定義を進めて参りたいと思います。重ねて御礼申し上げます。

行動規範の内容に同意します

uniq列も文字列ということでしたら、「utf8_general_ciで一致してしまった」という可能性も考えられます。

utf8_general_ciでは、アルファベットの大文字小文字が同一視されます。

投稿2016/08/09 14:57

maisumakun

総合スコア145183

SugiuraY

2016/08/09 15:52

ご回答ありがとうございます、おそらく問題はなく重複はないと思いますが、仮にそこに原因があった場合には、これらを分けるために使用すべき照合順序は存在するのでしょうか？よろしくお願い申し上げます。

maisumakun

2016/08/09 22:38

utf8_binであれば、全く同じバイト列のみを同じと認識するようになります。

行動規範の内容に同意します

yambejp 様のコメント欄と関連します。
2 について、長くしても一度切られたものは戻っては来ません。
しかし、com, per, bul から uniq は推定可能ですから、CSV は用いず、SQL で UPDATE をかければよいのではないでしょうか。
・com, per, bul の複合主キーは無理なんでしょうか

3 に関連しますが、ファイルが10程度までであれば以下のような方法はいかがでしょうか。