POSTED BY
2023-04-30

EUC-JPでデータベース作成(PostgreSQL)

CREATE DATABASE "dbname" WITH TEMPLATE="template0" ENCODING='EUC_JP' LC_COLLATE='C' LC_CTYPE='C';

テーブル定義確認

desc <table-name>; #MySQL
show create table <table-name>; #MySQL
\d <table-name> #PostgreSQL
</table-name></table-name></table-name>

カラム型変換

ALTER TABLE table_name MODIFY column_name data_type; #MySQL
ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type; #PostgreSQL

-- AUTO INCREMENT制約などを取り去る場合もMODIFYを使う(MySQL)
-- 例：SERIAL型主キーを作ってしまったがFLOATに変更し無制約の通常カラムに変更したい場合
CREATE TABLE aaa ( no BIGINT AUTO INCREMENT PRIMARY KEY; );
ALTER TABLE aaa MODIFY no FLOAT; -- 取り去り
ALTER TABLE aaa DROP PRIMARY KEY; -- AUTO INCREMENTが無くなると主キー削除が可能

文字列データの直接置換

-- articleテーブルのcategoryテキストデータ内の"debian"を"freebsd"に置換
UPDATE article SET category=REPLACE(category,"debian","freebsd") WHERE category IS NOT NULL;

デフォルト文字コード確認

show variables like '%char%'; #MySQL

インデックスの追加・削除

-- テーブル宣言と同時の場合
CREATE TABLE test(

mail1 VARCHAR(256) NOT NULL UNIQUE KEY, -- カラム宣言と同時の場合
mail2 VARCHAR(256) NOT NULL,
name VARCHAR(256) NOT NULL,
UNIQUE INDEX u_test_mail2(mail2), -- テーブル宣言の末尾で宣言の場合
INDEX i_test_name(name)

);

-- テーブル宣言後に行う場合
ALTER TABLE test ADD UNIQUE INDEX u_test_mail1(mail1);
ALTER TABLE test ADD INDEX i_test_name(name);

-- 削除
ALTER TABLE test DROP INDEX u_test_mail1;

-- 主キーの削除
ALTER TABLE test DROP PRIMAY KEY;

-- インデックス詳細確認(MySQL)
show indexes from <table_name>;
</table_name>

フルテキスト・インデックス＝全文検索エンジンの使用(MySQL)
全文検索とは、検索単語をMeCab等を使用してわかち書きしたものを検索にかけて抽出するもの。よってデフォルトでは検索単語と完全一致しないものまで出てくるが、完全一致モードもある。

https://dev.mysql.com/doc/refman/5.6/ja/fulltext-search.html

titleというカラムに全文検索インデックスを作成する場合

-- テーブル定義と同時の場合
CREATE TABLE...
  FULLTEXT (title) WITH PARSER NGRAM,

-- 途中で追加の場合
ALTER TABLE datarec ADD FULLTEXT (title) WITH PARSER NGRAM;

IN NATURAL LANGUAGE MODE による検索 このモードは類似性の高いものから抽出される。キーワードと完全一致しないものも出てくる。

SELECT * FROM datarec WHERE MATCH (title) AGAINST ('できない' IN NATURAL LANGUAGE MODE);

テストでは、「でき」または「ない」のどちらかが含まれる行が抽出された。

IN BOOLEAN MODE による検索 このモードはキーワードと完全一致するもののみを抽出する。

SELECT * FROM datarec WHERE MATCH (title) AGAINST ('できない' IN BOOLEAN MODE);

テストでは、「できない」が含まれる行のみが抽出された。このモードは以下のような検索もできる。

SELECT * FROM datarec WHERE MATCH (title) AGAINST ('+デッセイ -オデッセイ' IN BOOLEAN MODE);

上記例では「デッセイ」を含むものを検索するが、「オデッセイ」は対象外とする、という条件になる。