「DB」の版間の差分

提供：senooken JP Wiki

2025年10月20日 (月) 11:55時点における最新版

Other

Performance

SQL vs. app

第5回　DB側でやること、アプリ側でやることを見極めよう | gihyo.jp

DB側ですべき処理とアプリ側ですべき処理の見極め。

ネットワーク上でデータの受け渡しがあるので、ソート以外はSQLですべてしたほうがいい。

コンピューターが処理することに変わりはなく、

カラム順序

テーブルのカラム・列の順序。これに意味があるのか？カラムを追加する場合などに影響がある。

パフォーマンスに影響がある。特に下3個の情報が参考になる。

基本的に、頻繁に使用する列 (主キー、外部キー、頻出検索、頻出更新の順) を先頭に配置する。ただし、null許容は全体的に後ろ。null許容の中でも頻度順にする。NULL許容の前に、TEXTなどの可変長。

つまり、「固定長→可変長→NULL許容」の順番にする。さらに、それぞれのブロックで、「アクセス頻度高→低」の順番にする。

ディスクスペースの利用率、使用するデータの効率などに影響がある模様。

created_at/updated_atのような全テーブルに付随するようなカラムは、主キーの直後でいい。全テーブルで共通にできるから。

マスター

マスタデータって何？マスタデータについて詳しく解説！ - J-MDM／Snowflake

ユーザー、顧客や拠点の情報など、ベースとなるデータのこと。

マスターの他に、トランザクションというのもある。これは、売買などシステムや業務の稼働に伴って生じるデータ。

【データベース正規化完全ガイド】実務観点での正規化手順やポイントを詳しく解説 | PrAhaENGINEERLAB

トランザクションテーブルで、マスターテーブルを直接参照するのはまずい。

マスターテーブルを更新したら、トランザクションの情報も変わってしまうから。

登録時にマスターテーブルのデータをコピーしていれる。

Sharding

シャーディングとは - 意味をわかりやすく - IT用語辞典 e-Words

シャーディング。DBの負荷分散の手法。

データをレコード・行単位で水平分割して、複数のデータベースサーバーに分散して記録する。

分散したDBの単位をシャードと呼んでいる。データが増大しても台数を増やせば問題ない。

ただし、どのデータがどのサーバーで記録しているかを把握する必要がある。ハッシュ値などから計算する。

https://chatgpt.com/c/67d7bcb9-db48-800b-954a-8d8982221af2

DBシャーディングとテーブルシャーディングがある。

例えば、1個のテーブルでログを管理する場合、昔のログはほぼ参照しない。これが同じテーブルにあると、検索時に邪魔になる。必要な時だけ、JOINしたらいい。インデックスの効率も上がるらしい。

シャーディングする際には、データのシャードへの分割方法がいくつかある。

レンジベース: IDを特定範囲 (ユーザーID、日付) を基準にわける。例えば、ID=1-100をシャード1など。
1. 利点=直感的でシンプル。
2. 欠点=データが均等に分散せず、特定シャードに負荷が集中する。スケールアウト時のデータ移行が大変。
ハッシュベース: IDをハッシュ関数で変換し、それをシャード数でわったあまりで振り分ける。例: シャード=ユーザーID % 4。
1. 利点=データが均等に分散。
2. 欠点=範囲検索は難しい。シャード数を増やす場合、再配置が必要。
ユーザー属性: ユーザーの所属地域別など。
一貫性ハッシュ:

親子関係

親子関係、ツリー構造、階層構造を表現したいことがある。

１つのテーブルで親子関係を管理する方法

基本的に、親コードのカラムをどこかのテーブルに持たせて対応させる。

隣接リスト (Adjacency List): 親子関係が再帰的になっている場合、途中データの削除はやっかいになる。親子の取得が少々複雑で何回もSQLが必要になり厄介。階層構造が2-3階程度なら問題ない。
経路列挙:

Standard

ISO/IEC 9075 - Wikipedia

ISO/IEC 9075がSQLの標準。標準外のSQL/関数は独自実装なので扱いには注意が必要。標準のみの使用が望ましい。

ANSI SQLと呼んだりするらしい。

List of SQL reserved words - Wikipedia: 標準SQLの予約語一覧
SQL syntax - Wikipedia: 標準SQLの構文。

制約

3のキー制約がある。

NOT NULL。
UNIQUE制約: ユニークキー NULL許容
PRIMARY KEY制約: プライマリーキー非NULL。UK AND NOT NULL相当。
FOREIGN KEY制約: 外部キー非NULL。
CHECK制約

Error

SQLSTATE[01000]: Warning: 1265 Data truncated for column

ALTER TABLEでデータ型を変更して、文字数を切り詰めるときに、元データがあって、切り詰められないときに出るエラー。

Transfer

import

主キーが文字列型のテーブルに、外部からインポートする場合、主キーの生成方法が問題になる。

データを削除して取り込む想定なら、自分で連番を作ればいい。SQLで生成してもいい。

COUNT/ROW/ROWS/ROWNUM/ROW_NUMBERなど。

ROW_NUMBERが使えないなら変数で行えるらしい (MySQLのSELECT結果に行番号を振る。 #mariadb - Qiita)。

Design

ID

DB ID

DBのIDの採番規則。非常に重要。全体のパフォーマンスにも影響する。

Instagramの方式が、後発なだけあって優れているように感じる。

Sharding & IDs at Instagram. With more than 25 photos and 90 likes… | by Instagram Engineering | Instagram Engineering

生成されたIDは時間順に並べ替え可能である必要がある(例えば、写真 ID のリストは、写真に関する詳細情報を取得せずに並べ替えることができます)
ID は理想的には 64 ビットである必要があります (インデックスが小さくなり、Redis などのシステムでより優れたストレージを実現するため)
システムには、新しい「可動部分」をできるだけ少なく導入する必要があります。私たちが非常に少数のエンジニアで Instagram を拡張できたのは、信頼できるシンプルでわかりやすいソリューションを選択したことが大きな理由です。

Instagramは独自の計算式でIDを算出している。

日時、ユーザーID、自動増分値。

この3要素で構成して算出している。これでバッティングせずに時間ソートで64ビットで収まる。よく考えられている。

議論

漢(オトコ)のコンピュータ道: IDの設計についてのさらに突っ込んだ議論

IDとコード

コードは広い概念。

IDはコードの内、一意なもの。

Naming

テーブルの主キーの命名規則にいろいろ議論がある。

ケース1=idとすべきか、ケース2=table_idとすべきか。それぞれ言い分がある。

ケース1=id
- 主キー列、外部キー列が明確。
- テーブル名の反復がなくDRY原則に従う。
ケース2=table_id
- USINGで指定可能。
- 複数テーブルで同じ列を同名にできる。

ケース1がいいと思う。テーブル名で十分コンテキストがある。ケース2を採用するならば、全部の列にテーブル名を前置しないと一貫性がない。

USINGはシンプルな結合しかできない。ONは条件を増やせる。

正規化

データの重複をなくし、整合的にデータを取り扱えるようにデータベースを設計すること。

第1-5、ボイスコッド正規形がある。一般的に用いられるのは1-3。

非正規形

正規化されていない状態のテーブル。具体的には、1セルに複数のデータが入っている状態。

学生テーブル
学生ID	学生名	コース名	教授名
1	山田太郎	サッカー、軽音	山田、鈴木
2	菊池太郎	野球	山田
3	川上太郎	バレーボール	山根

例えば、所属サークルに [サークル、軽音] の2個のデータが入っている。これがまずい。

第1正規形

「1つのフィールドには1つの値しか入らない」状態。

非正規形から第一正規形にするには、行でわけるか、列で分けるかの方法がある。

そのままやると、問題がある。行で分けると、主キーが2重になる。列だと大量のNULLセルが生まれる。

行で分けると以下のようになる。

学生テーブル
学生ID	学生名	コース名	教授名
1	山田太郎	サッカー	山田
1	山田太郎	軽音	鈴木
2	菊池太郎	野球	山田
3	川上太郎	バレーボール	山根

主キーが重複するが、ひとまず第一正規形にはなった。

重複がある場合、以下の2パターンがある。

似たカラムが複数 (TEL1/TEL2など)。
1セルに複数の値 (カンマ区切りなど)。

行に分けることがポイント。

第1正規形じゃないと、拡張できなくて困る。カラムを増やす場合、テーブル定義の変更が都度必要になる。

第2正規形

主キーの一部への依存関係をなくす。「部分関数従属性をなくす」。

片方にしか依存しない値を見つけ、それを専用のテーブルに移動する。

例えば、上記の学生テーブルだと、[学生ID/学生名] が一つの塊で、コース名は別。これらの塊は以下の依存関係がある。

学生IDがキーで、学生名がIDに従属している (学生IDで学生名を特定できる)。
コース名だけでコース名と教授名を特定できる。コースID列を追加しても同じこと。

これを3のテーブルに分割する。

ブロックごとのテーブルを作る。
片方のテーブルに、片方のIDを残す。

学生テーブル
学生ID	学生名
1	山田太郎
2	菊池太郎
3	川上太郎

コーステーブル
コースID	コース名	教授名
1	サッカー	山田
2	軽音	鈴木
3	野球	山田
4	バレーボール	山根

関連テーブル、学生詳細テーブル
学生ID	コースID
1	1
1	2
2	3
3	4

残ったIDだけのテーブル。関連テーブル、中間テーブルと呼ぶ。IDを片方に残して、複合主キーとすることもできる。

第２正規形の解決課題。

片方のデータだけ登録できない。
データの不整合が簡単に発生する。

第2正規化を行ったことで、複合主キーが生まれる (「第二正規化」は、複合キーが存在するテーブルのみで行うのでしょうか)。

マスターテーブルじゃないなら、複合主キーも悪くないか。複合主キーを使わないなら、代理キーでIDを生成するだけだから。

この関連テーブルには注意が必要。実装の都合で複合主キーではなく、代理キーを主キーにするなら、複合ユニーク制約をつける「中間テーブルに複合ユニーク制約をつける理由 #SQL - Qiita」。そうしないと、同じデータが登録できてしまう。

第3正規形

「主キー」ではない項目への依存関係をなくす。推移関数従属性をなくす。

例えば、コーステーブルは、コース名に教授名が従属している。学生IDをコーステーブルに残したら、本来コースIDだけで決まるのに余計な学生IDが残る。

これを専用テーブルにする。

従属部分を別テーブルに移動する。
それぞれにレコードIDを付与する。
どちらかのテーブルに分離したテーブルのIDを付与する。

例だと、コースIDではないコース名に教授名が従属している。例えば、コースID=1/3はコース名が異なるが、同じ教授名になっており、教授名はコースIDには従属していない。

コーステーブル
コースID	コース名	教授ID
1	サッカー	1
2	軽音	2
3	野球	1
4	バレーボール	3

教授テーブル
教授ID	教授名
1	山田
2	鈴木
3	山根

Naming

日本語カラム

https://chatgpt.com/share/686b356d-2698-800b-974e-3ff1d34ae7ef

社内システムのような、日本人しか使わないようなシステムだったらあり。中途半端なローマ字略語にするくらいならよっぽどわかりやすい。

やるなら、完全に英語にするとか。文字エンコーディングだけきっちりする。

命名規則

DBスキーマの命名規則はあまりない。Yiiの規約がけっこうしっかりしている。

複数形ではなく単数形でデータベースのテーブルに名前を付ける
フィールド名の前にDBテーブル名を追加しない
モデルクラス名にテーブルプレフィックスを含めない
テーブルのID列に「id」という名前を付けます
意味のある主キー名を避ける
データベーススキーマで外部キーの関係を定義する
「id」で終わる外部キーフィールドに名前を付ける
単数形/複数形の性質を反映する名前関係を行う

Convention

List

いくつかSQLのコーディング規約がある。

大文字小文字

予約語以外、基本的に全部小文字で、複数単語はハイフン区切りにしたほうがいいらしい。

構文強調がないと、formなどと見分けがつきにくいので、大文字にしたほうがいい。可読性優先。

https://grok.com/share/c2hhcmQtMw%3D%3D_7be14fd7-bc9b-460a-935f-d1cbcca413f6

ただ、Oracle databaseは内部的に全部大文字にするので、カラム名とかは全部大文字でOK。

単語区切り

https://grok.com/share/c2hhcmQtMw%3D%3D_7be14fd7-bc9b-460a-935f-d1cbcca413f6 単語区切りはスネークケースがいい。内部的に大文字小文字をどちらかに正規化するRDBが多い。キャメルケースとかは引用符で囲んだりしないと表現できなくて面倒くさい。

JavaScriptのプロパティーがcamelCaseなのが一般的でテーブルカラムをそのまま使えないのが気になる。ライブラリーを間に挟むとか、サーバーAPIで変換かけるとかする。

共通カラム

https://chatgpt.com/share/6864bc60-681c-800b-81cb-f73fcb000f7f

created_at、created_by、updated_at、updated_by、deleted（または is_deleted）みたいなカラムが全テーブルに共通で入ったりする。

これはこのままで問題ない。データに付随するから。

ただし、何回も共通するのは気持ち悪い。

アプリ側の工夫で処理はまとめる。

例えば、TraitsやMixinで共通カラムの処理はまとめる。ほかに、ViewとかでSQLでも共通化できる。

Data

enum/string

区分系データを数字で持つか、文字列で持つか。

文字列だとわかりやすい。数字で持つと、意味の把握にカラム定義など、別の資料を確認する必要がある。アプリ側で表示に毎回そのマップや変換が必要になる。

数値で必要ならマスターの主キーにする。基本は文字列でいいと思う。

画像データのパス/BLOBでの格納

画像のファイルサイズが1MB以下の場合は問題ない。が、それ以外だと問題があるので、ファイルパスで扱うのがいいらしい。

NULL

Ref: NOT NULL 制約をつけるべきカラムとは #PostgreSQL - Qiita.

絶対に必要な項目にNOT NULL制約をつける。NULLを許容する列は、最後のほうに集めると、ストレージなどの効率がいい。

例えば、取得に失敗した場合に、値をどうするのか？などを考えるとよい。

int/string

データベースのカラムでIDやコード類を定義する場合、型をintなどの数値系、charなどの文字列系にするかの議論がある。

WHEREの範囲、算術計算など数値計算が伴うなら数値。データを何に使うかの意味論が重要。
数値のほうが高速。
郵便番号のように先頭が0つく場合、文字列。
数値だと、auto incrementで登録時の重複除去を保証できる。
値を変更する可能性がないなら数値。
文字列型だと文字数の検討が必要。

INT/BIGINT

型

TINYINT
- 符号付き: -128から127まで
- 符号なし: 0から255まで
- 1バイト（8ビット）を使用
SMALLINT
- 符号付き: -32,768から32,767まで
- 符号なし: 0から65,535まで
- 2バイト（16ビット）を使用
MEDIUMINT
- 符号付き: -8,388,608から8,388,607まで
- 符号なし: 0から16,777,215まで
- 3バイト（24ビット）を使用
INT
- 符号付き: -2,147,483,648から2,147,483,647まで
- 符号なし: 0から4,294,967,295まで
- 4バイト（32ビット）を使用
BIGINT
- 符号付き: -9,223,372,036,854,775,808から9,223,372,036,854,775,807まで
- 符号なし: 0から18,446,744,073,709,551,615まで
- 8バイト（64ビット）を使用

int型は4バイトでsignedなら2,147,483,647 (21億) が最大値。1日1万件増加するとして588年。10万件で58年。増加追加量次第だが、中小規模なら通常はint型で問題ない。

BIGINTなら、1日100万件で25269512429年。BIGINTならほぼ問題ない。

ただ、BIGINTはINTの2倍のサイズになるのでストレージも消費する。よく考えておく。

BIGINTでも足りない場合、プライマリーキーを文字列にして、UUIDやhash値を使う。

NULLと空文字

住所などのようなテキスト系のデータで、NULLを許容するべきか？空文字とNULLで意味を区別すべきか？

NULLと空文字の意味論

NULL=値がない、何であるか不明な状態。わからないという状態。
空文字=値がわかっていて、空なことが分かっているという状態。

例えば、人名のミドルネームの扱いがある。

ミドルネームなし=''
ミドルネームの有無不明=NULL

これを区別するにはNULLしかない。空の文字列は値。

NULLがないほうがSQLはシンプルになる。が、値が空なのか、不定なのか判別できない。判別したいなら、別のカラムが必要になる (分析用途のテーブルにnullはいらない)。

[カラム] なしと不明を区別する必要があるかどうか？不明=なしで問題ないなら空文字でいいと思われる。実務上違いがない。

文字列型なら空文字でいいが、数値型ならNULLを使うしかない。

データの要件を考えて判断する。基本は、NULLは使わない。文字列の選択肢なども。

ただ、他の日付などは空=NULLにするしかない。他のデータ型と合わせるなら、空=NULLでもいい気がする。

NULL

第8回　NULLって何？

そもそもNULLを理解する必要がある。

NULLは、「値が入っていない、値なし、値不明」を意味する。

空文字は「空という値が入っている」とみなす。値なしとは意味が違う。

VARCHARの文字数定義

varcharで最大文字数を定義できる。この最大文字数の考え方。

DB上は最大文字数は、定義するだけだとあまり意味がない。実際に使用された文字数でデータがカウントされる。varchar(2)でもvarchar(255)でも同じ。

ただし、varchar(255)までは1バイトだが、256文字目からは長さプレフィックスに2バイト使用する。なので、不必要に256文字のカラムにしない。

VARCHARの長さは、技術的・物理的なものというより、一種の制約、ビジネスルールとして考えたほうがいい。

無駄に長い文字数を許容すると、アプリ側でその幅を考慮必要になる。UI上の制約になるので注意したほうがよく感じる。

TEXT型

DB設計でtext型とvarchar型の違いや最大文字数を調べてみる | 東京初めてエンジニア

text型の最大長は65,535byteです。

半角→65,535字

全角→32,767字

また、絵文字とか入ってくるような場合など想定して、UTF-8の最大文字サイズ3byteを考えると、最大21,845字が入力できるようになります。

ちな、4byte文字の場合は、16,383字が入力できるっぽい。

byteサイズで65535を超えない値まで保存が可能です。

データの持ち方が違う。TEXT型はポインターをテーブルに保有して、実データは別領域。速度とかが変わってきそう。

外部キー制約

整合性制約の有無によるINSERTの速度差 - kagamihogeの日記

外部キー制約をいれると、データの整合性が保証できる。代わりに速度が落ちる。ないほうがいいか。

Relation

多対多

原則として中間テーブルを使う。

中間テーブルなしでやる場合、1列に複数レコードを入れるしかない。PostgreSQLなどの配列、JSON、カンマ区切り文字列。やらないほうがいい。

2個のテーブルにそれぞれのID列をもたせるだけでは正しく管理できない。例えば以下の問題が生じる。

データの重複: それぞれに相手のID列を渡す必要があり、同じデータを何度も追加必要になる。
管理の煩雑: あるIDを削除する場合、もう片方のテーブルにもIDが残る。
性能: 両方のテーブルの更新が必要で性能が低下する。

中間テーブルの命名規則。

2のテーブルを_でつなげる。2テーブルの関係が明確なら関係の名前をつけるといいが、これは考えるのが難しい。

多対多のデータの登録画面の実装方法は2種類。

追加ボタンで関連を追加できるようにするか、チェックボックス、ドロップダウンリストで複数選択できるようにする。

ポリモーフィック

1個のテーブルで複数のテーブルに関連させるパターン。

画像テーブルを、投稿テーブルとプロフィールテーブルに関連させる場合。

1テーブルだけなら、画像テーブルにプロフィールテーブルの外部キーを持たせれば済んでいた。これを1テーブルで投稿テーブルにも対応させるパターン。

外部キー列に追加してテーブル名の列も追加する。これで複数テーブルを特定する。一見柔軟だが、外部参照制約をつけられなかったり、SQLが複雑になるなどの欠点がある。SQLが複雑になるということは性能も悪くなる。

ポリモーフィック関連を使わなくても、中間テーブルを使って親子を関連付けることはできる。ただし、テーブル数は増える

交差テーブル: 1レコードが共通で呼ばれる場合に対処できない。
共通の親テーブル: 親idを全部で持つ。1レコードが共通で呼ばれる場合に対処できない。

親テーブルを持つパターンが理にかなっている気がする。オブジェクト指向の継承になっており、余計なテーブル数も少なくシンプル。

ポリモーフィック関連は使わないほうがよさそう。

Function/Operator

MySQL :: MySQL 8.0 リファレンスマニュアル :: 12 関数と演算子

Flow control function

表 12.7 「フロー制御演算子」

名前	説明
`CASE`	CASE 演算子
`IF()`	If/else 構文
`IFNULL()`	Null if/else 構文
`NULLIF()`	expr1 = expr2 の場合に NULL を返します

CASEとIFはSQL文としても同名が存在する。ただし、使うタイミングがSQL文だとストアドプロシージャであまり使うことはない。演算子と関数をよく使う。

SQL

About

【SQL】4種類の命令(DML, DDL, TCL, DCL) #SQL - Qiita

①データ操作言語　DML (Data Manipulation Language): select/insert/update/delete/lock table/explain

②トランザクション制御言語　TCL (Transaction Control Language): commit/rollback/set transaction/savepoint

③データ定義言語　DDL (Data Definition Language): create/drop/alter/truncate/rename/comment

④データ制御言語　DCL (Data Control Language): grant/revoke

DML

DML (Data Manipulation Language)。データ操作用言語。

INSERT/SELECT/UPDATE/DELETEが該当。

Expression/式

CASE

2種類の構文がある。

CASE selector
WHEN selector_value_1 THEN result_1
WHEN selector_value_2 THEN result_2
...
WHEN selector_value_n THEN result_n
[ ELSE
  else_result ]
END

JSONを作る際にも使う。

DELETE

DELETE文（SQLを基本から学ぶシリーズ）

DELETE FROM [table] [条件]

構文がシンプル。

INSERT

basic

INSERT INTO テーブル名 (列名1, 列名2,...) VALUES (値1, 値2,...), (), ();

INSERT INTO テーブル名 VALUES (値1, 値2,...);

列名と値の順番があっていれば、順番はテーブルカラム定義の順番じゃなくてもOK。カラム名を省略した場合、全カラムが定義の順番で必要。

基本はカラム名を指定したほうがいい。

VALUESの後、,()で複数レコード一度に追加できる。

INSERT文に（SELECT結果＋入力結果）を入れる #MySQL - Qiita

SELECTの結果を値に使うこともできる。その場合、構文が若干異なる。

INSERT INTO `テーブル名１` (col1, col2, ...)
  SELECT col1, col2, ... FROM `テーブル名２`

VALUESが不要。

例えば、データベースに登録されていない商品をIDが重複しないように、データを追加したい場合だとこんな感じに書きます。

INSERT INTO `テーブル名` (id,item_name)
  SELECT MAX(id)+1,"商品名" FROM `テーブル名`

bulk

PHP/MySQL でレコードを N 件ずつバルクインサート | Born Too Late

一括INSERTする場合、一度の件数が大きすぎると問題がある模様。

1000件ずつくらいに分割するといいらしい。

UPDATE

Basic

UPDATE文（SQLを基本から学ぶシリーズ）

UPDATE (表名) SET (カラム名1) = (値1) WHERE (条件);
UPDATE (表名) SET (カラム名1) = (値1), (カラム名2) = (値2) WHERE (条件);

一括で更新する。

別のテーブルで更新

UPDATE文（SQLを基本から学ぶシリーズ）

2種類の方法がある。

UPDATE employees SET employees.manager_name =
( SELECT manager.manager_name FROM manager
  WHERE employees.emp_no = manager.emp_no );

服問い合わせ、サブクエリー。

UPDATE employees JOIN manager
ON employees.emp_no = manager.emp_no
SET employees.manager_name = manager.manager_name

JOIN。JOINのほうがシンプルかもしれない。

UPSERT

Ref: 【総まとめ】UPSERTとは？DBごとの違いは？ | 初心者DIYプログラミング入門.

INSERTするかUPDATEするか、一度SELECTで値の有無を確認してから本来しないといけない。SQLを2回実行必要で、処理が無駄になるので、これを1回で行う。これがUPSERTと呼ばれるもの。ただし、UPSERTというSQLがあるわけではない。MERGE文が標準だが、実装していないものがあって、DBごとの独自拡張になる。

MySQL

INSERT ... ON DUPULICATE KEY UPDATE

MySQLにMERGE文はない (sql - Is merge statement available in MySQL - Stack Overflow)。代わりにINSERT ... ON DUPULICATE KEY UPDATEという文を使う。

「sql - What are practical differences between `REPLACE` and `INSERT ... ON DUPLICATE KEY UPDATE` in MySQL? - Stack Overflow」によると、REPLACE文も使えるが、内部的に一度削除して、副作用があるので、INSERT ... ON DUPULICATE KEY UPDATEがいい模様。

MySQL :: MySQL 8.4 Reference Manual :: 15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

複数レコードを一括で更新したい場合。「MySQLでbulk insert + on duplicate key updateしたい - oinume journal」VALUESを使う。

INSERT INTO users VALUES
  ('akuwano', 15), ('oinume', 14), ('oranie', 13)
  ON DUPLICATE KEY UPDATE age = VALUES(age);

MySQL insert row on duplicate key update multiple columns - Stack Overflow

INSERT INTO tb (name, date, stat1, stat2, stat3)
VALUES (?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE stat1 = stat1 + VALUES(stat1), stat2 = stat2 + VALUES(stat2), stat3 = stat3 + VALUES(stat3)

複数列指定する場合、列名 = values(列名), で複数列挙する模様。

AUTO_INCREMENT

MySQLのINSERT ... ON DUPULICATE KEY UPDATEは扱いに注意が必要。AUTO_INCREMENTがあると、更新でも増加する。

基本的に最初にINSERTを実行するから。

挙動として、一度のINSERT ... ON DUPULICATE KEY UPDATE中は連番が保証される。終わったらUPDATE回数分AIが増分される。

大量更新前提のテーブルの場合、増分値がすごい勢いで増加する。いくつか対策がある。

INSERT ... ON DUPULICATE KEY UPDATEを使わず、昔ながらのSELECTの結果を見てINSERT/UPDATEする。
AUTO_INCREMENTをそもそも使わない、idを使わない削除する。
idをbigintにする。
ストレージエンジンをInnoDBではなくMyIsamを使う。

最初2個が回答になっている。実行中は連番なので、ON DUPLICATE KEY UPDATEの使用後に、ALTER TABLEでAUTO_INCREMENTの初期値をリセットする。

INSERT INTO `table_blah` (`the_col`) VALUES("the_value")
ON DUPLICATE KEY UPDATE `the_col` = "the_value";

SET @NEW_AI = (SELECT MAX(`the_id`)+1 FROM `table_blah`);
SET @ALTER_SQL = CONCAT('ALTER TABLE `table_blah` AUTO_INCREMENT =', @NEW_AI);
PREPARE NEWSQL FROM @ALTER_SQL;
EXECUTE NEWSQL;

これでOK。

なお、実行中の連番は注意が必要。1回のSQL内で同じ行に重複してINSERT/UPDATEしている場合、SQL実行中にAIが増加する。重複していなかったら連番。SQL実行前に事前に重複を除去したらいい。SQLの用意に工夫が必要。

UNIQUE

UPSERTで挿入する際のUNIQUE制約には注意が必要。複合UNIQUEでNULLを許容する場合、NULLは固有扱いになるので、実質重複キーが生まれる。回避したければ。

複合UNIQUEにはNOT NULL
文字列結合、ハッシュなど算出可能な参照用のカラムを追加する。

注意すべき挙動の整理

AUTO_INCREMENTとUNIQUE制約に由来する注意すべき挙動がある。(MySQL 5.7で確認。

PKの指定がなく、UNIQUEで行特定の場合、PKがAIで更新→更新ならPKは常に必要。事前にSELECTでPKを取得しておく。
1. 重複を削除するので、そもそもUNIQUE制約は不要かもと思ったが、重複は1SQL内で複数ある場合の話。重複が生じるのでUNIQUEは必要。
UPDATE発生時、UPSERT後にAIがUPDATE回数分増加→UPSERT後にAIを最大値で初期化。
UPDATE発生時、実行中にもAI増加→PKなしの複数UPDATE時はUPSERT実行前のSQLで一番最後を残して重複レコード除去。
マスター系テーブルで固有でINSERTする場合、重複除去のためにNOT NULL+UNIQUE制約をつけておく。
1. NULL != NULLが成立するので、UNIQUE制約をつける場合はNOT NULLもつけておく。

Oracle

Basic

UPDATE文実行時に、対象レコードが0だったとしても失敗しないので注意。OracleだとMERGE文がUPSERT相当。

MERGE INTO target_table tgt
USING (SELECT :id AS id, :name AS name FROM dual) src
  ON (tgt.id = src.id)
WHEN MATCHED THEN
  UPDATE SET tgt.name = src.name -- キー存在時の処理
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (src.id, src.name); -- キー不在時の処理

dualはOracle専用のダミーテーブル (【勉強ブログ】Oracleだけにある謎の表「DUAL」って何？―ORACLE MASTER Silver DBAへの道 #6｜よっしー)。

GROUP BY

Basic

【SQL入門】複数のデータをグループ化するGROUP BYの使い方 | 株式会社AMG Solution

テーブルの値でデータをグループ化する句。

基本構文

SELECT 要素名 FROM テーブル名 GROUP BY グループ化したい要素名;

SELECT部分でGROUP BYで指定したカラム名の指定が必須？

GROUP BYで指定したカラム以外は、集約関数を使わないと選択できない。テキストのように集約不能な場合でも。その場合は集約の意味はない。

連番

MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita

MySQL 5.7だと変数で実現。

mysql> select * from history;
+----+----------+--------------------+------------+
| id | group_id | name               | created_at |
+----+----------+--------------------+------------+
|  1 |        1 | Cupcake            | 2020-01-01 |
|  2 |        1 | Donut              | 2020-01-02 |
|  3 |        2 | Eclair             | 2020-01-03 |
|  4 |        2 | Froyo              | 2020-01-04 |
|  5 |        2 | Gingerbread        | 2020-01-05 |
|  6 |        3 | Honeycomb          | 2020-01-06 |
|  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
|  8 |        4 | Jelly Bean         | 2020-01-08 |
|  9 |        4 | KitKat             | 2020-01-09 |
+----+----------+--------------------+------------+
9 rows in set (0.00 sec)

SELECT
  @rownum := IF(@prev_group_id = group_id, @rownum + 1, 1) AS rownum, -- 連番の列
  history.id,
  @prev_group_id := group_id AS group_id,
  history.name,
  history.created_at
FROM
  history,
  (select @rownum := 0) hoge,
  (select @prev_group_id := '') fuga
ORDER BY
  group_id,
  created_at DESC

+--------+----+----------+--------------------+------------+
| rownum | id | group_id | name               | created_at |
+--------+----+----------+--------------------+------------+
|      1 |  2 |        1 | Donut              | 2020-01-02 |
|      2 |  1 |        1 | Cupcake            | 2020-01-01 |
|      1 |  5 |        2 | Gingerbread        | 2020-01-05 |
|      2 |  4 |        2 | Froyo              | 2020-01-04 |
|      3 |  3 |        2 | Eclair             | 2020-01-03 |
|      1 |  6 |        3 | Honeycomb          | 2020-01-06 |
|      1 |  9 |        4 | KitKat             | 2020-01-09 |
|      2 |  8 |        4 | Jelly Bean         | 2020-01-08 |
|      3 |  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
+--------+----+----------+--------------------+------------+
9 rows in set (0.00 sec)

MySQL8でROW_NUMBERが使えるならもっと簡単。

SELECT
  ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY group_id, created_at DESC) AS rownum,
  id,
  group_id,
  name,
  created_at
FROM
  history
ORDER BY
  group_id,
  created_at DESC

SELECT

MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.10 SELECT ステートメント

JOIN

外部テーブルと外部キーで結合の方法がいくつかある。

INNER JOIN: AND。両方のテーブルにある項目のみ。
- SELECT ... FROM ... INNER JOIN テーブル名 ON 項目A = 項目B
OUTER JOIN: OR。片方がなくても。
- LEFT OUTER JOIN: 左を基準。右側はNULLありえる。
- RIGHT OUTER JOIN: 右を基準。左側はNULLありえる。
CROSS JOIN: 全組み合わせ。
NATURAL: 同名項目を取得。
UNION/UNION ALL: 2以上のSELECT結果の結合。

他にWHEREで結合する方法がある。

結合すると、同名カラムは上書きされる。必要なら、select column as column_nameで別名をつける。

結合する場合、同じ値だから通常は問題ない。

列名が重複する場合の対応

addSelectで列名をASで地道に追加する。これが確実。プリペアードステートメントなどを使うと、プログラム側でできなくはない。

【Laravel】目的別クエリビルダの記述方法まとめ（記事を書く前の整理用）

結合したテーブルに同じ名前のカラムがあっても正しく取り出す方法【MySQL+PHP】 - りまねどっとねっと

PHP PDOだったら列番号で取得するオプションなどがあるのでそれで対応できる。

他にGroupConcatで文字列で結合する。

SQL select join: is it possible to prefix all columns as 'prefix.*'? - Stack Overflow

ダミーの空列を追加すれば、処理はしやすい。が、そもそも同じ列名を取得できるのか？

同じテーブルを複数回Joinしたい場合 #MySQL - Qiita

SELECT * FROM table
INNER JOIN join_table AS join1
ON table.id = join1.id
INNER JOIN join_table AS join2
ON table.another_id = join2.another_id

ASで別名をつける。

JOINとWHERE

内部結合の場合、結果も速度も同じ。

ただ、外部結合はJOINしかできない。

JOINだと、テーブルと結合条件が一か所にまとまるためわかりやすい。

SELECT * 
FROM table1, table2 #←結合させたいもの
LEFT OUTER JOIN (
    # 副問い合わせとか長いSQLが入ると想定
) AS sub
ON table1.id=sub.id AND table2.id=sub.id
WHERE table1.id=table2.id #←結合条件

SELECT * 
FROM table1
INNER JOIN table2 ON table1.id=table2.id #←結合させたいものと結合条件が一緒
LEFT OUTER JOIN (
    # 副問い合わせとか長いSQLが入ると想定
) AS sub
ON table1.id=sub.id

複数テーブルが登場するなら、基本はJOINがよいだろう。

information schema

Information schema - Wikipedia

DBのテーブル情報を格納する特殊なテーブルが [information schema]。

標準化されているが、SQLiteやOracle Databaseなどで未対応。

MySQL/PostgreSQLなどでは対応されている。が、SQLほど標準化されていないので扱いには注意。

「Why doesn't SQLite support INFORMATION_SCHEMA? - Quora」にあるように、SQLiteのような軽量な実装にすると、このメタデータのテーブル実装はコストが大きいのだと思われる。

VIEW

SELECT文の結果に別名をつけるようなこと。

何回も呼び出すSELECT文を省略でき、シンプルにできる。また、テーブルを直接使わないこととで、機密情報へのアクセスを制御できる。

SELECT文なので、ストレージを消費しない。
SELECT文呼び出しなので、複雑だと時間がかかる。

一括更新

Ref: MySQLで複数行を一括更新するクエリの実装方法.

ELT/FIELDを併用すれば一括更新可能とのこと。

NULLを含むLIKE

sql - like '%' does not accept NULL value - Stack Overflow

【SQL】NULL値を制御/SQLマスターへの道「COALESCE」 #初心者 - Qiita

LIKEで文字列検索する際、SELECT * FROM DB WHERE <column> LIKE '%%';であいまい検索してもNULLの場合ヒットしない。

where COALESCE([table].[column],'') like '<parameter>'
IFNULL([table].[column], '') like '%'

COALESCEかISNULLで引数に空文字を指定するとうまくいく模様。

COALESCEが標準なのでこれを使う。

COALESCE (コアリース) は引数のリストから、最初の非NULLを取得する。NULLを含むデータの処理に便利。

ISNULL(<value>, <orValue>) はCOALESCEの引数2個版。理由がないならシンプルなISNULLを使うとよい。

同じ名前の関数で、動きが違う。

「MySQL の ISNULL()・COALESCE() - MySQL の基礎 - MySQL 入門」MySQLのISNULLは引数1個で、boolを返すだけ。

COALESCE() や IFNULL() を使うしかない。

NULLIF/COALESCEは標準。

MYSQLのNULLIFは引数1=引数2の場合、trueでNULLを、そうでなければ1個目の引数を返す。IFNULLと逆。

NULLIF(引数1, 引数2);

以下の CASE 文と同等です。

<CASE 
	WHEN 引数1 = 引数2 
		THEN NULL 
		ELSE 引数1
END;

なお、Oracle databaseの場合、NVL/NVL2という関数もある (OracleのNULL置換（NVL、NVL2、COALESCE、三者の違いやどれを使うか選ぶ時の参考など） #oracle - Qiita)。

日付範囲

注意点がある。

2024-01-01から2024-01-02としたい場合、いくつか記述方法がある。

where PURCHASE_DATETIME >= '2024-01-01' and PURCHASE_DATETIME <= '2024-01-02'

SELECT * FROM users WHERE id BETWEEN 2 AND 4;
SELECT * FROM users WHERE id >= 2 AND id <= 4;

ただし、上記はまずい。2024-01-02T00:00までしか判定されない。方法がいくつかある。

DATE関数で日付部分のみ抽出: SELECT * FROM users WHERE DATE(created_at) BETWEEN '2022-01-01' AND '2022-01-02'
1日進めて小なりで抽出: where PURCHASE_DATETIME >= '2024-01-01' and PURCHASE_DATETIME < '2024-01-03'
ミリ秒まで指定: DATETIME <= '2010-10-31 23:59:59.999'

3はミリ秒の.999の部分がどこまでいるのか？という問題があるしスマートではない。

1-2で対応するのがよい。1がいいか。

Echo/SELECTだけでtable作成

SQLでecho/print するための代替方法 - それマグで！

SQL SELECT基礎(3) その場で表を作る #SQL - Qiita

SQLでechoのようにリテラル表示したいことがある。

select '##  A ' as '';

asでカラム名を空にすると、必要なものだけになる。

UNION ALLで複数のselectを結合できる。

SELECT 1 AS a, 2 AS b, 3 AS c, 4 AS d
UNION ALL
SELECT 2,4,6,8
UNION ALL
SELECT 3,6,9,12

これで4x3の表を作れる。

引用符

DBごとのSQLのクォーテーションを整理したった #MySQL - Qiita

	'シングル'	"ダブル"	`バック`	[大カッコ]	備考
MySQL	文字列定数	文字列定数	引用識別子	（なし）	引用識別子はUNIX系では大文字小文字を区別1
MySQL （ANSI_QUOTES モード）	文字列定数	引用識別子	引用識別子	（なし）
PostgreSQL	文字列定数	引用識別子	（なし）	（なし）	引用識別子では大文字小文字を区別
Oracle	文字列定数	引用識別子	（なし）	（なし）	引用識別子では大文字小文字を区別
SQLite	文字列定数	引用識別子	引用識別子	引用識別子
Microsoft SQL Server (Transact-SQL)	文字列定数	引用識別子	（なし）	引用識別子	QUOTED_IDENTIFIER が ON (既定値) の場合

基本的に、以下と思っておけばいい。

一重引用符: 文字列定数

二重引用符: 引用識別子

引用識別子は、テーブル名、カラム名など、DB中のオブジェクト。

SELECTのASなどで指定するリテラルは文字列定数扱い。あくまで定義済みのオブジェクトかどうかで変わる模様。

引用識別子

MySQL :: MySQL 8.0 リファレンスマニュアル :: 9.2 スキーマオブジェクト名

DBのテーブル名、カラム名など、DB中のオブジェクトを引用識別子と呼ぶ。引用識別子は、引用符で囲んだり、囲まなかったりする。特殊文字を含んだり、予約語の場合、囲みが必須になる。

識別子の条件は以下。

引用符不要
- ASCII: [0-9,a-z,A-Z$_] (基本的なラテン文字、0-9 の数字、ドル、下線)
- 拡張: U+0080 .. U+FFFF
識別子は数字で始められるが、数字のみは引用符必要。
空白終わりはNG。
引用符で囲んでいる場合に限り、識別子に引用符を含められる。その場合、識別子扱いの引用符は二重にする必要がある。
テーブル名などの修飾子のピリオド.の後に続く名前は、識別子が必須のため、予約語でも引用符不要。

スペースなどの、上記で許容されている$_以外の記号 (スペース、ハイフン、スラッシュなど) を含む場合に囲みが必要。

なお、文字列定数は常に一重引用符が必要。数字などは囲み不要。

COMMENT

SQL記述文内でコメントアウトする。単一行・複数行用の書き方 | アナリティクス沖縄│DataAnalytics

SQLのコメントアウト。癖がある。

[-- ]: ハイフン2個+スペースの後がコメント扱い。
/* */: ブロックコメントアウト。

他に、テーブルやカラムにはCOMMENT文でコメントを残すことができる。

https://chatgpt.com/c/67c51145-8b6c-800b-8983-5153f6fcf882

ただし、DB自体へのコメントなどはできない。DB自体にコメントを残したければ、システムテーブルを作るか、外部文書で情報を残す。

行番号

MySQL 8.0からROW_NUMBERが使える。8.0未満だと変数を使って実現する。

SET @rownum=0; SELECT @rownum:=@rownum+1 as ROW_NUM, TABLE_NAME FROM information_schema.TABLES LIMIT 10;
SET @row=0; SELECT @row:=@row+1 as ROW_NUM, t.* FROM [table] t;

DDL

データ定義言語　DDL (Data Definition Language)

命令	説明
CREATE	テーブルを新規作成する
DROP	既存のデータベースオブジェクトを削除する
ALTER	既存のデータベースオブジェクトを変更する
TRUNCATE	テーブル内のデータを全削除する
RENAME	既存のデータベースのオブジェクトの名前を変更する
COMMENT	データベースのオブジェクトのコメントを定義、または変更する

CREATE TABLE

複合制約

複合unique制約のつけ方 - テックメモ跡地

CREATE TABLE example(
    column1    SERIAL       PRIMARY KEY,
    column2    INTEGER      NOT NULL,
    column3    INTEGER      NOT NULL,
    UNIQUE ( column2, column3 )
);

複製

いくつか方法がある。

テーブル定義とデータのコピー。

CREATE TABLE "新規作成するテーブル" AS SELECT * FROM "コピー元テーブル";

SHOW tables

テーブル一覧やテーブルの確認方法。

完全に標準的な方法はない。

information_schema.tables
MySQL: SHOW TABLES
SQLite: .tables/SELECT * FROM dbname.sqlite_master WHERE type='table' [sql - How can I list the tables in a SQLite database file that was opened with ATTACH? - Stack Overflow];
database - How to show tables in PostgreSQL? - Stack Overflow

一番確実なのはテーブルに対してSELECTしてエラーが出るかを確認。

SELECT count(*) FROM $table;

他に、information_schema.tablesを使う方法がある。MySQL/PostgreSQLなどSQLiteがないならこれでもOK。

select count(*) from information_schema.tables where table_name = 'tablename';

ALTER TABLE

テーブル定義を変更する文。ほぼ標準化されていないので、DB毎の独自構文を理解する必要がある。

SQL│テーブルの定義を変更する。列のデータ型や名前、順番の変更 | アナリティクス沖縄│DataAnalytics

-- テーブル名変更
ALTER TABLE [before table name] RENAME TO [after table name];
-- 列名変更
ALTER TABLE [table name] RENAME COLUMN [before column name] TO [after column name];

テーブル名と列名の変更だけシンプルで全体共通の模様。

COLUMN

DB製品ごとに微妙に構文が違う。

RENAMEだけ共通の模様。RENAMEとALTERはPostgreSQLと共通？

,で区切って、同じテーブルへの複数のサブ文？を1文で書ける。

PostgreSQL

ALTER TABLE

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]

MySQL

MySQL :: MySQL 8.4 Reference Manual :: 15.1.9 ALTER TABLE Statement

CHANGE: 改名と定義。MySQLの独自拡張。カラム名を常に2回書かないといけないが、改名と定義変更を同時に行える。
MODIFY: Oracleとの互換用。定義のみ。カラム名1回のみ。単一カラムの定義変更用。
RENAME Oracleとの互換用。COLUMN: 改名のみ。
ALTER: デフォルト値の変更。
ADD: 列の追加

ALTER TABLE t1 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

ALTER TABLE t1 RENAME COLUMN b TO a;

ALTER TABLE <table-name> ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

カラム追加時は、AFTERでどの列の後ろに追加するかを指定できる。指定しない場合、末尾になる。

Oracle

ALTER TABLE {テーブル名} ADD (列定義).
ALTER TABLE table_name ADD column_name data_type constraint;
ALTER TABLE table_name 
ADD (
    column_1 datatype constraint,
    column_2 datatype constraint,
    ...
);

-- table1に列pnoとmemoを追加するSQL
ALTER TABLE table1 ADD (pno NUMBER(5,0) ,memo VARCHAR2(100));
ALTER TABLE table_name ADD new_column VARCHAR2(50) NOT NULL;

なお、Oracle Databaseだとafter/before相当のカラム位置指定は不能。追加後の位置変更も不能。テーブル再作成しかない。

https://grok.com/share/c2hhcmQtMw%3D%3D_da633c97-e4aa-4284-8100-93ef74cf9be5

AUTO_INCREMENT

MySQL :: MySQL 8.0 リファレンスマニュアル :: 3.6.9 AUTO_INCREMENT の使用

レコードの挿入時に番号を自動で増分してくれる仕組み。DB側でIDの一意性を保証してくれるので、同時実行などをアプリ側で考えなくていいので使うと楽。

増分のルールがある。

INSERT時にカラムを未指定または、値が0かNULLの場合に増分。
値は1から始まる。
値を指定してINSERTした場合、既存の行の最大値+1が次の値。

次の値を指定も可能。

 ALTER TABLE tbl AUTO_INCREMENT = 100;

複合主キーの変更

https://grok.com/share/c2hhcmQtMw%3D%3D_c20aeee5-b5f2-4e7f-b583-c7d6d1276ec5

複合主キーを探して、削除して、設定する。oracle databaseの話。

SELECT constraint_name
FROM user_constraints
WHERE table_name = 'YOUR_TABLE' AND constraint_type = 'P';

ALTER TABLE your_table
DROP CONSTRAINT PK_YOUR_TABLE;

ALTER TABLE your_table
ADD CONSTRAINT PK_YOUR_TABLE PRIMARY KEY (col1, col2, sub_no);

テーブルをコピーして練習してからのほうがいいかも。

削除

DELETE文 TRUNCATE文 DROP文の違い(SQL構文) | 株式会社アースリンク

テーブルのデータ削除の方法がいくつかある。

DELETE: COMMITしてなければロールバック可能。WHEREで行を指定して削除。そのため多少時間かかる。AUTO_INCREMENT維持。
TRUNCATE: 全削除して再作成。DELETEより速い。ロールバック不能。AUTO_INCREMENTもリセット。
DROP: ロールバックも不能でテーブル構造も消える。AUTO_INCREMENTもリセット。

DELETE FROM table_name;
TRUNCATE TABLE table_name;
DROP TABLE table_name;

基本はTRUNCATEでOK。

Function

String

LPAD/RPAD

SQL-2023標準

LPAD(カラム, 桁数, 埋め文字)
RPAD(カラム, 桁数, 埋め文字)

LEFT/RIGHT

SQL-2023標準。

指定した文字数で切り取る。

CONCAT

カラム同士を結合したいことがある。いくつか方法がある。

||: 標準SQL (2008 5.2)。ただし、MySQLはデフォルトだとOR扱いで使用不能。PIPES_AS_CONCAT モードだとOK。
CONCAT(): 非標準。MySQLなどデフォルトで||未対応なDBの場合こちら。

SELECT 'a' || 'b'
SELECT CONCAT(C1, C2) AS C1C2 FROM [table_name];

Other

Variable

SQL内で変数を使う構文が用意されている。

MySQLの場合。

ユーザー変数という呼び名？

書式=@var_name。var_nameが変数名。
変数名の使用可能文字=には英数字_$が使用可能。加えて、''で囲めば (@'var-name') 文字には何でも使える。
制限=大文字小文字無視。最大64文字。
スコープ=ユーザー変数はセッション固有。

変数割当

SET @var_name = expr [, @var_name = expr] ...

SET文で行う。=の他に:=も使える。なお、@var_name := exprでSETを省略することも可能だが、これは廃止予定。

ただ、短縮時に便利なことがある (sql - Getting values from previous rows using user-defined variable in MySQL - Stack Overflow)。

SET @a = 1;
SELECT @a, @a:=@a+1;

SELECT内で割り当てと使用を同時にする場合、SETは構文エラーになる。それに、ユーザー変数を含む式の評価順序は未定義。やめたほうがいい。

ただし、直前の行を参照する場合などもこの構文を使うしかない。MySQL 8以上ならWIndow関数でこの構文は不要になる。MySQL 8未満の考慮が必要ならやむを得ない。

文を分けずに1文で変数定義と使用を同時に行う場合。FROMで定義するしかない (MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita)。

SELECT
  @rownum := IF(@prev_group_id = group_id, @rownum + 1, 1) AS rownum, -- 連番の列
  history.id,
  @prev_group_id := group_id AS group_id,
  history.name,
  history.created_at
FROM
  history,
  (select @rownum := 0) hoge,
  (select @prev_group_id := '') fuga
ORDER BY
  group_id,
  created_at DESC

変数は定義しないと型が認識されなくて、値が更新されないらしい。

階層テーブルの取得

SQL Interview Question8: Parent Child Relationship-Use Case of Self Join | by Deeksha Singh | Geek Culture | Medium

select a.son as grandson,
c.son as grandfather,c.dead_or_alive as dead_or_alive
from family_relation as a
left join family_relation as b on a.father=b.son
left join family_relation as c on b.father=c.son

こんな感じで、同じテーブルに対して、別名をつけてそれで連結して取得する。これで子と親を横に並べられる。

ピボットテーブル

行データの列データへの変換をピボットと呼ぶらしい。ピボットテーブルの作成という方法で実現できる模様。

いくつか方法がある。

CASE文とGROUP BY句
独自拡張: PIVOT演算子、WITH (MySQL)

CASE文とGROUP BY句で実現するのが基本。

sales
product_id	month	sale_amount
1	Jan	100
1	Feb	150
1	Mar	120
2	Jan	200
2	Feb	250
2	Mar	210

SELECT 
    product_id,
    MAX(CASE WHEN month = 'Jan' THEN sale_amount END) AS Jan,
    MAX(CASE WHEN month = 'Feb' THEN sale_amount END) AS Feb,
    MAX(CASE WHEN month = 'Mar' THEN sale_amount END) AS Mar
FROM 
    sales
GROUP BY 
    product_id;

product_id	Jan	Feb	Mar
1	100	150	120
2	200	250	210

値が決まっているならこの方法でもいい。

複数行データの複数列

ピボットターブルに近いが、値が決まっていない場合の行の列への変換。

peopleテーブル    telテーブル
| id | name |    | id | people_id | tel           |
|----|------|    |----|-----------|---------------|
| 1  | hoge |    | 1  | 1         | 090-0000-1111 |
| 2  | fuga |    | 2  | 1         | 090-0000-2222 |
| 3  | foo  |    | 3  | 2         | 090-0000-3333 |
| 4  | bar  |    | 4  | 3         | 090-0000-4444 |

| id | tel1          | tel2          |
|----|---------------|---------------|
| 1  | 090-0000-1111 | 090-0000-2222 |
| 2  | 090-0000-3333 | null          |
| 3  | 090-0000-4444 | null          |
| 4  | null          | null          |

上記のように複数行を複数列にしたい。

考え方としては、階層テーブルの取得に近いと思われる。

SELECT *
FROM `cross_table` AS a
LEFT JOIN `cross_table` AS b ON a.括りオーナーコード = b.括りオーナーコード
limit 50

こんな感じで結合する。すると、重複するのが2行になってクロスに近い表示になる。ただし2個。ここからUNIQUEする？

括りオーナーコード	代表オーナーコード1	代表オーナーコード2
1	693	693
2	728	728
3	2676	2676
5	1357	1357
6	677	677
7	2675	2675
8	1187	1187
9	2231	2231
10	124	124
11	2486	2486
12	505	505
13	1677	1677
14	21	21
15	3567	3567
16	758	758
17	3167	3167
18	3404	3404
18	3404	6412
18	6412	3404
18	6412	6412
19	2295	2295
20	2968	2968
21	2771	2771
22	2414	2414
23	324	324
24	320	320
25	187	187
26	4043	4043
27	3613	3613
28	1853	1853
29	550	550
30	9	9
31	2639	2639
31	2639	6413
31	6413	2639
31	6413	6413
32	2946	2946
33	321	321
34	4262	4262
35	145	145
36	4270	4270
37	779	779
38	3383	3383
38	3383	6414
38	6414	3383
38	6414	6414
39	4076	4076
40	955	955
41	688	688
42	2011	2011

DISTINCTで列を指定して重複を除去するときれいになりそう。

SELECT DISTINCT a.括りオーナーコード, a.代表オーナーコード as 代表オーナーコード1 , b.代表オーナーコード as 代表オーナーコード2
FROM `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ` as a
left join `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ`  as b on a.括りオーナーコード = b.括りオーナーコード
limit 50
;

SELECT a.括りオーナーコード, a.代表オーナーコード, b.代表オーナーコード
FROM `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ` AS a
LEFT JOIN `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ` AS b ON a.括りオーナーコード = b.括りオーナーコード
GROUP BY a.括りオーナーコード, a.代表オーナーコード, b.代表オーナーコード
limit 50

これでうまくいく。

括りオーナーコード	代表オーナーコード	代表オーナーコード
1	693	693
2	728	728
3	2676	2676
5	1357	1357
6	677	677
7	2675	2675
8	1187	1187
9	2231	2231
10	124	124
11	2486	2486
12	505	505
13	1677	1677
14	21	21
15	3567	3567
16	758	758
17	3167	3167
18	3404	3404
18	3404	6412
18	6412	3404
18	6412	6412
19	2295	2295
20	2968	2968
21	2771	2771
22	2414	2414
23	324	324
24	320	320
25	187	187
26	4043	4043
27	3613	3613
28	1853	1853
29	550	550
30	9	9
31	2639	2639
31	2639	6413
31	6413	2639
31	6413	6413
32	2946	2946
33	321	321
34	4262	4262
35	145	145
36	4270	4270
37	779	779
38	3383	3383
38	3383	6414
38	6414	3383
38	6414	6414
39	4076	4076
40	955	955
41	688	688
42	2011	2011

ただ、この方法だと重複除去したい他の列を表示できない。工夫が必要。サブクエリを使うか、DISTINCTの他にGROUP BY+HAVING集約関数。ややこしい。

https://chatgpt.com/c/672b21a0-83f4-800b-8b29-afa1d0a57817

うまくいっていない。こういうデータが残って重複が残る。

31	2639	2639
31	2639	6413
31	6413	2639
31	6413	6413

GROUP BY HAVINGで<>で違うものを2列目に用意して、同じ場合は結合みたいな。だめ。2個以上あった場合に複数ヒットしてしまう。

括りオーナーの同一番号のところにグループの連番列を追加して、その連番1/2を使ってピボットテーブルにすればいける。

SELECT 
括りオーナーコード,
MAX(CASE WHEN rownum = 1 THEN `代表オーナーコード` END) AS 代表オーナーコード1,
MAX(CASE WHEN rownum = 2 THEN `代表オーナーコード` END) AS 代表オーナーコード2
FROM (
    SELECT 
`括り代表オーナーコード`, 
@rownum := IF(@prev_group_id = `括りオーナーコード`, @rownum + 1, 1) AS rownum, 
@prev_group_id := `括りオーナーコード` AS 括りオーナーコード,
`代表オーナーコード`
FROM `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ`, (SELECT @rownum:=0) hoge, (SELECT @prev_group_id:='') fuga  
ORDER BY 括りオーナーコード, 括り代表オーナーコード
) numbered
GROUP BY 括りオーナーコード;

括りオーナーコード	代表オーナーコード1	代表オーナーコード2
1	693	NULL
2	728	NULL
3	2676	NULL
5	1357	NULL
6	677	NULL
7	2675	NULL
8	1187	NULL
9	2231	NULL
10	124	NULL
11	2486	NULL
12	505	NULL
13	1677	NULL
14	21	NULL
15	3567	NULL
16	758	NULL
17	3167	NULL
18	6412	3404
19	2295	NULL
20	2968	NULL
21	2771	NULL
22	2414	NULL
23	324	NULL
24	320	NULL
25	187	NULL
26	4043	NULL

成功。

MySQL

SQL

カラム名検索

MySQLで指定されたカラム名を持つテーブルを検索する #MySQL - Qiita

select table_name, column_name from information_schema.columns where column_name = '検索したいカラム名' and table_schema = '検索対象のデータベース名';

Type

About

MySQL :: MySQL 8.0 リファレンスマニュアル :: 11 データ型

データ型の書式がある。

DECIMAL(M [, D]): M=全体の桁数。D=内小数部の桁数。
CHAR(M)/VARCHAR(M): M=格納最大文字数。CHARのM=0-255までで末尾はスペース。VARCHARはM=0-65535までで、可変長文字列で256以上もOK。末尾のスペースは削除される。先頭にプレフィクスの1-2バイトのデータを付与している。255バイト以下の文字列なら1バイト、256バイト以上なら2バイト追加。

AUTO_INCREMENT

0やNULLがカラムに設定される場合、DBで持っている増分値を使ってくれる設定。

AUTO_INCREMENTのカラムを2以上定義しようとすると以下のエラーになる。

there can be only one auto column and it must be defined as a key

AUTO_INCREMENT属性は、PRIMARY KEYかUNIQUE KEYがないとつけられないらしい。

MySQLでAUTO_INCREMENTが指定されているカラムに「0」を入れるには

[NO_AUTO_VALUE_ON_ZERO] を指定しておくと、0の場合。増分値を使わない。

Cast

暗黙の型変換がある。

INSERTでは、式のデータ型とカラムのデータ型が不一致の場合にも型変換が発生する。

文字列から数値への変換では、文字列の先頭から有効な数字部分までを数値とみなす。

ただ、'00120'のような0始まりの数字も先頭の0を除去して数値とみなしてくれる。この根拠は見つけられなかった。

数値型の属性

MySQLは独自拡張で数値系のデータ型にint(9)のように表示幅を指定できる。DB上の表示幅なだけで実データには影響ない。それに、MySQL 8.0.17からdeprecated/廃止予定扱い。使っちゃダメ。

データ型で幅を指定しない場合、デフォルトの幅が内部的に存在する模様。

この幅は、その数値型の最小・最大値の符号を除去した桁数。

phpmyadminなど一部のクライアントがこの幅を勝手に使うので注意する。

BOOLEAN

https://chatgpt.com/c/6796f621-7264-800b-9a07-4c04fc8ae63b

MySQLのBOOLEANは内部的にTINYINT(1)に変換される。

BOOLEAN自体はSQL:1999で標準化されているが、対応状況はまちまち。

MySQL: TINYINT(1)として内部的に処理。
PostgreSQL: 完全対応。
SQLite: 内部的にINTEGER。
SQL Server: 未対応。
Oracle: 未対応。

Backup

MySQLのデータのバックアップには2種類の方法がある。

mysqldump
データファイルのコピー

基本的にはmysqldumpコマンドを使ってデータをSQLのテキストファイルとして出力する。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

全データベース、データベース、テーブル単位のそれぞれでバックアップできる。

全データベースのバックアップには--all-databasesを指定する。

mysqldump -p -u <username> --all-databases >mysql.sql
mysqldump -p -u <username> <db_name> [table_name ...]

復元はmysqlコマンドでSQLを読み込めばいい。

mysql -p -u username <mysql.sql
mysql -p -u username database_name <file.sql

Error

Error: [PDOException] PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]

MySQL8.0以降で接続できない場合は「認証プラグイン」を変更する
phpからMySQL 8.0へPDOで接続時「SQLSTATE[HY000 [2054] The server requested authentication method unknown to the client」 - Symfoware]
日々の覚書: MySQL 8.0.4におけるデフォルト認証形式の変更
docker-compose MySQL8.0 のDBコンテナを作成する #Docker - Qiita

MySQL 8.0になってから、認証方式が変わったらしく、そのままだと接続に失敗する。

PHP 7.1.16, 7.2.4 以降から対応しているらしい。それ以前だと設定が必要。

Numeric value out of range: 1264 Out of range value for column

"Out of range value for column 'カラム名' at 行番号" に対処した #MySQL - Qiita

エラーコード 1264 は、データ型の範囲を超えた値をINSERTしたときに発生するWarningである。直前に実行したSQLのWarningは SHOW WARNINGS で確認できる。

Config

Character

MySQLの文字コードとCollation #MySQL - Qiita

DBで使用する文字セットと、その照合順序を選択できる。

文字コードはutf8mb4で問題ない。照合順序も基本はutf8mb4_binで問題ないと思う。

A ≠ a 区別する

≠ 区別する

は≠ぱ≠ば区別する

utf8mb4_general_ciは上2個を区別しない。

区別しないとあいまい検索で都合がいいことがある。

ただし、あいまい検索にすると、対象範囲が増えるので、少し遅くなる模様 (CharsetとCollationの設定がMySQLのパフォーマンスに与える影響 | Yakst)。

SQL mode

MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.11 サーバー SQL モード

SQLの基本的な動きを制御する設定がある。以下で現在値を確認。

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

以下で設定。

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

GLOBALは権限が必要。SESSIONは誰でも変更可能。

デフォルト=ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO および NO_ENGINE_SUBSTITUTION。

カンマで区切って複数指定できる。

モードの値としては、ANSIが重要。基本はこれでよい。

Collation

【MySQL】照合順序とは？ #MySQL - Qiita

MySQL :: MySQL 8.4 Reference Manual :: 12.3.1 Collation Naming Conventions

utf8_general_ciのようなcollate (照合順序) がある。これの意味。

書式 [文字集合]_[言語名]_[比較法]

文字集合: character set
言語名: general=マルチリンガル。0900=UCA 9.0.0。bin=バイナリ比較。バイト単位で比較する。大文字小文字やアクセントの違いなどを考慮する。
比較法: ai=アクセント無視 (はとぱは同一)/as=アクセント区別/ci=大文字小文字無視 (case ignore)/cs=大文字小文字区別 (case sensitive)/ks=カナ区別/bin=バイナリー。

default

mysqlのデフォルトはutf8_general_ci。

general:
unicode: 全角半角を区別しない。

絵文字の扱いなどが変わってくる。

MySQL 8.0=utf8mb4/utf8mb4_0900_ai_ci (MySQL :: MySQL 8.0 Reference Manual :: 12.3.2 Server Character Set and Collation/MySQL :: MySQL 8.0 Reference Manual :: 12.2 Character Sets and Collations in MySQL)
MySQL 5.7=latin1/latin1_swedish_ci (MySQL :: MySQL 5.7 Reference Manual :: 10.3.2 Server Character Set and Collation)
MySQL 5.7=utf8mb4/utf8mb4_general_ci (MySQL :: MySQL 5.7 Reference Manual :: 10.2 Character Sets and Collations in MySQL)

utf8mb4_bin/utf8mb4_0900_bin

utf8mb4_0900_binなどの0900はMySQL 8.0から使用可能。0900はUnicode 9.0の意味。MySQL専用でMariaDBにはない。

utf8mb4_binは終端スペースを除去する。0900は除去しない。

utf8mb4_binは照合時に先頭に0を追加する場合があるが、0900はそれがなく、0900のほうが並べ替えがはるかに高速。らしい。

基本はutf8mb4_0900_binを使うとよさそう。

language

MySQL :: MySQL 8.0 Reference Manual :: 12.10.1 Unicode Character Sets

generalとかunicodeとかの言語名部分の違いが上記に記載がある。

なお、日本語の異体字はDBでは吸収できない。アプリ側で対応必要 (MySQLでの異体字対応検索 - まぁそれなりにねっと)。

設定・変更

MariaDBのDBの文字コードと照合順の変更 | GNU social JP Web

指定したDBの文字コード・照合順の情報は以下のコマンド・SQLで確認可能です。

DB_USER=root
DB_NAME=mysql
mysql -u $DB_USER -p -e \
'SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA;'

+--------------------+----------------------------+------------------------+
| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8                       | utf8_general_ci        |
| performance_schema | utf8                       | utf8_general_ci        |
| mysql              | utf8mb4                    | utf8mb4_general_ci     |
+--------------------+----------------------------+------------------------+

設定は以下で行えます。

DB_USER=root
DB_NAME=mysql
## MariaDB
mysql -u $DB_USER -p -e \
 "ALTER DATABASE \`$DB_NAME\` DEFAULT CHARACTER SET 'utf8mb4' DEFAULT COLLATE 'utf8mb4_bin';"
## MySQL
mysql -u $DB_USER -p -e \
  "ALTER DATABASE $DB_NAME DEFAULT CHARACTER SET 'utf8mb4' DEFAULT COLLATE 'utf8mb4_0900_bin';"

Other

データベース一覧

show databases;

テーブルの検索

https://chatgpt.com/c/6789de8b-11ac-800b-bb21-45ba3735aa3f

SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_NAME LIKE '%検索するテーブル名%'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

information_schemaから検索できる。

testデータベース

https://chatgpt.com/c/67b686cc-fdc0-800b-9538-03d86cf046de

MySQL 5.7までMySQLインストール時に、自動的にtestデータベースが作られる。MySQL 8.0からは自動では作られない。

テスト用のデータベースで全ユーザーがアクセス可能。

本番では削除が推奨される。phpmyadminなどのツールのデータ保存用に使うと良い。

予約語

MySQLは組込関数がたくさんあり、それらが全て予約語。以下でも確認できる。

select * from information_schema.KEYWORDS;

予約語をカラム名などに使う場合、バッククオートかANSIモードで二重引用符でエスケープが必要。

ただ、だからといって全部にエスケープすると可読性が悪い。変数や予約語だけエスケープするのがいい模様。

rank

Oracle

第I部 Oracleリレーショナル・データ構造

2 表と表クラスタ

表と表クラスタ

2.2. 表の概要

2.2.4. Oracleデータ型

数値データ型

NUMBER
浮動小数点数

NUMBERデータ型

固定小数点数と浮動小数点数を格納可能。どんな大きさの数値でも格納可能。

固定小数点数は NUMBER[(<p>[, <s>])] の形式で指定。

p=精度: 全体の桁数。精度を指定しない場合、値は丸めずに格納。
s=scale: 小数点から最下位有効桁までの桁数。省略すると0になる。

https://grok.com/share/c2hhcmQtMw%3D%3D_68537ced-0913-4a0c-a788-df06a1afb8ec

NUMBER型で精度を省略すると最大38桁の任意精度の数値を格納する。

データの範囲や精度が事前に不明な場合、事前に決められない場合、将来の変化の可能性が高い場合。

基本は精度を指定する。意図しないデータを抑制できる。

6 データ・ディクショナリと動的パフォーマンス・ビュー

データ・ディクショナリと動的パフォーマンス・ビュー

About

Oracle Databaseの重要な部分。DBに関する管理メタデータの読取専用の表の集合。以下のような情報を含んでいる。

列のデフォルト値、整合性制約情報など、データベース内の各スキーマ・オブジェクトの定義
スキーマ・オブジェクトに割り当てられている領域、およびスキーマ・オブジェクトによって現在使用されている領域の容量
Oracle Databaseユーザーの名前、ユーザーに付与された権限とロール、およびユーザーに関連する監査情報

データが表に入っているため、SQLで閲覧可能。実表とビューで構成されており、実表はDBが内部的に扱うだけで、ユーザーはビューにアクセスするのが基本。

データ・ディクショナリーは、アクセス権限に応じて、以下の接頭辞で同じ内容のものがグループ化されている。

表6-1 データ・ディクショナリ・ビューのセット

接頭辞	ユーザー・アクセス	内容	ノート
`DBA_`	データベース管理者	すべてのオブジェクト	一部の`DBA_`ビューには、管理者にとって有用な情報を含む列が追加されています。
`ALL_`	すべてのユーザー	ユーザーが権限を持つオブジェクト	ユーザーが所有するオブジェクトが含まれています。これらのビューは有効化されている現在の一連のロールに従います。
`USER_`	すべてのユーザー	ユーザーによって所有されているオブジェクト	接頭辞が`USER_`のビューには、通常、列`OWNER`は含まれません。この列は、問合せを発行するユーザーとして、`USER_`ビューに暗黙的に含まれています。

必ず3セットあるわけではない。

システム提供のDICTIONARYビューに、全データ・ディクショナリ・ビューの名前と短い説明がある。

SELECT * FROM DICTIONARY  ORDER BY TABLE_NAME;

ALL_TAB_COLUMNS

Oracle DBでは、以下のデータディクショナリービューを参照することで、テーブル定義を確認できる。

テーブル名	スコープ
USER_TAB_COLUMNS	ログインユーザーのカラム一覧
ALL_TAB_COLUMNS	ログインユーザーがアクセスできるすべてのカラム一覧
DBA_TAB_COLUMNS	データベース内のすべてのカラム一覧

スコープが違うのみ。基本はALL_TAB_COLUMNSを使うことになる。

以下のSQLでテーブル定義を確認可能。

SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'SAMPLE_TABLE';

列に対して、その統計情報が以下で記載される。

列	データ型	NULL	説明
`OWNER`	`VARCHAR2(30)`	`NOT NULL`	表、ビューまたはクラスタの所有者
`TABLE_NAME`	`VARCHAR2(30)`	`NOT NULL`	表、ビューまたはクラスタの名前
`COLUMN_NAME`	`VARCHAR2(30)`	`NOT NULL`	列名
`DATA_TYPE`	`VARCHAR2(106)`		列のデータ型
`DATA_TYPE_MOD`	`VARCHAR2(3)`		列のデータ型修飾子
`DATA_TYPE_OWNER`	`VARCHAR2(30)`		列のデータ型の所有者
`DATA_LENGTH`	`NUMBER`	`NOT NULL`	列の長さ(バイト)
`DATA_PRECISION`	`NUMBER`		`NUMBER`データ型の場合は10進精度。`FLOAT`データ型の場合は2進精度。その他のデータ型の場合はNULL。
`DATA_SCALE`	`NUMBER`		数値の小数点以下の桁
`NULLABLE`	`VARCHAR2(1)`		列にNULLを指定できるかどうかを示す。列に`NOT NULL`制約がある場合、または列が`PRIMARY KEY`の一部である場合、値は`N`となる。この制約は、`ENABLE VALIDATE`状態である必要がある。
`COLUMN_ID`	`NUMBER`		作成された列の順序番号
`DEFAULT_LENGTH`	`NUMBER`		列のデフォルト値の長さ
`DATA_DEFAULT`	`LONG`		列のデフォルト値
`NUM_DISTINCT`	`NUMBER`		列内で異なる値の数^{脚注 1}
`LOW_VALUE`	`RAW(32)`		列内の下限値^{脚注 1}
`HIGH_VALUE`	`RAW(32)`		列内の上限値^{脚注 1}
`DENSITY`	`NUMBER`		`COLUMN_NAME`に対してヒストグラムが使用可能な場合、この列にはヒストグラム内の1つ以下のエンドポイントにわたる値の選択性が示される。複数のエンドポイントにわたる値の選択性は示されない。 `COLUMN_NAME`に対してヒストグラムが使用可能ではない場合、この列の値は`1`/`NUM_DISTINCT`である。^{脚注 1}
`NUM_NULLS`	`NUMBER`		列内のNULLの数
`NUM_BUCKETS`	`NUMBER`		列のヒストグラム内のバケット数注意: ヒストグラム内のバケット数は、SQL文`ANALYZE`の`SIZE`パラメータに指定されます。ただし、サンプル内の行数より多いバケットを持つヒストグラムは作成されません。また、サンプルに繰返しが非常に多い値が含まれる場合、指定された数のバケットは作成されますが、この列で指定した値は、内部圧縮アルゴリズムのために小さくなる場合があります。
`LAST_ANALYZED`	`DATE`		この列が分析された最新の日付
`SAMPLE_SIZE`	`NUMBER`		この列の分析で使用されたサンプル・サイズ
`CHARACTER_SET_NAME`	`VARCHAR2(44)`		キャラクタ・セットの名前 `CHAR_CS` `NCHAR_CS`
`CHAR_COL_DECL_LENGTH`	`NUMBER`		キャラクタ・タイプ列の宣言の長さ
`GLOBAL_STATS`	`VARCHAR2(3)`		パーティション表の場合、表全体を収集した列統計情報なのか(`YES`)、基礎となるパーティションおよびサブパーティションの統計情報から推定されたものなのか(`NO`)
`USER_STATS`	`VARCHAR2(3)`		統計情報が、ユーザーによって直接入力されたか(`YES`)されていないか(`NO`)
`AVG_COL_LEN`	`NUMBER`		列の平均の長さ(バイト)
`CHAR_LENGTH`	`NUMBER`		列の長さが文字単位で表示されます。この値は、次のデータ型のみに適用される。 `CHAR` `VARCHAR2` `NCHAR` `NVARCHAR2`
`CHAR_USED`	`VARCHAR2(1)`		列がバイトの長さセマンティクス(`B`)を使用するか、文字の長さセマンティクス(`C`)を使用するか、またはデータ型が次のいずれでもない(NULL)ことを示す `CHAR` `VARCHAR2` `NCHAR` `NVARCHAR2`
`V80_FMT_IMAGE`	`VARCHAR2(3)`		列データがリリース8.0のイメージ形式であるかどうか(`YES` \| `NO`)
`DATA_UPGRADED`	`VARCHAR2(3)`		列データが最新のタイプ・バージョン形式にアップグレードされたかどうか(`YES` \| `NO`)
`HISTOGRAM`	`VARCHAR2(15)`		ヒストグラムの有無およびタイプ: `NONE` `FREQUENCY` `HEIGHT BALANCED`

データベース・リファレンス

6 静的データ・ディクショナリ・ビュー

制約

ALL_CONSTRAINTS

表の制約定義を確認する。

SELECT * FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = 'T_MEMBERS_DCP_ACTION';

主キーとか一意キーの有無を確認できる。

特に以下が重要。

CONSTRAINT_NAME
CONSTRAINT_TYPE
INDEX_NAME

列	データ型	説明
`OWNER`	`VARCHAR2(128)`	制約定義の所有者
`CONSTRAINT_NAME`	`VARCHAR2(128)`	制約定義の名前
`CONSTRAINT_TYPE`	`VARCHAR2(1)`	制約定義のタイプ `C` - 表でのチェック制約 `P` - 主キー `U` - 一意のキー `R` - 参照整合性 `V` - ビューでのチェック・オプション付き `O` - ビューで読取り専用 `H` - ハッシュ式 `F` - REF列を含む制約 `S` - サプリメンタル・ロギング
`TABLE_NAME`	`VARCHAR2(128)`	制約定義付きの表(またはビュー)に対応付けられた名前
`SEARCH_CONDITION`	`LONG`	チェック制約に対する検索条件のテキスト。この列は、行の発生元が現在のコンテナの場合のみ正しい値を返す。
`SEARCH_CONDITION_VC`	`VARCHAR2(4000)`	チェック制約に対する検索条件のテキスト。この列では検索条件が切り捨てられる場合がある。
`R_OWNER`	`VARCHAR2(128)`	参照制約で参照される表の所有者
`R_CONSTRAINT_NAME`	`VARCHAR2(128)`	参照表の一意制約の定義名
`DELETE_RULE`	`VARCHAR2(9)`	参照制約の削除ルール `CASCADE` `SET NULL` `NO ACTION`
`STATUS`	`VARCHAR2(8)`	制約の施行状態 `ENABLED` `DISABLED`
`DEFERRABLE`	`VARCHAR2(14)`	制約が遅延可能か(`DEFERRABLE`)そうでないか(`NOT DEFERRABLE`)
`DEFERRED`	`VARCHAR2(9)`	制約が初期状態から遅延されていたか(`DEFERRED`)そうでないか(`IMMEDIATE`)
`VALIDATED`	`VARCHAR2(13)`	`STATUS` = `ENABLED`の場合、可能な値は次のとおり。 `VALIDATED` - すべてのデータは制約に従っている(つまり、制約が有効にされたとき、および後続のデータが表に入力されたときに表内の既存のデータが検証されている) `NOT` `VALIDATED` - 制約に従っていないデータがある可能性がある(つまり、制約が有効にされたときに表内の既存のデータは検証されなかったが、表に入力された後続のデータは検証されている) `STATUS` = `DISABLED`の場合、可能な値は次のとおり。 `VALIDATED`: すべてのデータは制約に従っているが、制約の一意索引は削除されている。この設定は、データ・ウェアハウス環境において有用であるが、いくつかの制限事項がある。この設定の詳細は、を参照。 `NOT` `VALIDATED` - 制約に従っていないデータがある可能性がある
`GENERATED`	`VARCHAR2(14)`	制約の名前がユーザーにより生成されたか(`USER NAME`)システムにより生成されたか(`GENERATED NAME`)
`BAD`	`VARCHAR2(3)`	この制約があいまいな方法で世紀を指定するか(`BAD`)そうでないか(NULL)。この不明瞭さから生じるエラーを回避するには、`TO_DATE`ファンクションで4桁の年を使用して制約を再度書き込みます。関連項目: `TO_DATE`ファンクションは、およびを参照。
`RELY`	`VARCHAR2(4)`	`VALIDATED` = `NOT` `VALIDATED`の場合、この列は、クエリー・リライトのときに制約が考慮されるか(`RELY`)されないか(NULL)を示す。 `VALIDATED` = `VALIDATED`の場合、この列は意味を持たない。関連項目: の制約に関する項を参照。
`LAST_CHANGE`	`DATE`	制約が最後に使用可能または使用禁止にされた時点
`INDEX_OWNER`	`VARCHAR2(128)`	索引を所有しているユーザーの名前
`INDEX_NAME`	`VARCHAR2(128)`	索引の名前(一意および主キー制約の場合のみ表示)
`INVALID`	`VARCHAR2(7)`	制約が無効か(`INVALID`)そうでないか(NULL)
`VIEW_RELATED`	`VARCHAR2(14)`	制約がビューによって異なるか(`DEPEND ON VIEW`)そうでないか(NULL)
`ORIGIN_CON_ID`	`VARCHAR2(256)`	データの発生元のコンテナのID。可能な値は次のとおり。 `0`: この値は、非CDB内の行に使用される。この値はCDBには使用されない。 n: この値は、コンテナID n (行の発生元がルートの場合はn = 1)のコンテナを発生元とするデータを含む行に対して使用される。

ALL_CONS_COLUMNS

制約の指定列を確認する。

SELECT *
FROM ALL_CONS_COLUMNS
WHERE TABLE_NAME = 'T_MEMBERS_DCP_ACTION';

権限

特定テーブルへの権限の確認方法

https://grok.com/share/c2hhcmQtMw%3D%3D_4f7d1813-7b1c-497e-975a-63042de54f40

テーブルとロールで権限が付与されている。両方確認が必要。

-- ロールの権限
SELECT * FROM USER_SYS_PRIVS;

-- テーブル単位の権限
SELECT privilege
FROM dba_tab_privs
WHERE grantee = USER
AND table_name = 'テーブル名';

SYS_PRIVS

ユーザー・ロールに付与されたシステム権限。

システム権限付与を確認できる。

SELECT * FROM USER_SYS_PRIVS;

USERNAME	PRIVILEGE	ADMIN_OPTION	COMMON	INHERITED
WEB	EXECUTE ANY PROCEDURE	NO	NO	NO
WEB	ALTER SESSION	NO	NO	NO
WEB	SELECT ANY DICTIONARY	NO	NO	NO
WEB	CREATE MATERIALIZED VIEW	NO	NO	NO
WEB	CREATE PROCEDURE	NO	NO	NO
WEB	CREATE TABLE	NO	NO	NO
WEB	CREATE SESSION	NO	NO	NO
WEB	CREATE TRIGGER	NO	NO	NO
WEB	SELECT ANY TABLE	NO	NO	NO

TAB_PRIVS

データベース内のすべてのオブジェクトについての権限付与を示します。

列	データ型	説明
`GRANTEE`	`VARCHAR2(128)`	アクセス権を付与されたユーザーまたはロールの名前
`OWNER`	`VARCHAR2(128)`	オブジェクトの所有者
`TABLE_NAME`	`VARCHAR2(128)`	オブジェクト名。オブジェクトには、表、パッケージ、索引、順序など、任意のオブジェクトを設定できる。
`GRANTOR`	`VARCHAR2(128)`	権限付与を実行したユーザー名
`PRIVILEGE`	`VARCHAR2(40)`	オブジェクトについての権限
`GRANTABLE`	`VARCHAR2(3)`	権限が`GRANT OPTION`付きで付与されたか(`YES`)されていないか(`NO`)
`HIERARCHY`	`VARCHAR2(3)`	権限が`HIERARCHY OPTION`付きで付与されたか(`YES`)されていないか(`NO`)
`COMMON`	`VARCHAR2(3)`	権限がどのように付与されたかを示します。可能な値は次のとおり。 `YES`: 権限が共通して付与された場合(`CONTAINER=ALL`が使用された場合) `NO`: 権限がローカルで付与された場合(`CONTAINER=ALL`が使用されなかった場合)
`TYPE`	`VARCHAR2(24)`	オブジェクトのタイプ
`INHERITED`	`VARCHAR2(3)`	権限付与が別のコンテナから継承されているかどうか(`YES` \| `NO`)

SQL言語リファレンス

11 エラー処理

PL/SQLのエラー処理

11.2 例外処理の概要

すべてのPL/SQLブロックに例外処理部を配置でき、ここに1以上の例外ハンドラーを配置できる。例えば、以下の構文を使用する。

EXCEPTION
  WHEN ex_name_1 THEN statements_1                 -- Exception handler
  WHEN ex_name_2 OR ex_name_3 THEN statements_2  -- Exception handler
  WHEN OTHERS THEN statements_3                      -- Exception handler
END;

ex_name_nが例外の名前。statements_nは1以上の文。ブロックの実行部で例外が発生すると、実行部が中止し、例外処理部に制御が移る。

それ以外はstatements_3が実行される。

11.10 エラー・コードとエラー・メッセージの取得

例外ハンドラ内で、処理中の例外について以下ができる。

SQLCODEを使用してエラー・コードを取得。
以下のどちらからでエラー・メッセージを取得。
- SQLERRM
- DBMS_UTILITY.FORMAT_ERROR_STACK

例11-23 SQLCODEおよびSQLERRMの値の表示

DROP TABLE errors;
CREATE TABLE errors (
  code      NUMBER,
  message   VARCHAR2(64)
);

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
  name    EMPLOYEES.LAST_NAME%TYPE;
  v_code  NUMBER;
  v_errm  VARCHAR2(64);
BEGIN
  SELECT last_name INTO name
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = -1;
EXCEPTION
  WHEN OTHERS THEN
    v_code := SQLCODE;
    v_errm := SUBSTR(SQLERRM, 1, 64);
    DBMS_OUTPUT.PUT_LINE
      ('Error code ' || v_code || ': ' || v_errm);
 
    /* Invoke another procedure,
       declared with PRAGMA AUTONOMOUS_TRANSACTION,
       to insert information about errors. */
 
    INSERT INTO errors (code, message)
    VALUES (v_code, v_errm);

    RAISE;
END;

DDL

ALTER TABLE

Other

バージョン確認

SELECT * FROM V$VERSION

BANNER,BANNER_FULL,BANNER_LEGACY,CON_ID
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production	"Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.21.0.0.0"	Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production	0

LIMIT

PostgreSQLやMySQLで使えるLimit句をOracle SQLで使う方法 | 株式会社アースリンク

oracleではlimitがない。

SELECT * FROM Test_Emp LIMIT 5;

SELECT * FROM Test_Emp WHERE ROWNUM <= 5;

rownumの疑似列を使う。

ただしこのROWNUMはORDER BY句で正しくソートすることができません。

ROWNUMで取得した結果に対してソートを行うので、

LIMIT句の様にソートしてからの取得ができないからです。

ROWNUM擬似列で行数制限しながらソートしたい場合

なので、結果に対してソートを行いたい場合、

副問合せをしてその中でORDER BY句を使用します。

SELECT * FROM
(SELECT * FROM Test_Emp ORDER BY Emp_No DESC)
WHERE ROWNUM <= 5

データベースPL/SQL言語リファレンス

6 PL/SQLの静的SQL

PL/SQLの静的SQL

6.2 カーソルの概要

カーソルは特定のSELECT文などの処理の情報を格納しておくポインター。

SELECTの結果を格納しておいて、1件ずつ処理するときに使う。配列みたいなもの。

暗黙のカーソルと明示カーソルがある。

6.2.1 暗黙カーソル

SELECT文を実行すると自動的に作られるカーソル。SQLカーソルとも呼ばれる。文の実行後に自動的にクローズされる。

6.2.2 明示カーソル

ユーザーが明示的に構築・管理するカーソル。明示カーソルを宣言・定義して、その後にSELECT文と関連付けて使う。

OPEN文で明示カーソルオープンして、FETCH文でフェッチして、CLOSE文でクローズする。

明示カーソルの宣言は以下。

CURSOR cursor_name [ parameter_list ] RETURN return_type;

宣言+定義は以下。

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
  IS select_statement;

PL/SQLパッケージおよびタイプ・リファレンス

出力

DBMS_OUTPUT

デバッグ用パッケージ。

DBMS_OUTPUT.PUT_LINE('I got here:'||:new.col||' is the new value');

SQL上でのechoコマンド相当。

JSON Developer's Guide

第V部 JSONデータの生成

23 SQL/JSONファンクションを使用したJSONデータの生成

いくつか方法がある。

方式
- OBJECT: JSONオブジェクトを作成。
- ARRAY: JSON配列を作成。
- AGG: SQLの副問い合わせの結果から作成。AGGを使わない場合、プロパティーは引数に依存。

関数
- JSON_OBJECT
- JSON_OBJECTAGG
- JSON_ARRAY
- JSON_ARRAYAGG

JSON_OBJECT

2種類の書き方がある。

JSON_OBJECT('key1' VALUE 'value1', 'key2' VALUE 'value2', ...)
JSON_OBJECT('key1':'value1', 'key2':'value2', ...)

keyの部分は文字列で固定。valueの部分は型は柔軟。

SELECT json_object('name'          VALUE first_name || ' ' || last_name,
                   'hasCommission' VALUE
                     CASE WHEN commission_pct IS NULL THEN 'false' ELSE 'true'
                     END FORMAT JSON)
  FROM employees WHERE first_name LIKE 'W%';

JSON_OBJECT('NAME'ISFIRST_NAME||''||LAST_NAME,'
-----------------------------------------------
{"name":"William Gietz","hasCommission":false}
{"name":"William Smith","hasCommission":true}
{"name":"Winston Taylor","hasCommission":false}

SELECT json_object('id'          : employee_id,
                   'name'        : first_name || ' ' || last_name,

                   'contactInfo' : json_object('mail'  : email,
                                               'phone' : phone_number),
                   'hireDate'    : hire_date,
                   'pay'         : salary)
  FROM hr.employees
  WHERE salary > 15000;

-- The query returns rows such as this (pretty-printed here for clarity):

{"id"          : 101,
 "name"        : "Neena Kochhar",
 "contactInfo" : {"mail"  : "NKOCHHAR",
                  "phone" : "515.123.4568"},
 "hireDate"    : "21-SEP-05",
 "pay"         : 17000}

JSON_OBJECTAGG

SQLの結果からJSONをまとめて作りたいときとかに使う。

書式が2個ある。

JSON_OBJECTAGG([KEY] <key> VALUE <value>)
JSON_OBJECTAGG(<key>, <value>)

公式文書では見つけられなかったが、単に,区切りも許容される模様。こっちのほうがシンプル。

ｋ

SELECT json_objectagg(department_name VALUE department_id) FROM departments;

-- The returned object is pretty-printed here for clarity.
-- The order of the object members is arbitrary.

JSON_OBJECTAGG(DEPARTMENT_NAMEISDEPARTMENT_ID)
----------------------------------------------
{"Administration":        10,
 "Marketing":             20,
 "Purchasing":            30,
+                SELECT
+                    JSON_OBJECTAGG(
+                        CASE CS.SPECCODE
+                            WHEN 68   THEN '重量'
+                            WHEN 411  THEN 'LAN'
+                            WHEN 413  THEN '標準キーボード'
+                            WHEN 418  THEN '電源'
+                            WHEN 782  THEN '入出力ポート'
+                            WHEN 5124 THEN 'SSD2'
+                            WHEN 5209 THEN 'Bluetooth'
+                            WHEN 5295 THEN 'CPUグリス'
+                            WHEN 5688 THEN '無線LAN'
+                            WHEN 5701 THEN 'WEBカメラ'
+                            WHEN 6602 THEN 'バッテリー'
+                        END, COALESCE(CS.CUSTOMNAME_SIMPLE, CS.CUSTOMNAME)
+                    )
+                FROM ISOP7.M_ASM_CUSTOM_S CS
+                WHERE
+                    CS.DELFLG = 0 AND
+                    CS.SORTNO = 1 AND
+                    CS.SPECCODE IN (68, 411, 413, 418, 782, 5124, 5209, 5295, 5688, 5701, 6602)
+                    AND CS.MODELCODE = VUV.MODELCODE AND CS.SUBNO = VUV.SUBNO

END, COALESCEのところをEND : COALESCEにしたらJSON_OBJECTの形式になる。

Other

Manual

マニュアル | Oracle 日本
Oracle Database 19c - スタート・ガイド
Oracle Database 19c - ブック: ここに全マニュアルが一覧されている模様。

内容別にマニュアルが細分化されている。

目次: 概要とか基本的な概念の話。[Oracle Databaseの概要]/[SQLの概要] あたりにだいたいの記載がある。
目次: システムテーブルカラムとか。
目次: データベースPL/SQL言語リファレンス
目次: PL/SQLパッケージおよびタイプ・リファレンス (SQLの構文外のOracle独自のパッケージ・ライブラリー・関数群)
目次: SQL言語リファレンス

TNSNAMES.ORA

ローカル・ネーミング・パラメータ(tnsnames.ora)

Oracle DBのDBへの接続情報を記載した設定ファイル。

TNS=Transparent Network Substrate

OracleのDNS相当。

DBの接続情報を記載した=の左側の識別子。DNSの項目名みたいなもの。

デフォルトでORACLE_HOME/network/adminディレクトリーに配置されている。以下の順序で検索する。

環境変数TNS_ADMIN

ORACLE_HOME/network/admin (例: C:\oracle\product\10.2.0\client_1\network\ADMIN)

1個の項目で最低限必要なもの。

<net service name>=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <HOST>)(PORT = <PORT>))
    (CONNECT_DATA =
      (SERVICE_NAME = <service name>)
    )
  )

省略可能な項目が少ない。

SERVICE_NAMEは少々厄介。以下の方法で確認する。

データベースに接続後、SQL実行 (SELECT name FROM v$active_services;)
lsnrctl servicesコマンド実行

Owner/Schema/User

https://grok.com/share/c2hhcmQtMw%3D%3D_5d0fd582-b217-4615-9cce-4bbc3311d256

Oracle database特有の概念、用語がある。

Oracle Databaseは他のRDBに比べて、エンタープライズ向けの設計が多い。

Owner: DB内のオブジェクトの所有者。オブジェクトの作成者がそのオブジェクトのownerになって所有権を持つ。他のユーザーがそのテーブルにアクセスするには、権限の付与が必要。

セキュリティーと名前空間の管理を厳格に扱うため、オブジェ区ごととに明確な所有者を定義している。これにより、複数ユーザーが同じDBで作業しても衝突を防げる。

このOwnerがSchemaと密接な関係になっていてややこしい。

SchemaはMySQLだとDBのこと。PostgreSQLだと名前空間相当。

Oracleの場合、スキーマは、ユーザーが所有するオブジェクトの集合 (名前空間)。スキーマ名はユーザー名と同じ。ユーザーを作成すると同名のスキーマも作成される。オブジェクトを論理的にグループ化するためのもの。

例: ユーザーSCOTTがテーブルEMPを作成すると、テーブルはSCOTTスキーマに属し、フルネームはSCOTT.EMPになる。

Ownerはスキーマ内のオブジェクトの所有者で、基本的にスキーマのユーザー自身。つまり、User = Schema Owner。UserとSchema/Ownerが連動している。

日付処理

https://grok.com/share/c2hhcmQtMw%3D%3D_dd5722c0-b2d0-43f5-83e9-655ca84c8b8d

TRUNC(数値)

DATE型のカラムに対して、日付で絞り込みたい場合、TRUNK関数で、小数点以下 (時刻部分) を切り捨てて、日付部分にして比較する。

SELECT *
FROM your_table
WHERE TRUNC(created_date) = TRUNC(SYSDATE);

範囲指定の場合。

SELECT *
FROM your_table
WHERE created_date >= TRUNC(SYSDATE)
  AND created_date < TRUNC(SYSDATE) + 1;

トランザクション処理

Oracle DatabaseのトランザクションはBEGINがいらない #SQL - Qiita

Oracle Databaseは常にトランザクションが有効。なので、BEGINは省略可能。COMMITが毎回必要。

Client

OSqlEdit

Oracle DBの軽量クライアント。

手順書には記載がないのですが、追加でOSqlEditのインストールもお願いします。

https://code73.mydns.jp/a_ogawa/osqledit/index.htm

ライセンスは未取得のため、お手数ですが起動時の「シェアウェアです」の警告は無視して進めてください。

これの扱いがよくわからない。

PHP環境でのDB操作は、phpMyAdminよりもAdminerをおすすめします | やってみルカ

Adminerを利用してたった1つのPHPファイルで簡単にデータベースを管理する方法

OSqlEditより、Adminerでいいかも。phpMyAdminはMySQL/MariaDBだけだけど、AdminerはDBならなんでも対応している。

Zipファイルを解凍して、[osqledit.exe] を実行すると起動できる模様。ただ、起動すると真っ先に、 [OCIライブラリの初期化に失敗しました] と表示される。

Windows10にOsqlEdit(64bit版)をインストールして接続できるようにする手順 - そういうのがいいブログ

Windows10(64bit)にOracle Instant ClientをインストールしSQL*Plusを使えるようにする手順 - そういうのがいいブログ

OsqlEditを使うのに、Oracle Instant Clientが必要とのこと。

[Oracle Instant Client] はOracle社が提供する、無料のDB接続ツール。ダウンロードして環境変数をちょっと設定するだけで使えるようになる。

[Oracle Instant Client - Oracle Databaseに接続するための無償のツールとライブラリ | Oracle 日本]-[今すぐダウンロード]-[Instant Client for Microsoft Windows (x64)] を選ぶ。

以下のファイルを保存する (Basic Light Package とPrecompiler Package以外)。

Basic Package

SQL*Plus Package

Tools Package

SDK Package

JDBC Supplement Package

ODBC Package

これらを全部展開する。instantclient_23_8のようなディレクトリーに全部展開される。

これを任意の場所に配置する。例えば、C:\oracle\instantclient_23_8 になる。

以下の環境変数を設定する。

:: NLS_LANG=Japanese_Japan.JA16SJISTILDE 
:: TNS_ADMIN=C:\oracle\instantclient_23_8 
Path=%TNS_ADMIN%

Pathだけ最低設定していればよさそう。

Pathの設定をすると、osqleditの起動時のエラーが解消される。メッセージが以下になる。

OSqlEditはシェアウェアです。 
継続して利用される場合、ライセンスをご購入下さい。

いったん無視してOK。Login画面が表示される。

Client

General

複数対応

https://grok.com/share/c2hhcmQtMw%3D%3D_c8665fa7-fa85-4db2-89d3-b18c521ff38b

複数DBに対応したクライアントがほしい。特に、Oracle database

Adminer: テーブルを見るだけ。oracleのpackageとかはSQLで手動。
DBeaver: 本命。
OmniDB: 開発停止。

DBeaverがよさそう。

phpMyAdmin

Install

phpMyAdmin configuration storage

Installation — phpMyAdmin 5.2.3-dev documentation

phpmyadmin 3.4.0未満までLinked Tables Infrastructureと呼んでいた、configuration storageという機能がある。この機能を有効にすることで、ブックマークやコメントなどの機能が使えるようになる。

2種類の設定方法がある。

Zero configuration
Manual configuration

それぞれの方法を検討する限り、結局保存用のテーブルが必要なので、Zero configurationでtestデータベースに作るのがいいだろう。

Zero configuration

Zero configurationは多くの場合に自動構成されるもの。特に共有ホスティングで役立つ。デフォルトオンになっている。

以下の3のシナリオをカバーしている。

configuration storageテーブル不在で、[Operation] タブにアクセスすると、作成を提案する ([The phpMyAdmin configuration storage has been deactivated. Find out why.] の表示)。このリンク[Find out why]-[Create the phpMyAdmin configuration storage in the current database.]の[Create]をクリックして作る。
configuration storageテーブルがすでに存在するDBの場合、自動的に検知して使う。ユーザーが1DBにしかアクセスできない共有ホスティングで役立つ。
複数のDBにユーザーがアクセス可能な場合、configuration storageテーブルを含むDBにアクセスしたら、その後は別のDBでもこのDBのconfiguration storageテーブルを参照する。

ただ、上記のケースは開発などで、余計なテーブルを作りたくない場合にはダメにみえる。

Manual configuration

sqlディレクトリー内に、create_tables.sqlが見つかる。これを使って手動でテーブルを作る。

Table

以下のテーブルをphpmyadminで使っている模様。

pma__bookmark
pma__central_columns
pma__column_info
pma__designer_settings
pma__export_templates
pma__favorite
pma__history
pma__navigationhiding
pma__pdf_pages
pma__recent
pma__relation
pma__savedsearches
pma__table_coords
pma__table_info
pma__table_uiprefs
pma__tracking
pma__userconfig
pma__usergroups
pma__users

Other

テーブル名一覧

DB内のテーブルが多い場合、ページネーションで全テーブルが表示されず、目当てのテーブル名での検索・到達できない。

以下の手順で該当テーブルを表示できる。

[Search]-[Inside tables:] にはテーブル一覧があるので、これでテーブル名を把握。
&table=のURLクエリーにテーブル名を指定して表示。

テーブル定義出力

database - How to export mysql schema from phpmyadmin? - Super User

[エクスポート]-[詳細]

フォーマット=Texy! text
出力:◎出力をテキストで表示する
フォーマット特有のオプション:ダンプするテーブル=構造

これで出力・表示すると使いやすい。

Import

Import and export — phpMyAdmin 5.1.4 documentation

ネット上のSQLのサンプルを試したいことがある。

例: MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita

以下のようなSQLの実行結果を取り込んで自分でもサンプルを試したい。

mysql> select * from history;
+----+----------+--------------------+------------+
| id | group_id | name               | created_at |
+----+----------+--------------------+------------+
|  1 |        1 | Cupcake            | 2020-01-01 |
|  2 |        1 | Donut              | 2020-01-02 |
|  3 |        2 | Eclair             | 2020-01-03 |
|  4 |        2 | Froyo              | 2020-01-04 |
|  5 |        2 | Gingerbread        | 2020-01-05 |
|  6 |        3 | Honeycomb          | 2020-01-06 |
|  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
|  8 |        4 | Jelly Bean         | 2020-01-08 |
|  9 |        4 | KitKat             | 2020-01-09 |
+----+----------+--------------------+------------+
9 rows in set (0.00 sec)

これをこのまま取り込む方法はない。スプレッドシートでCSVに変換して取り込む。

部分インポート: 行ヘッダーがある場合ここに1を指定。
フォーマット: CSV
カラムの区切り記号: デフォルトの,の他\tでタブも認識する模様。

テーブル一覧表示数

DB選択時 (index.php?route=/database/structure&db=) のテーブル一覧が、1ページ250テーブルでページネーションになっている。

この最大値を増やすには、config.inc.phpの$cfg['MaxTableList']を変更するしかない。

文字化け

DBとphpmyadminのServer connection collationが異なると、他のPHPのアプリにも影響がある模様。

ここはDBの文字エンコーディングと一致させる必要がある。

文字化けして表示されたり、されなかったりしていた。apacheを再起動したら、一旦解消される。

mysql - change default collation in phpmyadmin - Stack Overflow

config.inc.phpに以下の設定でデフォルト値をいれるとよさそう。

$cfg['DefaultConnectionCollation'] = 'utf8_general_ci';

Bookmarks

Bookmarks — phpMyAdmin 5.2.3-dev documentation

入力したクエリーなどを保存しておく機能がある。

ただし、事前に [phpMyAdmin configuration storage] の設定が必要。

SQLのブックマークは、DBごとに保存される。例えば、テーブル名の検索などで、information_schemaを検索することはよくある。これは、information_schemaのDBを開かないとブックマークを表示できない。

末尾に [ブックマークされているSQL] のリストボックスが表示されるので、ここから呼び出せる。

Adminer

Oracle

Docker

https://grok.com/share/c2hhcmQtMw%3D%3D_f7a332ac-d5e4-40b9-8168-79bde08bfbe1

「adminer - Official Image | Docker Hub」に公式イメージがある。デフォルトで、MySQL/PostgreSQL/SQLite/SimpleDB/Elasticsearchに対応している。が、Oracleは未対応。対応するなら、このイメージをベースにPHP拡張の追加インストールが必要。面倒くさい。

Grokによると、対応イメージを用意してくれている人がいるので、これを使うといいとのこと。

soivangoi/nginx-php-adminer-oci8 - Docker Image | Docker Hub

docker run -d -p 8088:80 soivangoi/nginx-php-adminer-oci8

これでlocalhost:8008でアクセスできる。

だが、デフォルト違うオーナーのテーブルが、メニュー一覧に表示されなくて、扱いにくい。認識はできている。

4.3.1でバージョンが古いのが原因らしい。バージョンを上げたら解決するらしい。が、自分でociを使ったdockerを作る必要があって少々面倒くさい。

https://grok.com/share/c2hhcmQtMw%3D%3D_ba20c1c3-4ee3-47e4-8e42-63f51b854915

試行錯誤の末、以下のDockerfileだと、ビルドできて解消できた (2025-09-12 Fri)。adminer 5.1.0でも表示は問題なかった。

FROM php:7.4.0-apache

# 環境変数の設定
ENV ADMINER_VERSION=5.4.0
ENV MEMORY=256M
ENV UPLOAD=2048M
ENV LD_LIBRARY_PATH="/usr/local/instantclient"
ENV ORACLE_HOME="/usr/local/instantclient"
ENV TNS_ADMIN="/usr/local/instantclient/network/admin"

RUN sed -i 's|http://deb.debian.org/debian|http://archive.debian.org/debian|g' /etc/apt/sources.list && \ 
    sed -i 's|http://security.debian.org/debian-security|http://archive.debian.org/debian-security|g' /etc/apt/sources.list && \ 
    echo 'Acquire::Check-Valid-Until "false";' > /etc/apt/apt.conf.d/99ignore-release-date 

# 必要なパッケージと依存関係のインストール
RUN apt-get update && apt-get install -y \
    libaio1 \
    unzip \
    wget \
    ca-certificates \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/*

# Oracle Instant Clientのインストール
ADD https://download.oracle.com/otn_software/linux/instantclient/214000/instantclient-basic-linux.x64-21.4.0.0.0dbru.zip /tmp
ADD https://download.oracle.com/otn_software/linux/instantclient/214000/instantclient-sdk-linux.x64-21.4.0.0.0dbru.zip /tmp
RUN unzip /tmp/instantclient-basic-linux.x64-21.4.0.0.0dbru.zip -d /usr/local/ && \
    unzip /tmp/instantclient-sdk-linux.x64-21.4.0.0.0dbru.zip  -d /usr/local/ &&\
    ln -s /usr/local/instantclient_21_4 /usr/local/instantclient 
ENV NLS_LANG=Japanese_Japan.JA16SJISTILDE
ENV LD_LIBRARY_PATH=/usr/local/instantclient

# OCI8拡張のインストール
RUN docker-php-ext-configure oci8 --with-oci8=instantclient,/usr/local/instantclient && \
    docker-php-ext-install oci8 && \ 
    docker-php-ext-configure pdo_oci --with-pdo-oci=instantclient,/usr/local/instantclient,21.4 && \
    docker-php-ext-install pdo_oci && \
    docker-php-ext-enable oci8

# Adminerのインストール
RUN wget https://github.com/vrana/adminer/releases/download/v${ADMINER_VERSION}/adminer-${ADMINER_VERSION}.php -O /var/www/html/index.php

# PHP設定
RUN echo "memory_limit=${MEMORY}" > /usr/local/etc/php/conf.d/adminer.ini && \
    echo "upload_max_filesize=${UPLOAD}" >> /usr/local/etc/php/conf.d/adminer.ini && \
    echo "post_max_size=${UPLOAD}" >> /usr/local/etc/php/conf.d/adminer.ini

# 作業ディレクトリの設定
WORKDIR /var/www/html

docker build -t adminer-oracle .
docker run -d -p 8088:80 adminer-oracle

これでlocalhost:8088をブラウザーで開く。解決！

login

Oracle DB接続時は、ログイン時の入力内容に注意が必要。

[サーバー] 欄に以下の形式で項目入力が必要。

host:port/service_name

host: サーバーのホスト名またはIPアドレス。
port: ポート。通常は1521
service_name: DBのサービス名。TSNの識別子。

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

間違えていると以下のようなエラーが出る。

これで表示はできる。

owner

Oracleではスキーマ=オーナー。

Adminerでは、DBとスキーマを同じ名前にすると、そのオーナーのテーブルを一覧できる模様。

制限

Oracle database対応は不十分で、いくつかの機能がない。

テーブルの複製 (移動や削除は可能)
SQL形式でのエクスポート (CSVなどなら対応)
テーブル以外のfunction/procedureなどの閲覧。SELECT * FROM ALL_PROCEDURESなどのSQLで確認可能。

テーブルの複製はSQLでやるしかなさそう。

テーブル以外を閲覧できないのが痛い。いまいち。DBeaverのほうがよさそう。

DBeaver

Basic

DBeaverの使い方(Oracleに接続してSQLを実行) | ITSakura

SQL開発ツールとしてのDBeaverの設定

[Database Navigator] ペーン-[Create]-[Connection]-[Oracle]-[Next]

[Oracle Connection Settings] で接続情報を入力。

[Test Connection] を選ぶ。必要なドライバーを自動的に取得する。

driver files are missing

[Test Connection]-[Download driver files]-[Download] を選んでも [Oracle driver files are missing.] で失敗する。

DBeaverからドライバインストールに失敗した場合の対応手順 #Mac - Qiita

プロキシー設定などで取得に失敗しているのが原因の模様。自分でファイルを取得して配置してもいい。

[Edit Connection]-[Connection settings]-[Main]-[Driver settings]

[Libraries]

デフォルトを全部削除。

%AppData%\Local\DBeaver\lib\oracleとかに配置する。

これでOK。

ER図

テーブルを選択-[Diagram] タブ。これだけで勝手に作ってくれている。めちゃくちゃ楽。

CLOBの閲覧

https://grok.com/share/c2hhcmQtMw%3D%3D_14def65d-074e-4453-b21c-5251822d09aa

データ量が大きいと、途中で打ち切られる。いくつか対策がある。右クリック-[Export data]

Binary data
- [Window]-[Preference]-[Editors]-[Data Editor]-[Binary Editor]
- [Maximum LOB length to keep in memory (10000):
Content
- Maximum text editor content length (KB) (100): テキストエディターに表示するサイズ。これを増やすと表示に反映される。

「https://wiki.senooken.jp/index.php?title=DB&oldid=5057」から取得

@@ 21行目: / 21行目: @@
 * [https://stackoverflow.com/questions/2111958/does-column-order-matter-in-your-mysql-tables database - Does column order matter in your MySQL tables? - Stack Overflow]
 * [https://stackoverflow.com/questions/894522/is-there-any-reason-to-worry-about-the-column-order-in-a-table mysql - Is there any reason to worry about the column order in a table? - Stack Overflow]
+* https://chatgpt.com/share/6885bd3c-6838-800b-9732-6abab69744f7
-パフォーマンスに影響がある。特に下2個の情報が参考になる。
+パフォーマンスに影響がある。特に下3個の情報が参考になる。
-基本的に、頻繁に使用する列 (主キー、外部キー、頻出検索、頻出更新の順) を先頭に配置する。ただし、null許容は全体的に後ろ。null許容の中でも頻度順にする。
+基本的に、頻繁に使用する列 (主キー、外部キー、頻出検索、頻出更新の順) を先頭に配置する。ただし、null許容は全体的に後ろ。null許容の中でも頻度順にする。NULL許容の前に、TEXTなどの可変長。
+つまり、「固定長→可変長→NULL許容」の順番にする。さらに、それぞれのブロックで、「アクセス頻度高→低」の順番にする。
 ディスクスペースの利用率、使用するデータの効率などに影響がある模様。
+created_at/updated_atのような全テーブルに付随するようなカラムは、主キーの直後でいい。全テーブルで共通にできるから。
 === マスター ===
 [https://jsol-datamanagement.com/solution/solution-575/ マスタデータって何？マスタデータについて詳しく解説！ - J-MDM／Snowflake]
@@ 434行目: / 439行目: @@
 構文強調がないと、formなどと見分けがつきにくいので、大文字にしたほうがいい。可読性優先。
+https://grok.com/share/c2hhcmQtMw%3D%3D_7be14fd7-bc9b-460a-935f-d1cbcca413f6
+ただ、Oracle databaseは内部的に全部大文字にするので、カラム名とかは全部大文字でOK。
+==== 単語区切り ====
+https://grok.com/share/c2hhcmQtMw%3D%3D_7be14fd7-bc9b-460a-935f-d1cbcca413f6
+単語区切りはスネークケースがいい。内部的に大文字小文字をどちらかに正規化するRDBが多い。キャメルケースとかは引用符で囲んだりしないと表現できなくて面倒くさい。
+JavaScriptのプロパティーがcamelCaseなのが一般的でテーブルカラムをそのまま使えないのが気になる。ライブラリーを間に挟むとか、サーバーAPIで変換かけるとかする。
 ==== 共通カラム ====
@@ 671行目: / 686行目: @@
 == SQL ==
+=== About ===
+[https://qiita.com/hinako_n/items/e25c971b156e4abf122b 【SQL】4種類の命令(DML, DDL, TCL, DCL) #SQL - Qiita]
+①データ操作言語　DML (Data Manipulation Language): select/insert/update/delete/lock table/explain
+②トランザクション制御言語　TCL (Transaction Control Language): commit/rollback/set transaction/savepoint
+③データ定義言語　DDL (Data Definition Language): create/drop/alter/truncate/rename/comment
+④データ制御言語　DCL (Data Control Language): grant/revoke
 === DML ===
@@ 676行目: / 702行目: @@
 INSERT/SELECT/UPDATE/DELETEが該当。
+==== Expression/式 ====
+===== CASE =====
+* [https://docs.oracle.com/cd/F19136_01/lnpls/plsql-language-fundamentals.html#GUID-216F1B33-493F-4CDE-93BB-096BACA8523E PL/SQL言語の基礎]
+* [https://techmania.jp/blog/sql-case/ 5分でわかるSQLの条件分岐処理「CASE式」の使い方【完全版】 │ TechMania]
+種類の構文がある。
+ CASE selector
+ WHEN selector_value_1 THEN result_1
+ WHEN selector_value_2 THEN result_2
+ ...
+ WHEN selector_value_n THEN result_n
+ [ ELSE
+   else_result ]
+ END
+JSONを作る際にも使う。
 ==== DELETE ====
@@ 812行目: / 856行目: @@
 # マスター系テーブルで固有でINSERTする場合、重複除去のためにNOT NULL+UNIQUE制約をつけておく。
 ## NULL != NULLが成立するので、UNIQUE制約をつける場合はNOT NULLもつけておく。
+===== Oracle =====
+====== Basic ======
+UPDATE文実行時に、対象レコードが0だったとしても失敗しないので注意。OracleだとMERGE文がUPSERT相当。
+* [https://docs.oracle.com/cd/F19136_01/sqlrf/MERGE.html MERGE]
+* [https://resanaplaza.com/2023/02/26/%e3%80%90%e5%ae%9f%e7%94%a8%e3%80%91oracle%e3%81%ab%e3%81%8a%e3%81%91%e3%82%8bupsert%e3%81%ae%e6%9b%b8%e3%81%8d%e6%96%b9%ef%bc%88%e3%82%b5%e3%83%b3%e3%83%97%e3%83%ab%e4%bb%98%e3%81%8d%ef%bc%89/ 【詳しく解説】OracleにおけるUpsertの書き方（サンプル付き） | 初学者DIYプログラミング入門]
+* [https://codingls.com/plsql/4636/ 【PL/SQL】MERGE文の活用法｜UPSERT処理を高速かつ安全に実装する方法 | コーディングライフスタイル]
+ MERGE INTO target_table tgt
+ USING (SELECT :id AS id, :name AS name FROM dual) src
+   ON (tgt.id = src.id)
+ WHEN MATCHED THEN
+   UPDATE SET tgt.name = src.name -- キー存在時の処理
+ WHEN NOT MATCHED THEN
+   INSERT (id, name) VALUES (src.id, src.name); -- キー不在時の処理
+dualはOracle専用のダミーテーブル ([https://note.com/mute_quoll963/n/n526f9b930842 【勉強ブログ】Oracleだけにある謎の表「DUAL」って何？―ORACLE MASTER Silver DBAへの道 #6｜よっしー])。
 ==== GROUP BY ====
@@ 1,025行目: / 1,087行目: @@
   		ELSE 引数1
   END;
+なお、Oracle databaseの場合、NVL/NVL2という関数もある ([https://qiita.com/waokitsune/items/08d955741783f31eb31d OracleのNULL置換（NVL、NVL2、COALESCE、三者の違いやどれを使うか選ぶ時の参考など） #oracle - Qiita])。
 === 日付範囲 ===
@@ 1,171行目: / 1,234行目: @@
   SET @row=0; SELECT @row:=@row+1 as ROW_NUM, t.* FROM [table] t;
-=== Table ===
+=== DDL ===
+データ定義言語　DDL (Data Definition Language)
+{| class="wikitable"
+!命令
+!説明
+|-
+|CREATE
+|テーブルを新規作成する
+|-
+|DROP
+|既存のデータベースオブジェクトを削除する
+|-
+|ALTER
+|既存のデータベースオブジェクトを変更する
+|-
+|TRUNCATE
+|テーブル内のデータを全削除する
+|-
+|RENAME
+|既存のデータベースのオブジェクトの名前を変更する
+|-
+|COMMENT
+|データベースのオブジェクトのコメントを定義、または変更する
+|}
 ==== CREATE TABLE ====
+===== 複合制約 =====
 [https://letitride.hatenablog.com/entry/20120907/1347013609 複合unique制約のつけ方 - テックメモ跡地]
   CREATE TABLE example(
@@ 1,181行目: / 1,269行目: @@
       UNIQUE ( column2, column3 )
   );
+===== 複製 =====
+* [https://stellacreate.com/entry/oracle-table-copy 【Oracle】テーブルをコピーする | StellaCreate]
+* [https://zenn.dev/db_tech/articles/8b73e40bb652cb 【Oracle】既存テーブルのコピーを作成する（データあり/なし）]
+いくつか方法がある。
+テーブル定義とデータのコピー。
+ CREATE TABLE "新規作成するテーブル" AS SELECT * FROM "コピー元テーブル";
 ==== SHOW tables ====
@@ 1,218行目: / 1,316行目: @@
 ,で区切って、同じテーブルへの複数のサブ文？を1文で書ける。
-PostgreSQL
+====== PostgreSQL ======
 [https://www.postgresql.jp/document/16/html/sql-altertable.html ALTER TABLE]
   ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
@@ 1,239行目: / 1,336行目: @@
 カラム追加時は、AFTERでどの列の後ろに追加するかを指定できる。指定しない場合、末尾になる。
-==== 削除 ====
+====== Oracle ======
-[https://www.earthlink.co.jp/engineerblog/technology-engineerblog/2680/ DELETE文 TRUNCATE文 DROP文の違い(SQL構文) | 株式会社アースリンク]
+* [https://sql-oracle.com/?p=222 【Oracle】テーブルに列（カラム）を追加するSQL | Oracle初心者でもスッキリわかる]
+* [https://www.silicloud.com/ja/blog/oracle%e3%81%a7%e6%8c%87%e5%ae%9a%e3%81%95%e3%82%8c%e3%81%9f%e4%bd%8d%e7%bd%ae%e3%81%ab%e3%83%95%e3%82%a3%e3%83%bc%e3%83%ab%e3%83%89%e3%82%92%e8%bf%bd%e5%8a%a0%e3%81%99%e3%82%8b%e6%96%b9%e6%b3%95/ Oracle テーブル特定位置へのカラム追加方法【ALTER TABLE】 - Blog - Silicon Cloud]
+* [https://www.oracletutorial.com/oracle-basics/oracle-alter-table-add-column/ Oracle ALTER TABLE ADD Column]
-テーブルのデータ削除の方法がいくつかある。
+ ALTER TABLE {テーブル名} ADD (列定義).
+ ALTER TABLE table_name ADD column_name data_type constraint;
+ ALTER TABLE table_name
+ ADD (
+     column_1 datatype constraint,
+     column_2 datatype constraint,
+     ...
+ );
-* DELETE: COMMITしてなければロールバック可能。WHEREで行を指定して削除。そのため多少時間かかる。AUTO_INCREMENT維持。
+ -- table1に列pnoとmemoを追加するSQL
-* TRUNCATE: 全削除して再作成。DELETEより速い。ロールバック不能。AUTO_INCREMENTもリセット。
+ ALTER TABLE table1 ADD (pno NUMBER(5,0) ,memo VARCHAR2(100));
-* DROP: ロールバックも不能でテーブル構造も消える。AUTO_INCREMENTもリセット。
+ ALTER TABLE table_name ADD new_column VARCHAR2(50) NOT NULL;
+なお、Oracle Databaseだとafter/before相当のカラム位置指定は不能。追加後の位置変更も不能。テーブル再作成しかない。
- DELETE FROM table_name;
+https://grok.com/share/c2hhcmQtMw%3D%3D_da633c97-e4aa-4284-8100-93ef74cf9be5
- TRUNCATE TABLE table_name;
- DROP TABLE table_name;
-基本はTRUNCATEでOK。
-==== AUTO_INCREMENT ====
+===== AUTO_INCREMENT =====
 [https://dev.mysql.com/doc/refman/8.0/ja/example-auto-increment.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 3.6.9 AUTO_INCREMENT の使用]
@@ 1,266行目: / 1,371行目: @@
 次の値を指定も可能。
    ALTER TABLE tbl AUTO_INCREMENT = 100;
+===== 複合主キーの変更 =====
+https://grok.com/share/c2hhcmQtMw%3D%3D_c20aeee5-b5f2-4e7f-b583-c7d6d1276ec5
+複合主キーを探して、削除して、設定する。oracle databaseの話。
+ SELECT constraint_name
+ FROM user_constraints
+ WHERE table_name = 'YOUR_TABLE' AND constraint_type = 'P';
+ ALTER TABLE your_table
+ DROP CONSTRAINT PK_YOUR_TABLE;
+ ALTER TABLE your_table
+ ADD CONSTRAINT PK_YOUR_TABLE PRIMARY KEY (col1, col2, sub_no);
+テーブルをコピーして練習してからのほうがいいかも。
+==== 削除 ====
+[https://www.earthlink.co.jp/engineerblog/technology-engineerblog/2680/ DELETE文 TRUNCATE文 DROP文の違い(SQL構文) | 株式会社アースリンク]
+テーブルのデータ削除の方法がいくつかある。
+* DELETE: COMMITしてなければロールバック可能。WHEREで行を指定して削除。そのため多少時間かかる。AUTO_INCREMENT維持。
+* TRUNCATE: 全削除して再作成。DELETEより速い。ロールバック不能。AUTO_INCREMENTもリセット。
+* DROP: ロールバックも不能でテーブル構造も消える。AUTO_INCREMENTもリセット。
+ DELETE FROM table_name;
+ TRUNCATE TABLE table_name;
+ DROP TABLE table_name;
+基本はTRUNCATEでOK。
 === Function ===
@@ 2,008行目: / 2,144行目: @@
 |}
 成功。
+== MySQL ==
-== phpMyAdmin ==
+=== SQL ===
-=== Install ===
+==== カラム名検索 ====
+[https://qiita.com/dkwnm/items/3aa7be0bca95a2a75304 MySQLで指定されたカラム名を持つテーブルを検索する #MySQL - Qiita]
+ select table_name, column_name from information_schema.columns where column_name = '検索したいカラム名' and table_schema = '検索対象のデータベース名';
-==== phpMyAdmin configuration storage ====
+=== Type ===
-[https://docs.phpmyadmin.net/en/latest/setup.html#linked-tables Installation — phpMyAdmin 5.2.3-dev documentation]
-phpmyadmin 3.4.0未満までLinked Tables Infrastructureと呼んでいた、configuration storageという機能がある。この機能を有効にすることで、ブックマークやコメントなどの機能が使えるようになる。
+==== About ====
+[https://dev.mysql.com/doc/refman/8.0/ja/data-types.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 11 データ型]
-種類の設定方法がある。
+データ型の書式がある。
-* Zero configuration
+* DECIMAL(M [, D]): M=全体の桁数。D=内小数部の桁数。
-* Manual configuration
+* CHAR(M)/VARCHAR(M): M=格納最大文字数。CHARのM=0-255までで末尾はスペース。VARCHARはM=0-65535までで、可変長文字列で256以上もOK。末尾のスペースは削除される。先頭にプレフィクスの1-2バイトのデータを付与している。255バイト以下の文字列なら1バイト、256バイト以上なら2バイト追加。
-それぞれの方法を検討する限り、結局保存用のテーブルが必要なので、Zero configurationでtestデータベースに作るのがいいだろう。
+==== AUTO_INCREMENT ====
+やNULLがカラムに設定される場合、DBで持っている増分値を使ってくれる設定。
-===== Zero configuration =====
+AUTO_INCREMENTのカラムを2以上定義しようとすると以下のエラーになる。
-Zero configurationは多くの場合に自動構成されるもの。特に共有ホスティングで役立つ。デフォルトオンになっている。
+ there can be only one auto column and it must be defined as a key
-以下の3のシナリオをカバーしている。
+* [https://mimirswell.ggnet.co.jp/blog-185 【MySQL】AUTO_INCREMENT属性カラムの制約 | ミーミルの泥泉]
+* [https://qiita.com/nakamoto_yuki/items/69cf9ba26f1b651808af マリアDBでテーブル作成時のエラー「there can be only one auto column and it must be defined as a key mariadb」 #MySQL - Qiita]
-# configuration storageテーブル不在で、[Operation] タブにアクセスすると、作成を提案する ([The phpMyAdmin configuration storage has been deactivated. Find out why.] の表示)。このリンク[Find out why]-[Create the phpMyAdmin configuration storage in the current database.]の[Create]をクリックして作る。
+AUTO_INCREMENT属性は、PRIMARY KEYかUNIQUE KEYがないとつけられないらしい。
-# configuration storageテーブルがすでに存在するDBの場合、自動的に検知して使う。ユーザーが1DBにしかアクセスできない共有ホスティングで役立つ。
-# 複数のDBにユーザーがアクセス可能な場合、configuration storageテーブルを含むDBにアクセスしたら、その後は別のDBでもこのDBのconfiguration storageテーブルを参照する。
-ただ、上記のケースは開発などで、余計なテーブルを作りたくない場合にはダメにみえる。
+[https://zenn.dev/ikeo/articles/9b4d2513c0528f MySQLでAUTO_INCREMENTが指定されているカラムに「0」を入れるには]
-===== Manual configuration =====
+[NO_AUTO_VALUE_ON_ZERO] を指定しておくと、0の場合。増分値を使わない。
-sqlディレクトリー内に、create_tables.sqlが見つかる。これを使って手動でテーブルを作る。
-===== Table =====
+==== Cast ====
-以下のテーブルをphpmyadminで使っている模様。
- pma__bookmark
- pma__central_columns
- pma__column_info
- pma__designer_settings
- pma__export_templates
- pma__favorite
- pma__history
- pma__navigationhiding
- pma__pdf_pages
- pma__recent
- pma__relation
- pma__savedsearches
- pma__table_coords
- pma__table_info
- pma__table_uiprefs
- pma__tracking
- pma__userconfig
- pma__usergroups
- pma__users
-=== Other ===
+* [https://dev.mysql.com/doc/refman/8.0/ja/insert.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.6 INSERT ステートメント]
+* [https://dev.mysql.com/doc/refman/8.0/ja/type-conversion.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.3 式評価での型変換]
+* [https://dev.mysql.com/doc/refman/8.0/ja/cast-functions.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.11 キャスト関数と演算子]
-==== テーブル名一覧 ====
+暗黙の型変換がある。
-DB内のテーブルが多い場合、ページネーションで全テーブルが表示されず、目当てのテーブル名での検索・到達できない。
-以下の手順で該当テーブルを表示できる。
+INSERTでは、式のデータ型とカラムのデータ型が不一致の場合にも型変換が発生する。
-# [Search]-[Inside tables:] にはテーブル一覧があるので、これでテーブル名を把握。
+文字列から数値への変換では、文字列の先頭から有効な数字部分までを数値とみなす。
-# &table=のURLクエリーにテーブル名を指定して表示。
-==== テーブル定義出力 ====
+ただ、'00120'のような0始まりの数字も先頭の0を除去して数値とみなしてくれる。この根拠は見つけられなかった。
-[https://superuser.com/questions/168595/how-to-export-mysql-schema-from-phpmyadmin database - How to export mysql schema from phpmyadmin? - Super User]
-[エクスポート]-[詳細]
+==== 数値型の属性 ====
+* [https://qiita.com/mamy1326/items/74b040f855d4023b0178 MySQL 8.0 から int の ZEROFILL 指定が deprecated #MySQL8.0 - Qiita]
+* [https://stackoverflow.com/questions/69858233/why-default-int11 mysql - Why default INT(11) - Stack Overflow]
+* [https://dev.mysql.com/doc/refman/8.0/en/integer-types.html MySQL :: MySQL 8.0 Reference Manual :: 13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT]
+* [https://dev.mysql.com/doc/refman/8.0/ja/numeric-type-syntax.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 11.1.1 数値データ型の構文]
+* [https://stackoverflow.com/questions/14573451/mysql-size-limits-to-integer-columns phpmyadmin - MySQL - Size Limits to Integer Columns - Stack Overflow]
+MySQLは独自拡張で数値系のデータ型にint(9)のように表示幅を指定できる。DB上の表示幅なだけで実データには影響ない。それに、MySQL 8.0.17からdeprecated/廃止予定扱い。使っちゃダメ。
+データ型で幅を指定しない場合、デフォルトの幅が内部的に存在する模様。
-* フォーマット=Texy! text
+この幅は、その数値型の最小・最大値の符号を除去した桁数。
-* 出力:◎出力をテキストで表示する
-* フォーマット特有のオプション:ダンプするテーブル=構造
-これで出力・表示すると使いやすい。
+phpmyadminなど一部のクライアントがこの幅を勝手に使うので注意する。
-==== Import ====
+==== BOOLEAN ====
-[https://docs.phpmyadmin.net/en/latest/import_export.html Import and export — phpMyAdmin 5.1.4 documentation]
+https://chatgpt.com/c/6796f621-7264-800b-9a07-4c04fc8ae63b
-ネット上のSQLのサンプルを試したいことがある。
+MySQLのBOOLEANは内部的にTINYINT(1)に変換される。
-例: [https://qiita.com/kyamadahoge/items/fdbc04790f620dddbf3c MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita]
+BOOLEAN自体はSQL:1999で標準化されているが、対応状況はまちまち。
-以下のようなSQLの実行結果を取り込んで自分でもサンプルを試したい。
+* MySQL: TINYINT(1)として内部的に処理。
- mysql> select * from history;
+* PostgreSQL: 完全対応。
- +----+----------+--------------------+------------+
+* SQLite: 内部的にINTEGER。
- | id | group_id | name               | created_at |
+* SQL Server: 未対応。
- +----+----------+--------------------+------------+
+* Oracle: 未対応。
- |  1 |        1 | Cupcake            | 2020-01-01 |
- |  2 |        1 | Donut              | 2020-01-02 |
- |  3 |        2 | Eclair             | 2020-01-03 |
- |  4 |        2 | Froyo              | 2020-01-04 |
- |  5 |        2 | Gingerbread        | 2020-01-05 |
- |  6 |        3 | Honeycomb          | 2020-01-06 |
- |  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
- |  8 |        4 | Jelly Bean         | 2020-01-08 |
- |  9 |        4 | KitKat             | 2020-01-09 |
- +----+----------+--------------------+------------+
-rows in set (0.00 sec)
-これをこのまま取り込む方法はない。スプレッドシートでCSVに変換して取り込む。
-* 部分インポート: 行ヘッダーがある場合ここに1を指定。
+=== Backup ===
-* フォーマット: CSV
+MySQLのデータのバックアップには2種類の方法がある。
-* カラムの区切り記号: デフォルトの,の他\tでタブも認識する模様。
-==== テーブル一覧表示数 ====
+* mysqldump
+* データファイルのコピー
-* https://chatgpt.com/c/67b3df2c-b26c-800b-a60d-46febafe8bcf
+基本的にはmysqldumpコマンドを使ってデータをSQLのテキストファイルとして出力する。
-* [https://docs.phpmyadmin.net/ja/latest/config.html 設定 — phpMyAdmin 6.0.0-dev ドキュメント]
-DB選択時 (index.php?route=/database/structure&db=) のテーブル一覧が、1ページ250テーブルでページネーションになっている。
+[https://dev.mysql.com/doc/refman/8.0/ja/mysqldump.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム]
-この最大値を増やすには、config.inc.phpの$cfg['MaxTableList']を変更するしかない。
+全データベース、データベース、テーブル単位のそれぞれでバックアップできる。
-==== 文字化け ====
+全データベースのバックアップには--all-databasesを指定する。
-DBとphpmyadminのServer connection collationが異なると、他のPHPのアプリにも影響がある模様。
+ mysqldump -p -u <username> --all-databases >mysql.sql
+ mysqldump -p -u <username> <db_name> [table_name ...]
+復元はmysqlコマンドでSQLを読み込めばいい。
+ mysql -p -u username <mysql.sql
+ mysql -p -u username database_name <file.sql
-ここはDBの文字エンコーディングと一致させる必要がある。
+=== Error ===
-文字化けして表示されたり、されなかったりしていた。apacheを再起動したら、一旦解消される。
+==== Error: [PDOException] PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] ====
+* [https://www.petitmonte.com/database/mysql_authentication_plugin.html MySQL8.0以降で接続できない場合は「認証プラグイン」を変更する]
+* [https://symfoware.blog.fc2.com/blog-entry-2160.html phpからMySQL 8.0へPDOで接続時「SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client」 - Symfoware]
+* [https://yoku0825.blogspot.com/2018/01/mysql-804.html 日々の覚書: MySQL 8.0.4におけるデフォルト認証形式の変更]
+* [https://qiita.com/ucan-lab/items/b094dbfc12ac1cbee8cb docker-compose MySQL8.0 のDBコンテナを作成する #Docker - Qiita]
-[https://stackoverflow.com/questions/153706/change-default-collation-in-phpmyadmin mysql - change default collation in phpmyadmin - Stack Overflow]
+MySQL 8.0になってから、認証方式が変わったらしく、そのままだと接続に失敗する。
-config.inc.phpに以下の設定でデフォルト値をいれるとよさそう。
+PHP 7.1.16, 7.2.4 以降から対応しているらしい。それ以前だと設定が必要。
- $cfg['DefaultConnectionCollation'] = 'utf8_general_ci';
-==== Bookmarks ====
+==== Numeric value out of range: 1264 Out of range value for column ====
-[https://docs.phpmyadmin.net/en/latest/bookmarks.html Bookmarks — phpMyAdmin 5.2.3-dev documentation]
+[https://qiita.com/mindwood/items/9a47dfabd0c5cebf1a62 "Out of range value for column 'カラム名' at 行番号" に対処した #MySQL - Qiita]
-入力したクエリーなどを保存しておく機能がある。
+エラーコード 1264 は、データ型の範囲を超えた値をINSERTしたときに発生するWarningである。直前に実行したSQLのWarningは SHOW WARNINGS で確認できる。
-ただし、事前に [phpMyAdmin configuration storage] の設定が必要。
+=== Config ===
-SQLのブックマークは、DBごとに保存される。例えば、テーブル名の検索などで、information_schemaを検索することはよくある。これは、information_schemaのDBを開かないとブックマークを表示できない。
+==== Character ====
+[https://qiita.com/tfunato/items/e48ad0a37b8244a788f6 MySQLの文字コードとCollation #MySQL - Qiita]
-末尾に [ブックマークされているSQL] のリストボックスが表示されるので、ここから呼び出せる。
+DBで使用する文字セットと、その照合順序を選択できる。
-== MySQL ==
-=== SQL ===
+文字コードはutf8mb4で問題ない。照合順序も基本はutf8mb4_binで問題ないと思う。
-==== カラム名検索 ====
+A ≠ a 区別する
-[https://qiita.com/dkwnm/items/3aa7be0bca95a2a75304 MySQLで指定されたカラム名を持つテーブルを検索する #MySQL - Qiita]
- select table_name, column_name from information_schema.columns where column_name = '検索したいカラム名' and table_schema = '検索対象のデータベース名';
-=== Type ===
+≠  区別する
-==== About ====
+は≠ぱ≠ば 区別する
-[https://dev.mysql.com/doc/refman/8.0/ja/data-types.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 11 データ型]
-データ型の書式がある。
+utf8mb4_general_ciは上2個を区別しない。
-* DECIMAL(M [, D]): M=全体の桁数。D=内小数部の桁数。
+区別しないとあいまい検索で都合がいいことがある。
-* CHAR(M)/VARCHAR(M): M=格納最大文字数。CHARのM=0-255までで末尾はスペース。VARCHARはM=0-65535までで、可変長文字列で256以上もOK。末尾のスペースは削除される。先頭にプレフィクスの1-2バイトのデータを付与している。255バイト以下の文字列なら1バイト、256バイト以上なら2バイト追加。
-==== AUTO_INCREMENT ====
+ただし、あいまい検索にすると、対象範囲が増えるので、少し遅くなる模様 ([https://yakst.com/ja/posts/5431 CharsetとCollationの設定がMySQLのパフォーマンスに与える影響 | Yakst])。
-やNULLがカラムに設定される場合、DBで持っている増分値を使ってくれる設定。
-AUTO_INCREMENTのカラムを2以上定義しようとすると以下のエラーになる。
+==== SQL mode ====
- there can be only one auto column and it must be defined as a key
+[https://dev.mysql.com/doc/refman/8.0/ja/sql-mode.html#sql-mode-combo MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.11 サーバー SQL モード]
-* [https://mimirswell.ggnet.co.jp/blog-185 【MySQL】AUTO_INCREMENT属性カラムの制約 | ミーミルの泥泉]
+SQLの基本的な動きを制御する設定がある。以下で現在値を確認。
-* [https://qiita.com/nakamoto_yuki/items/69cf9ba26f1b651808af マリアDBでテーブル作成時のエラー「there can be only one auto column and it must be defined as a key mariadb」 #MySQL - Qiita]
+ SELECT @@GLOBAL.sql_mode;
+ SELECT @@SESSION.sql_mode;
+以下で設定。
+ SET GLOBAL sql_mode = 'modes';
+ SET SESSION sql_mode = 'modes';
+GLOBALは権限が必要。SESSIONは誰でも変更可能。
-AUTO_INCREMENT属性は、PRIMARY KEYかUNIQUE KEYがないとつけられないらしい。
+デフォルト=ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO および NO_ENGINE_SUBSTITUTION。
-[https://zenn.dev/ikeo/articles/9b4d2513c0528f MySQLでAUTO_INCREMENTが指定されているカラムに「0」を入れるには]
+カンマで区切って複数指定できる。
-[NO_AUTO_VALUE_ON_ZERO] を指定しておくと、0の場合。増分値を使わない。
+モードの値としては、ANSIが重要。基本はこれでよい。
-==== Cast ====
+==== Collation ====
+[https://qiita.com/kazu56/items/6af85ffcf8d3954455ad 【MySQL】照合順序とは？ #MySQL - Qiita]
-* [https://dev.mysql.com/doc/refman/8.0/ja/insert.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.6 INSERT ステートメント]
+[https://dev.mysql.com/doc/refman/8.4/en/charset-collation-names.html MySQL :: MySQL 8.4 Reference Manual :: 12.3.1 Collation Naming Conventions]
-* [https://dev.mysql.com/doc/refman/8.0/ja/type-conversion.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.3 式評価での型変換]
-* [https://dev.mysql.com/doc/refman/8.0/ja/cast-functions.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.11 キャスト関数と演算子]
-暗黙の型変換がある。
+utf8_general_ciのようなcollate (照合順序) がある。これの意味。
-INSERTでは、式のデータ型とカラムのデータ型が不一致の場合にも型変換が発生する。
+書式 [文字集合]_[言語名]_[比較法]
-文字列から数値への変換では、文字列の先頭から有効な数字部分までを数値とみなす。
+* 文字集合: character set
+* 言語名: general=マルチリンガル。0900=UCA 9.0.0。bin=バイナリ比較。バイト単位で比較する。大文字小文字やアクセントの違いなどを考慮する。
+* 比較法: ai=アクセント無視 (はとぱは同一)/as=アクセント区別/ci=大文字小文字無視 (case ignore)/cs=大文字小文字区別 (case sensitive)/ks=カナ区別/bin=バイナリー。
-ただ、'00120'のような0始まりの数字も先頭の0を除去して数値とみなしてくれる。この根拠は見つけられなかった。
+===== default =====
+mysqlのデフォルトはutf8_general_ci。
-==== 数値型の属性 ====
+* general:
+* unicode: 全角半角を区別しない。
-* [https://qiita.com/mamy1326/items/74b040f855d4023b0178 MySQL 8.0 から int の ZEROFILL 指定が deprecated #MySQL8.0 - Qiita]
+絵文字の扱いなどが変わってくる。
-* [https://stackoverflow.com/questions/69858233/why-default-int11 mysql - Why default INT(11) - Stack Overflow]
-* [https://dev.mysql.com/doc/refman/8.0/en/integer-types.html MySQL :: MySQL 8.0 Reference Manual :: 13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT]
-* [https://dev.mysql.com/doc/refman/8.0/ja/numeric-type-syntax.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 11.1.1 数値データ型の構文]
-* [https://stackoverflow.com/questions/14573451/mysql-size-limits-to-integer-columns phpmyadmin - MySQL - Size Limits to Integer Columns - Stack Overflow]
-MySQLは独自拡張で数値系のデータ型にint(9)のように表示幅を指定できる。DB上の表示幅なだけで実データには影響ない。それに、MySQL 8.0.17からdeprecated/廃止予定扱い。使っちゃダメ。
+* MySQL 8.0=utf8mb4/utf8mb4_0900_ai_ci ([https://dev.mysql.com/doc/refman/8.0/en/charset-server.html MySQL :: MySQL 8.0 Reference Manual :: 12.3.2 Server Character Set and Collation]/[https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html MySQL :: MySQL 8.0 Reference Manual :: 12.2 Character Sets and Collations in MySQL])
+* MySQL 5.7=latin1/latin1_swedish_ci ([https://dev.mysql.com/doc/refman/5.7/en/charset-server.html MySQL :: MySQL 5.7 Reference Manual :: 10.3.2 Server Character Set and Collation])
+* MySQL 5.7=utf8mb4/utf8mb4_general_ci ([https://dev.mysql.com/doc/refman/5.7/en/charset-mysql.html MySQL :: MySQL 5.7 Reference Manual :: 10.2 Character Sets and Collations in MySQL])
-データ型で幅を指定しない場合、デフォルトの幅が内部的に存在する模様。
+===== utf8mb4_bin/utf8mb4_0900_bin =====
-この幅は、その数値型の最小・最大値の符号を除去した桁数。
+* [https://blog.siwa32.com/mysql_collation/ MySQL8のutf8mb4におけるCollation比較 - BLOG - siwa32.com]
+* [https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.17 (2019-07-22, General Availability)]
+* https://chatgpt.com/c/674ce499-dcd0-800b-bb9c-42a8fbe21805
-phpmyadminなど一部のクライアントがこの幅を勝手に使うので注意する。
+utf8mb4_0900_binなどの0900はMySQL 8.0から使用可能。0900はUnicode 9.0の意味。MySQL専用でMariaDBにはない。
-==== BOOLEAN ====
+utf8mb4_binは終端スペースを除去する。0900は除去しない。
-https://chatgpt.com/c/6796f621-7264-800b-9a07-4c04fc8ae63b
-MySQLのBOOLEANは内部的にTINYINT(1)に変換される。
+utf8mb4_binは照合時に先頭に0を追加する場合があるが、0900はそれがなく、0900のほうが並べ替えがはるかに高速。らしい。
-BOOLEAN自体はSQL:1999で標準化されているが、対応状況はまちまち。
+基本はutf8mb4_0900_binを使うとよさそう。
-* MySQL: TINYINT(1)として内部的に処理。
+===== language =====
-* PostgreSQL: 完全対応。
+[https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html MySQL :: MySQL 8.0 Reference Manual :: 12.10.1 Unicode Character Sets]
-* SQLite: 内部的にINTEGER。
-* SQL Server: 未対応。
-* Oracle: 未対応。
-=== Backup ===
+generalとかunicodeとかの言語名部分の違いが上記に記載がある。
-MySQLのデータのバックアップには2種類の方法がある。
-* mysqldump
+なお、日本語の異体字はDBでは吸収できない。アプリ側で対応必要 ([https://msnr.net/2021/02/mysql_itaiji.html MySQLでの異体字対応検索 - まぁそれなりに ねっと])。
-* データファイルのコピー
-基本的にはmysqldumpコマンドを使ってデータをSQLのテキストファイルとして出力する。
+===== 設定・変更 =====
+[https://web.gnusocial.jp/post/2023/05/13/6804/ MariaDBのDBの文字コードと照合順の変更 | GNU social JP Web]
-[https://dev.mysql.com/doc/refman/8.0/ja/mysqldump.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム]
+指定したDBの文字コード・照合順の情報は以下のコマンド・SQLで確認可能です。
+ DB_USER=root
+ DB_NAME=mysql
+ mysql -u $DB_USER -p -e \
+ 'SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
+     FROM INFORMATION_SCHEMA.SCHEMATA;'
-全データベース、データベース、テーブル単位のそれぞれでバックアップできる。
+ +--------------------+----------------------------+------------------------+
+ | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
-全データベースのバックアップには--all-databasesを指定する。
+ +--------------------+----------------------------+------------------------+
- mysqldump -p -u <username> --all-databases >mysql.sql
+ | information_schema | utf8                       | utf8_general_ci        |
-  mysqldump -p -u <username> <db_name> [table_name ...]
+ | performance_schema | utf8                       | utf8_general_ci        |
-復元はmysqlコマンドでSQLを読み込めばいい。
+ | mysql              | utf8mb4                    | utf8mb4_general_ci     |
-  mysql -p -u username <mysql.sql
+  +--------------------+----------------------------+------------------------+
-  mysql -p -u username database_name <file.sql
+設定は以下で行えます。
+ DB_USER=root
+ DB_NAME=mysql
+ ## MariaDB
+  mysql -u $DB_USER -p -e \
+  "ALTER DATABASE \`$DB_NAME\` DEFAULT CHARACTER SET 'utf8mb4' DEFAULT COLLATE 'utf8mb4_bin';"
+ ## MySQL
+  mysql -u $DB_USER -p -e \
+   "ALTER DATABASE $DB_NAME DEFAULT CHARACTER SET 'utf8mb4' DEFAULT COLLATE 'utf8mb4_0900_bin';"
-=== Error ===
+=== Other ===
-==== Error: [PDOException] PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] ====
+==== データベース一覧 ====
-* [https://www.petitmonte.com/database/mysql_authentication_plugin.html MySQL8.0以降で接続できない場合は「認証プラグイン」を変更する]
+ show databases;
-* [https://symfoware.blog.fc2.com/blog-entry-2160.html phpからMySQL 8.0へPDOで接続時「SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client」 - Symfoware]
-* [https://yoku0825.blogspot.com/2018/01/mysql-804.html 日々の覚書: MySQL 8.0.4におけるデフォルト認証形式の変更]
-* [https://qiita.com/ucan-lab/items/b094dbfc12ac1cbee8cb docker-compose MySQL8.0 のDBコンテナを作成する #Docker - Qiita]
-MySQL 8.0になってから、認証方式が変わったらしく、そのままだと接続に失敗する。
+==== テーブルの検索 ====
+https://chatgpt.com/c/6789de8b-11ac-800b-bb21-45ba3735aa3f
-PHP 7.1.16, 7.2.4 以降から対応しているらしい。それ以前だと設定が必要。
+ SELECT TABLE_SCHEMA, TABLE_NAME
+ FROM information_schema.TABLES
+ WHERE TABLE_NAME LIKE '%検索するテーブル名%'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME;
+information_schemaから検索できる。
+==== testデータベース ====
+https://chatgpt.com/c/67b686cc-fdc0-800b-9538-03d86cf046de
-==== Numeric value out of range: 1264 Out of range value for column ====
+MySQL 5.7までMySQLインストール時に、自動的にtestデータベースが作られる。MySQL 8.0からは自動では作られない。
-[https://qiita.com/mindwood/items/9a47dfabd0c5cebf1a62 "Out of range value for column 'カラム名' at 行番号" に対処した #MySQL - Qiita]
-エラーコード 1264 は、データ型の範囲を超えた値をINSERTしたときに発生するWarningである。直前に実行したSQLのWarningは SHOW WARNINGS で確認できる。
+テスト用のデータベースで全ユーザーがアクセス可能。
-=== Config ===
+本番では削除が推奨される。phpmyadminなどのツールのデータ保存用に使うと良い。
-==== Character ====
+==== 予約語 ====
-[https://qiita.com/tfunato/items/e48ad0a37b8244a788f6 MySQLの文字コードとCollation #MySQL - Qiita]
-DBで使用する文字セットと、その照合順序を選択できる。
+* https://chatgpt.com/c/67ce4824-41f8-800b-b7d0-ff41365b5d91
+* [https://dev.mysql.com/doc/refman/8.0/ja/keywords.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 9.3 キーワードと予約語]
+* [https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0170 第170回　MySQLのキーワードと予約語をテーブルに使用したい場合 | gihyo.jp]
-文字コードはutf8mb4で問題ない。照合順序も基本はutf8mb4_binで問題ないと思う。
+MySQLは組込関数がたくさんあり、それらが全て予約語。以下でも確認できる。
+ select * from information_schema.KEYWORDS;
+予約語をカラム名などに使う場合、バッククオートかANSIモードで二重引用符でエスケープが必要。
-A ≠ a 区別する
+ただ、だからといって全部にエスケープすると可読性が悪い。変数や予約語だけエスケープするのがいい模様。
-≠  区別する
+* rank
-は≠ぱ≠ば 区別する
+== Oracle ==
-utf8mb4_general_ciは上2個を区別しない。
+=== 第I部 Oracleリレーショナル・データ構造 ===
-区別しないとあいまい検索で都合がいいことがある。
+==== 2 表と表クラスタ ====
+[https://docs.oracle.com/cd/F19136_01/cncpt/tables-and-table-clusters.html 表と表クラスタ]
-ただし、あいまい検索にすると、対象範囲が増えるので、少し遅くなる模様 ([https://yakst.com/ja/posts/5431 CharsetとCollationの設定がMySQLのパフォーマンスに与える影響 | Yakst])。
+===== 2.2. 表の概要 =====
-==== SQL mode ====
+====== 2.2.4. Oracleデータ型 ======
-[https://dev.mysql.com/doc/refman/8.0/ja/sql-mode.html#sql-mode-combo MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.11 サーバー SQL モード]
+数値データ型
-SQLの基本的な動きを制御する設定がある。以下で現在値を確認。
+* NUMBER
- SELECT @@GLOBAL.sql_mode;
+* 浮動小数点数
- SELECT @@SESSION.sql_mode;
-以下で設定。
- SET GLOBAL sql_mode = 'modes';
- SET SESSION sql_mode = 'modes';
-GLOBALは権限が必要。SESSIONは誰でも変更可能。
-デフォルト=ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO および NO_ENGINE_SUBSTITUTION。
+NUMBERデータ型
-カンマで区切って複数指定できる。
+固定小数点数と浮動小数点数を格納可能。どんな大きさの数値でも格納可能。
-モードの値としては、ANSIが重要。基本はこれでよい。
+固定小数点数は NUMBER[(<nowiki><p>[, <s>])] の形式で指定。</nowiki>
-==== Collation ====
+* p=精度: 全体の桁数。精度を指定しない場合、値は丸めずに格納。
-[https://qiita.com/kazu56/items/6af85ffcf8d3954455ad 【MySQL】照合順序とは？ #MySQL - Qiita]
+* s=scale: 小数点から最下位有効桁までの桁数。省略すると0になる。
-[https://dev.mysql.com/doc/refman/8.4/en/charset-collation-names.html MySQL :: MySQL 8.4 Reference Manual :: 12.3.1 Collation Naming Conventions]
+https://grok.com/share/c2hhcmQtMw%3D%3D_68537ced-0913-4a0c-a788-df06a1afb8ec
-utf8_general_ciのようなcollate (照合順序) がある。これの意味。
+NUMBER型で精度を省略すると最大38桁の任意精度の数値を格納する。
-書式 [文字集合]_[言語名]_[比較法]
+データの範囲や精度が事前に不明な場合、事前に決められない場合、将来の変化の可能性が高い場合。
-* 文字集合: character set
+基本は精度を指定する。意図しないデータを抑制できる。
-* 言語名: general=マルチリンガル。0900=UCA 9.0.0。bin=バイナリ比較。バイト単位で比較する。大文字小文字やアクセントの違いなどを考慮する。
-* 比較法: ai=アクセント無視 (はとぱは同一)/as=アクセント区別/ci=大文字小文字無視 (case ignore)/cs=大文字小文字区別 (case sensitive)/ks=カナ区別/bin=バイナリー。
-===== default =====
+==== 6 データ・ディクショナリと動的パフォーマンス・ビュー ====
-mysqlのデフォルトはutf8_general_ci。
+[https://docs.oracle.com/cd/F19136_01/cncpt/data-dictionary-and-dynamic-performance-views.html データ・ディクショナリと動的パフォーマンス・ビュー]
-* general:
+===== About =====
-* unicode: 全角半角を区別しない。
+Oracle Databaseの重要な部分。DBに関する管理メタデータの読取専用の表の集合。以下のような情報を含んでいる。
-絵文字の扱いなどが変わってくる。
+* 列のデフォルト値、整合性制約情報など、データベース内の各[https://docs.oracle.com/cd/F19136&#x20;01/cncpt/Chunk505218552.html#GUID-EC18AB9A-E51B-4B90-941C-44F0C35851F5 スキーマ・オブジェクト]の定義
+* スキーマ・オブジェクトに割り当てられている領域、およびスキーマ・オブジェクトによって現在使用されている領域の容量
+* Oracle Databaseユーザーの名前、ユーザーに付与された権限とロール、およびユーザーに関連する監査情報
-* MySQL 8.0=utf8mb4/utf8mb4_0900_ai_ci ([https://dev.mysql.com/doc/refman/8.0/en/charset-server.html MySQL :: MySQL 8.0 Reference Manual :: 12.3.2 Server Character Set and Collation]/[https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html MySQL :: MySQL 8.0 Reference Manual :: 12.2 Character Sets and Collations in MySQL])
+データが表に入っているため、SQLで閲覧可能。実表とビューで構成されており、実表はDBが内部的に扱うだけで、ユーザーはビューにアクセスするのが基本。
-* MySQL 5.7=latin1/latin1_swedish_ci ([https://dev.mysql.com/doc/refman/5.7/en/charset-server.html MySQL :: MySQL 5.7 Reference Manual :: 10.3.2 Server Character Set and Collation])
-* MySQL 5.7=utf8mb4/utf8mb4_general_ci ([https://dev.mysql.com/doc/refman/5.7/en/charset-mysql.html MySQL :: MySQL 5.7 Reference Manual :: 10.2 Character Sets and Collations in MySQL])
-===== utf8mb4_bin/utf8mb4_0900_bin =====
+データ・ディクショナリーは、アクセス権限に応じて、以下の接頭辞で同じ内容のものがグループ化されている。
-* [https://blog.siwa32.com/mysql_collation/ MySQL8のutf8mb4におけるCollation比較 - BLOG - siwa32.com]
+表6-1 データ・ディクショナリ・ビューのセット
-* [https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.17 (2019-07-22, General Availability)]
+{| class="wikitable"
-* https://chatgpt.com/c/674ce499-dcd0-800b-bb9c-42a8fbe21805
+!接頭辞
+!ユーザー・アクセス
+!内容
+!ノート
+|-
+|<code>DBA_</code>
+|データベース管理者
+|すべてのオブジェクト
+|一部の<code>DBA_</code>ビューには、管理者にとって有用な情報を含む列が追加されています。
+|-
+|<code>ALL_</code>
+|すべてのユーザー
+|ユーザーが権限を持つオブジェクト
+|ユーザーが所有するオブジェクトが含まれています。これらのビューは有効化されている現在の一連のロールに従います。
+|-
+|<code>USER_</code>
+|すべてのユーザー
+|ユーザーによって所有されているオブジェクト
+|接頭辞が<code>USER_</code>のビューには、通常、列<code>OWNER</code>は含まれません。この列は、問合せを発行するユーザーとして、<code>USER_</code>ビューに暗黙的に含まれています。
+|}
+必ず3セットあるわけではない。
+システム提供のDICTIONARYビューに、全データ・ディクショナリ・ビューの名前と短い説明がある。
+ SELECT * FROM DICTIONARY  ORDER BY TABLE_NAME;
+===== ALL_TAB_COLUMNS =====
+* [https://it.notepad-blog.com/tool/db/154/ 【Oracle】テーブル定義情報を確認する方法（テーブル名やカラム名、データ型で検索） - ITメモブログ]
+* [https://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_2103.htm#sthref1589 ALL_TAB_COLUMNS]
+* [https://stellacreate.com/entry/oracle-select-all-column 【Oracle】データベース内の全てのカラム一覧を取得するSQL | StellaCreate]
+* [https://segakuin.com/oracle/ddv/all_tab_columns.html ALL_TAB_COLUMNS, DBA_TAB_COLUMNS, USER_TAB_COLUMNS 列データディクショナリビュー]
+Oracle DBでは、以下のデータディクショナリービューを参照することで、テーブル定義を確認できる。
+{| class="wikitable"
+|テーブル名
+|スコープ
+|-
+|USER_TAB_COLUMNS
+|ログインユーザーのカラム一覧
+|-
+|ALL_TAB_COLUMNS
+|ログインユーザーがアクセスできるすべてのカラム一覧
+|-
+|DBA_TAB_COLUMNS
+|データベース内のすべてのカラム一覧
+|}
+スコープが違うのみ。基本はALL_TAB_COLUMNSを使うことになる。
+以下のSQLでテーブル定義を確認可能。
+ SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'SAMPLE_TABLE';
+列に対して、その統計情報が以下で記載される。
+{| class="wikitable"
+!列
+!データ型
+!NULL
+!説明
+|-
+|<code>OWNER</code>
+|<code>VARCHAR2(30)</code>
+|<code>NOT NULL</code>
+|表、ビューまたはクラスタの所有者
+|-
+|<code>TABLE_NAME</code>
+|<code>VARCHAR2(30)</code>
+|<code>NOT NULL</code>
+|表、ビューまたはクラスタの名前
+|-
+|<code>COLUMN_NAME</code>
+|<code>VARCHAR2(30)</code>
+|<code>NOT NULL</code>
+|列名
+|-
+|<code>DATA_TYPE</code>
+|<code>VARCHAR2(106)</code>
+|
+|列のデータ型
+|-
+|<code>DATA_TYPE_MOD</code>
+|<code>VARCHAR2(3)</code>
+|
+|列のデータ型修飾子
+|-
+|<code>DATA_TYPE_OWNER</code>
+|<code>VARCHAR2(30)</code>
+|
+|列のデータ型の所有者
+|-
+|<code>DATA_LENGTH</code>
+|<code>NUMBER</code>
+|<code>NOT NULL</code>
+|列の長さ(バイト)
+|-
+|<code>DATA_PRECISION</code>
+|<code>NUMBER</code>
+|
+|<code>NUMBER</code>データ型の場合は10進精度。<code>FLOAT</code>データ型の場合は2進精度。その他のデータ型の場合はNULL。
+|-
+|<code>DATA_SCALE</code>
+|<code>NUMBER</code>
+|
+|数値の小数点以下の桁
+|-
+|<code>NULLABLE</code>
+|<code>VARCHAR2(1)</code>
+|
+|列にNULLを指定できるかどうかを示す。列に<code>NOT NULL</code>制約がある場合、または列が<code>PRIMARY KEY</code>の一部である場合、値は<code>N</code>となる。この制約は、<code>ENABLE VALIDATE</code>状態である必要がある。
+|-
+|<code>COLUMN_ID</code>
+|<code>NUMBER</code>
+|
+|作成された列の順序番号
+|-
+|<code>DEFAULT_LENGTH</code>
+|<code>NUMBER</code>
+|
+|列のデフォルト値の長さ
+|-
+|<code>DATA_DEFAULT</code>
+|<code>LONG</code>
+|
+|列のデフォルト値
+|-
+|<code>NUM_DISTINCT</code>
+|<code>NUMBER</code>
+|
+|列内で異なる値の数[[/docs.oracle.com/cd/E16338 01/server.112/b56311/statviews 2103.htm#BEICJFIG|<sup>脚注 1</sup>]]
+|-
+|<code>LOW_VALUE</code>
+|<code>RAW(32)</code>
+|
+|列内の下限値[[/docs.oracle.com/cd/E16338 01/server.112/b56311/statviews 2103.htm#sthref1589|<sup>脚注 1</sup>]]
+|-
+|<code>HIGH_VALUE</code>
+|<code>RAW(32)</code>
+|
+|列内の上限値[[/docs.oracle.com/cd/E16338 01/server.112/b56311/statviews 2103.htm#sthref1590|<sup>脚注 1</sup>]]
+|-
+|<code>DENSITY</code>
+|<code>NUMBER</code>
+|
+|<code>COLUMN_NAME</code>に対してヒストグラムが使用可能な場合、この列にはヒストグラム内の1つ以下のエンドポイントにわたる値の選択性が示される。複数のエンドポイントにわたる値の選択性は示されない。
+<code>COLUMN_NAME</code>に対してヒストグラムが使用可能ではない場合、この列の値は<code>1</code>/<code>NUM_DISTINCT</code>である。[[/docs.oracle.com/cd/E16338 01/server.112/b56311/statviews 2103.htm#sthref1591|<sup>脚注 1</sup>]]
+|-
+|<code>NUM_NULLS</code>
+|<code>NUMBER</code>
+|
+|列内のNULLの数
+|-
+|<code>NUM_BUCKETS</code>
+|<code>NUMBER</code>
+|
+|列のヒストグラム内のバケット数
+注意: ヒストグラム内のバケット数は、SQL文<code>ANALYZE</code>の<code>SIZE</code>パラメータに指定されます。ただし、サンプル内の行数より多いバケットを持つヒストグラムは作成されません。また、サンプルに繰返しが非常に多い値が含まれる場合、指定された数のバケットは作成されますが、この列で指定した値は、内部圧縮アルゴリズムのために小さくなる場合があります。
+|-
+|<code>LAST_ANALYZED</code>
+|<code>DATE</code>
+|
+|この列が分析された最新の日付
+|-
+|<code>SAMPLE_SIZE</code>
+|<code>NUMBER</code>
+|
+|この列の分析で使用されたサンプル・サイズ
+|-
+|<code>CHARACTER_SET_NAME</code>
+|<code>VARCHAR2(44)</code>
+|
+|キャラクタ・セットの名前
+* <code>CHAR_CS</code>
+* <code>NCHAR_CS</code>
+|-
+|<code>CHAR_COL_DECL_LENGTH</code>
+|<code>NUMBER</code>
+|
+|キャラクタ・タイプ列の宣言の長さ
+|-
+|<code>GLOBAL_STATS</code>
+|<code>VARCHAR2(3)</code>
+|
+|パーティション表の場合、表全体を収集した列統計情報なのか(<code>YES</code>)、基礎となるパーティションおよびサブパーティションの統計情報から推定されたものなのか(<code>NO</code>)
+|-
+|<code>USER_STATS</code>
+|<code>VARCHAR2(3)</code>
+|
+|統計情報が、ユーザーによって直接入力されたか(<code>YES</code>)されていないか(<code>NO</code>)
+|-
+|<code>AVG_COL_LEN</code>
+|<code>NUMBER</code>
+|
+|列の平均の長さ(バイト)
+|-
+|<code>CHAR_LENGTH</code>
+|<code>NUMBER</code>
+|
+|列の長さが文字単位で表示されます。この値は、次のデータ型のみに適用される。
+* <code>CHAR</code>
+* <code>VARCHAR2</code>
+* <code>NCHAR</code>
+* <code>NVARCHAR2</code>
+|-
+|<code>CHAR_USED</code>
+|<code>VARCHAR2(1)</code>
+|
+|列がバイトの長さセマンティクス(<code>B</code>)を使用するか、文字の長さセマンティクス(<code>C</code>)を使用するか、またはデータ型が次のいずれでもない(NULL)ことを示す
+* <code>CHAR</code>
+* <code>VARCHAR2</code>
+* <code>NCHAR</code>
+* <code>NVARCHAR2</code>
+|-
+|<code>V80_FMT_IMAGE</code>
+|<code>VARCHAR2(3)</code>
+|
+|列データがリリース8.0のイメージ形式であるかどうか(<code>YES</code><nowiki> | </nowiki><code>NO</code>)
+|-
+|<code>DATA_UPGRADED</code>
+|<code>VARCHAR2(3)</code>
+|
+|列データが最新のタイプ・バージョン形式にアップグレードされたかどうか(<code>YES</code><nowiki> | </nowiki><code>NO</code>)
+|-
+|<code>HISTOGRAM</code>
+|<code>VARCHAR2(15)</code>
+|
+|ヒストグラムの有無およびタイプ:
+* <code>NONE</code>
+* <code>FREQUENCY</code>
+* <code>HEIGHT BALANCED</code>
+|}
+=== データベース・リファレンス ===
+==== 6 静的データ・ディクショナリ・ビュー ====
+===== 制約 =====
+====== ALL_CONSTRAINTS ======
+[https://docs.oracle.com/cd/F19136_01/refrn/ALL_CONSTRAINTS.html ALL_CONSTRAINTS]
+表の制約定義を確認する。
+ SELECT * FROM ALL_CONSTRAINTS
+ WHERE TABLE_NAME = 'T_MEMBERS_DCP_ACTION';
+主キーとか一意キーの有無を確認できる。
+特に以下が重要。
+* CONSTRAINT_NAME
+* CONSTRAINT_TYPE
+* INDEX_NAME
+{| class="wikitable"
+!列
+!データ型
+!NULL
+!説明
+|-
+|<code>OWNER</code>
+|<code>VARCHAR2(128)</code>
+|
+|制約定義の所有者
+|-
+|<code>CONSTRAINT_NAME</code>
+|<code>VARCHAR2(128)</code>
+|
+|制約定義の名前
+|-
+|<code>CONSTRAINT_TYPE</code>
+|<code>VARCHAR2(1)</code>
+|
+|制約定義のタイプ
+* <code>C</code> - 表でのチェック制約
+* <code>P</code> - 主キー
+* <code>U</code> - 一意のキー
+* <code>R</code> - 参照整合性
+* <code>V</code> - ビューでのチェック・オプション付き
+* <code>O</code> - ビューで読取り専用
+* <code>H</code> - ハッシュ式
+* <code>F</code> - REF列を含む制約
+* <code>S</code> - サプリメンタル・ロギング
+|-
+|<code>TABLE_NAME</code>
+|<code>VARCHAR2(128)</code>
+|
+|制約定義付きの表(またはビュー)に対応付けられた名前
+|-
+|<code>SEARCH_CONDITION</code>
+|<code>LONG</code>
+|
+|チェック制約に対する検索条件のテキスト。この列は、行の発生元が現在のコンテナの場合のみ正しい値を返す。
+|-
+|<code>SEARCH_CONDITION_VC</code>
+|<code>VARCHAR2(4000)</code>
+|
+|チェック制約に対する検索条件のテキスト。この列では検索条件が切り捨てられる場合がある。
+|-
+|<code>R_OWNER</code>
+|<code>VARCHAR2(128)</code>
+|
+|参照制約で参照される表の所有者
+|-
+|<code>R_CONSTRAINT_NAME</code>
+|<code>VARCHAR2(128)</code>
+|
+|参照表の一意制約の定義名
+|-
+|<code>DELETE_RULE</code>
+|<code>VARCHAR2(9)</code>
+|
+|参照制約の削除ルール
+* <code>CASCADE</code>
+* <code>SET NULL</code>
+* <code>NO ACTION</code>
+|-
+|<code>STATUS</code>
+|<code>VARCHAR2(8)</code>
+|
+|制約の施行状態
+* <code>ENABLED</code>
+* <code>DISABLED</code>
+|-
+|<code>DEFERRABLE</code>
+|<code>VARCHAR2(14)</code>
+|
+|制約が遅延可能か(<code>DEFERRABLE</code>)そうでないか(<code>NOT DEFERRABLE</code>)
+|-
+|<code>DEFERRED</code>
+|<code>VARCHAR2(9)</code>
+|
+|制約が初期状態から遅延されていたか(<code>DEFERRED</code>)そうでないか(<code>IMMEDIATE</code>)
+|-
+|<code>VALIDATED</code>
+|<code>VARCHAR2(13)</code>
+|
+|<code>STATUS</code> = <code>ENABLED</code>の場合、可能な値は次のとおり。
+* <code>VALIDATED</code> - すべてのデータは制約に従っている(つまり、制約が有効にされたとき、および後続のデータが表に入力されたときに表内の既存のデータが検証されている)
+* <code>NOT</code> <code>VALIDATED</code> - 制約に従っていないデータがある可能性がある(つまり、制約が有効にされたときに表内の既存のデータは検証されなかったが、表に入力された後続のデータは検証されている)
+<code>STATUS</code> = <code>DISABLED</code>の場合、可能な値は次のとおり。
+* <code>VALIDATED</code>: すべてのデータは制約に従っているが、制約の一意索引は削除されている。この設定は、データ・ウェアハウス環境において有用であるが、いくつかの制限事項がある。この設定の詳細は、を参照。
+* <code>NOT</code> <code>VALIDATED</code> - 制約に従っていないデータがある可能性がある
+|-
+|<code>GENERATED</code>
+|<code>VARCHAR2(14)</code>
+|
+|制約の名前がユーザーにより生成されたか(<code>USER NAME</code>)システムにより生成されたか(<code>GENERATED NAME</code>)
+|-
+|<code>BAD</code>
+|<code>VARCHAR2(3)</code>
+|
+|この制約があいまいな方法で世紀を指定するか(<code>BAD</code>)そうでないか(NULL)。この不明瞭さから生じるエラーを回避するには、<code>TO_DATE</code>ファンクションで4桁の年を使用して制約を再度書き込みます。
+関連項目: <code>TO_DATE</code>ファンクションは、およびを参照。
+|-
+|<code>RELY</code>
+|<code>VARCHAR2(4)</code>
+|
+|<code>VALIDATED</code> = <code>NOT</code> <code>VALIDATED</code>の場合、この列は、クエリー・リライトのときに制約が考慮されるか(<code>RELY</code>)されないか(NULL)を示す。
+<code>VALIDATED</code> = <code>VALIDATED</code>の場合、この列は意味を持たない。
+関連項目: の制約に関する項を参照。
+|-
+|<code>LAST_CHANGE</code>
+|<code>DATE</code>
+|
+|制約が最後に使用可能または使用禁止にされた時点
+|-
+|<code>INDEX_OWNER</code>
+|<code>VARCHAR2(128)</code>
+|
+|索引を所有しているユーザーの名前
+|-
+|<code>INDEX_NAME</code>
+|<code>VARCHAR2(128)</code>
+|
+|索引の名前(一意および主キー制約の場合のみ表示)
+|-
+|<code>INVALID</code>
+|<code>VARCHAR2(7)</code>
+|
+|制約が無効か(<code>INVALID</code>)そうでないか(NULL)
+|-
+|<code>VIEW_RELATED</code>
+|<code>VARCHAR2(14)</code>
+|
+|制約がビューによって異なるか(<code>DEPEND ON VIEW</code>)そうでないか(NULL)
+|-
+|<code>ORIGIN_CON_ID</code>
+|<code>VARCHAR2(256)</code>
+|
+|データの発生元のコンテナのID。可能な値は次のとおり。
+* <code>0</code>: この値は、非CDB内の行に使用される。この値はCDBには使用されない。
+* n: この値は、コンテナID n (行の発生元がルートの場合はn = 1)のコンテナを発生元とするデータを含む行に対して使用される。
+|}
+====== ALL_CONS_COLUMNS ======
+[https://docs.oracle.com/cd/F19136_01/refrn/ALL_CONS_COLUMNS.html#GUID-5B08133E-40D0-4A9E-9033-BFCC1493BA3F ALL_CONS_COLUMNS]
+制約の指定列を確認する。
+ SELECT *
+ FROM ALL_CONS_COLUMNS
+ WHERE TABLE_NAME = 'T_MEMBERS_DCP_ACTION';
+===== 権限 =====
+====== 特定テーブルへの権限の確認方法 ======
+https://grok.com/share/c2hhcmQtMw%3D%3D_4f7d1813-7b1c-497e-975a-63042de54f40
+テーブルとロールで権限が付与されている。両方確認が必要。
+ -- ロールの権限
+ SELECT * FROM USER_SYS_PRIVS;
+ -- テーブル単位の権限
+ SELECT privilege
+ FROM dba_tab_privs
+ WHERE grantee = USER
+ AND table_name = 'テーブル名';
+====== SYS_PRIVS ======
+ユーザー・ロールに付与されたシステム権限。
+* [https://docs.oracle.com/cd/F19136_01/refrn/DBA_SYS_PRIVS.html#GUID-384E578D-6506-4419-B943-612A7CF3465F DBA_SYS_PRIVS]
+* [https://docs.oracle.com/cd/F19136_01/refrn/USER_SYS_PRIVS.html#GUID-8DEB9EB1-71F4-4FB2-9643-EED53259F3C3 USER_SYS_PRIVS]
+システム権限付与を確認できる。
+ SELECT * FROM USER_SYS_PRIVS;
+{| class="wikitable"
+!USERNAME
+!PRIVILEGE
+!ADMIN_OPTION
+!COMMON
+!INHERITED
+|-
+|WEB
+|EXECUTE ANY PROCEDURE
+|NO
+|NO
+|NO
+|-
+|WEB
+|ALTER SESSION
+|NO
+|NO
+|NO
+|-
+|WEB
+|SELECT ANY DICTIONARY
+|NO
+|NO
+|NO
+|-
+|WEB
+|CREATE MATERIALIZED VIEW
+|NO
+|NO
+|NO
+|-
+|WEB
+|CREATE PROCEDURE
+|NO
+|NO
+|NO
+|-
+|WEB
+|CREATE TABLE
+|NO
+|NO
+|NO
+|-
+|WEB
+|CREATE SESSION
+|NO
+|NO
+|NO
+|-
+|WEB
+|CREATE TRIGGER
+|NO
+|NO
+|NO
+|-
+|WEB
+|SELECT ANY TABLE
+|NO
+|NO
+|NO
+|}
+====== TAB_PRIVS ======
+データベース内のすべてのオブジェクトについての権限付与を示します。
+* [https://docs.oracle.com/cd/F19136_01/refrn/DBA_TAB_PRIVS.html DBA_TAB_PRIVS]
+* [https://docs.oracle.com/cd/F19136_01/refrn/USER_TAB_PRIVS.html#GUID-C0EAE1FC-AAA6-4846-8009-35189BD72F2D USER_TAB_PRIVS]
+{| class="wikitable"
+!列
+!データ型
+!NULL
+!説明
+|-
+|<code>GRANTEE</code>
+|<code>VARCHAR2(128)</code>
+|
+|アクセス権を付与されたユーザーまたはロールの名前
+|-
+|<code>OWNER</code>
+|<code>VARCHAR2(128)</code>
+|
+|オブジェクトの所有者
+|-
+|<code>TABLE_NAME</code>
+|<code>VARCHAR2(128)</code>
+|
+|オブジェクト名。オブジェクトには、表、パッケージ、索引、順序など、任意のオブジェクトを設定できる。
+|-
+|<code>GRANTOR</code>
+|<code>VARCHAR2(128)</code>
+|
+|権限付与を実行したユーザー名
+|-
+|<code>PRIVILEGE</code>
+|<code>VARCHAR2(40)</code>
+|
+|オブジェクトについての権限
+|-
+|<code>GRANTABLE</code>
+|<code>VARCHAR2(3)</code>
+|
+|権限が<code>GRANT OPTION</code>付きで付与されたか(<code>YES</code>)されていないか(<code>NO</code>)
+|-
+|<code>HIERARCHY</code>
+|<code>VARCHAR2(3)</code>
+|
+|権限が<code>HIERARCHY OPTION</code>付きで付与されたか(<code>YES</code>)されていないか(<code>NO</code>)
+|-
+|<code>COMMON</code>
+|<code>VARCHAR2(3)</code>
+|
+|権限がどのように付与されたかを示します。可能な値は次のとおり。
+* <code>YES</code>: 権限が共通して付与された場合(<code>CONTAINER=ALL</code>が使用された場合)
+* <code>NO</code>: 権限がローカルで付与された場合(<code>CONTAINER=ALL</code>が使用されなかった場合)
+|-
+|<code>TYPE</code>
+|<code>VARCHAR2(24)</code>
+|
+|オブジェクトのタイプ
+|-
+|<code>INHERITED</code>
+|<code>VARCHAR2(3)</code>
+|
+|権限付与が別のコンテナから継承されているかどうか(<code>YES</code><nowiki> | </nowiki><code>NO</code>)
+|}
+=== SQL言語リファレンス ===
+[https://docs.oracle.com/cd/F19136_01/sqlrf/toc.htm 目次]
+==== 11 エラー処理 ====
+[https://docs.oracle.com/cd/F19136_01/lnpls/plsql-error-handling.html PL/SQLのエラー処理]
+===== 11.2 例外処理の概要 =====
+すべてのPL/SQLブロックに例外処理部を配置でき、ここに1以上の例外ハンドラーを配置できる。例えば、以下の構文を使用する。
+ EXCEPTION
+   WHEN ex_name_1 THEN statements_1                 -- Exception handler
+   WHEN ex_name_2 OR ex_name_3 THEN statements_2  -- Exception handler
+   WHEN OTHERS THEN statements_3                      -- Exception handler
+ END;
+ex_name_nが例外の名前。statements_nは1以上の文。ブロックの実行部で例外が発生すると、実行部が中止し、例外処理部に制御が移る。
+それ以外はstatements_3が実行される。
+===== 11.10 エラー・コードとエラー・メッセージの取得 =====
+例外ハンドラ内で、処理中の例外について以下ができる。
+* SQLCODEを使用してエラー・コードを取得。
+* 以下のどちらからでエラー・メッセージを取得。
+** SQLERRM
+** DBMS_UTILITY.FORMAT_ERROR_STACK
+例11-23 SQLCODEおよびSQLERRMの値の表示
+ DROP TABLE errors;
+ CREATE TABLE errors (
+   code      NUMBER,
+   message   VARCHAR2(64)
+ );
+ CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
+   name    EMPLOYEES.LAST_NAME%TYPE;
+   v_code  NUMBER;
+   v_errm  VARCHAR2(64);
+ BEGIN
+   SELECT last_name INTO name
+   FROM EMPLOYEES
+   WHERE EMPLOYEE_ID = -1;
+ EXCEPTION
+   WHEN OTHERS THEN
+     v_code := SQLCODE;
+     v_errm := SUBSTR(SQLERRM, 1, 64);
+     DBMS_OUTPUT.PUT_LINE
+       ('Error code ' || v_code || ': ' || v_errm);
+     /* Invoke another procedure,
+        declared with PRAGMA AUTONOMOUS_TRANSACTION,
+        to insert information about errors. */
+     INSERT INTO errors (code, message)
+     VALUES (v_code, v_errm);
+     RAISE;
+ END;
+==== DDL ====
+===== ALTER TABLE =====
+[https://docs.oracle.com/cd/F19136_01/sqlrf/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877 ALTER TABLE]
+==== Other ====
+===== バージョン確認 =====
+ SELECT * FROM V$VERSION
+ BANNER,BANNER_FULL,BANNER_LEGACY,CON_ID
+ Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production	"Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
+ Version 19.21.0.0.0"	Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production	0
+===== LIMIT =====
+[https://www.earthlink.co.jp/engineerblog/technology-engineerblog/2562/ PostgreSQLやMySQLで使えるLimit句をOracle SQLで使う方法 | 株式会社アースリンク]
+oracleではlimitがない。
+ SELECT * FROM Test_Emp LIMIT 5;
+ SELECT * FROM Test_Emp WHERE ROWNUM <= 5;
+rownumの疑似列を使う。
+ただしこのROWNUMはORDER BY句で正しくソートすることができません。
+ROWNUMで取得した結果に対してソートを行うので、
+LIMIT句の様にソートしてからの取得ができないからです。
+ROWNUM擬似列で行数制限しながらソートしたい場合
+なので、結果に対してソートを行いたい場合、
+副問合せをしてその中でORDER BY句を使用します。
+ SELECT * FROM
+ (SELECT * FROM Test_Emp ORDER BY Emp_No DESC)
+ WHERE ROWNUM <= 5
+=== データベースPL/SQL言語リファレンス ===
+==== 6 PL/SQLの静的SQL ====
+[https://docs.oracle.com/cd/F19136_01/lnpls/static-sql.html PL/SQLの静的SQL]
+===== 6.2 カーソルの概要 =====
+カーソルは特定のSELECT文などの処理の情報を格納しておくポインター。
+SELECTの結果を格納しておいて、1件ずつ処理するときに使う。配列みたいなもの。
+暗黙のカーソルと明示カーソルがある。
+====== 6.2.1 暗黙カーソル ======
+SELECT文を実行すると自動的に作られるカーソル。SQLカーソルとも呼ばれる。文の実行後に自動的にクローズされる。
+====== 6.2.2 明示カーソル ======
+ユーザーが明示的に構築・管理するカーソル。明示カーソルを宣言・定義して、その後にSELECT文と関連付けて使う。
+OPEN文で明示カーソルオープンして、FETCH文でフェッチして、CLOSE文でクローズする。
+明示カーソルの宣言は以下。
+ CURSOR cursor_name [ parameter_list ] RETURN return_type;
+宣言+定義は以下。
+ CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
+   IS select_statement;
+=== PL/SQLパッケージおよびタイプ・リファレンス ===
+[https://docs.oracle.com/cd/F19136_01/arpls/toc.htm 目次]
+==== 出力 ====
+===== DBMS_OUTPUT =====
+[https://docs.oracle.com/cd/F19136_01/arpls/DBMS_OUTPUT.html DBMS_OUTPUT]
+デバッグ用パッケージ。
+ DBMS_OUTPUT.PUT_LINE('I got here:'||:new.col||' is the new value');
+SQL上でのechoコマンド相当。
+=== JSON Developer's Guide ===
+==== 第V部 JSONデータの生成 ====
+===== 23 SQL/JSONファンクションを使用したJSONデータの生成 =====
+* [https://docs.oracle.com/cd/F19136_01/adjsn/generation.html SQL/JSONファンクションを使用したJSONデータの生成]
+* [https://qiita.com/kenwatan/items/1fcd545001da4c2d9368 SQLをつかってJSONデータ生成（Oracle12c R2 新機能 SQL/JSON関数） #oracle - Qiita]
+いくつか方法がある。
+* 方式
+** OBJECT: JSONオブジェクトを作成。
+** ARRAY: JSON配列を作成。
+** AGG: SQLの副問い合わせの結果から作成。AGGを使わない場合、プロパティーは引数に依存。
+* 関数
+** JSON_OBJECT
+** JSON_OBJECTAGG
+** JSON_ARRAY
+** JSON_ARRAYAGG
+====== JSON_OBJECT ======
+種類の書き方がある。
+ JSON_OBJECT('key1' VALUE 'value1', 'key2' VALUE 'value2', ...)
+ JSON_OBJECT('key1':'value1', 'key2':'value2', ...)
+keyの部分は文字列で固定。valueの部分は型は柔軟。
+ SELECT json_object('name'          VALUE first_name || ' ' || last_name,
+ <nowiki> </nowiki>                  'hasCommission' VALUE
+ <nowiki> </nowiki>                    CASE WHEN commission_pct IS NULL THEN 'false' ELSE 'true'
+ <nowiki> </nowiki>                    END FORMAT JSON)
+ <nowiki> </nowiki> FROM employees WHERE first_name LIKE 'W%';
+ JSON_OBJECT('NAME'ISFIRST_NAME||<nowiki>''</nowiki>||LAST_NAME,'
+ <nowiki>-----------------------------------------------</nowiki>
+ {"name":"William Gietz","hasCommission":false}
+ {"name":"William Smith","hasCommission":true}
+ {"name":"Winston Taylor","hasCommission":false}
+ SELECT json_object('id'          : employee_id,
+                    'name'        : first_name || ' ' || last_name,
+                    'contactInfo' : json_object('mail'  : email,
+                                                'phone' : phone_number),
+                    'hireDate'    : hire_date,
+                    'pay'         : salary)
+   FROM hr.employees
+   WHERE salary > 15000;
+ -- The query returns rows such as this (pretty-printed here for clarity):
+ {"id"          : 101,
+  "name"        : "Neena Kochhar",
+  "contactInfo" : {"mail"  : "NKOCHHAR",
+                   "phone" : "515.123.4568"},
+  "hireDate"    : "21-SEP-05",
+  "pay"         : 17000}
+====== JSON_OBJECTAGG ======
+SQLの結果からJSONをまとめて作りたいときとかに使う。
+書式が2個ある。
+ JSON_OBJECTAGG([KEY] <key> VALUE <value>)
+ JSON_OBJECTAGG(<key>, <value>)
+公式文書では見つけられなかったが、単に,区切りも許容される模様。こっちのほうがシンプル。
+ｋ
+ SELECT json_objectagg(department_name VALUE department_id) FROM departments;
+ -- The returned object is pretty-printed here for clarity.
+ -- The order of the object members is arbitrary.
+ JSON_OBJECTAGG(DEPARTMENT_NAMEISDEPARTMENT_ID)
+ ----------------------------------------------
+ {"Administration":        10,
+  "Marketing":             20,
+  "Purchasing":            30,
+ +                SELECT
+ +                    JSON_OBJECTAGG(
+ +                        CASE CS.SPECCODE
+ +                            WHEN 68   THEN '重量'
+ +                            WHEN 411  THEN 'LAN'
+ +                            WHEN 413  THEN '標準キーボード'
+ +                            WHEN 418  THEN '電源'
+ +                            WHEN 782  THEN '入出力ポート'
+ +                            WHEN 5124 THEN 'SSD2'
+ +                            WHEN 5209 THEN 'Bluetooth'
+ +                            WHEN 5295 THEN 'CPUグリス'
+ +                            WHEN 5688 THEN '無線LAN'
+ +                            WHEN 5701 THEN 'WEBカメラ'
+ +                            WHEN 6602 THEN 'バッテリー'
+ +                        END, COALESCE(CS.CUSTOMNAME_SIMPLE, CS.CUSTOMNAME)
+ +                    )
+ +                FROM ISOP7.M_ASM_CUSTOM_S CS
+ +                WHERE
+ +                    CS.DELFLG = 0 AND
+ +                    CS.SORTNO = 1 AND
+ +                    CS.SPECCODE IN (68, 411, 413, 418, 782, 5124, 5209, 5295, 5688, 5701, 6602)
+ +                    AND CS.MODELCODE = VUV.MODELCODE AND CS.SUBNO = VUV.SUBNO
+END, COALESCEのところをEND : COALESCEにしたらJSON_OBJECTの形式になる。
+=== Other ===
+==== Manual ====
+* [https://www.oracle.com/jp/documentation/manual.html マニュアル | Oracle 日本]
+* [https://docs.oracle.com/cd/F19136_01/ Oracle Database 19c - スタート・ガイド]
+* [https://docs.oracle.com/cd/F19136_01/books.html Oracle Database 19c - ブック]: ここに全マニュアルが一覧されている模様。
+内容別にマニュアルが細分化されている。
+* [https://docs.oracle.com/cd/F19136_01/cncpt/toc.htm 目次]: 概要とか基本的な概念の話。[Oracle Databaseの概要]/[SQLの概要] あたりにだいたいの記載がある。
+* [https://docs.oracle.com/cd/F19136_01/refrn/toc.htm 目次]: システムテーブルカラムとか。
+* [https://docs.oracle.com/cd/F19136_01/lnpls/toc.htm 目次]: データベースPL/SQL言語リファレンス
+* [https://docs.oracle.com/cd/F19136_01/arpls/toc.htm 目次]: PL/SQLパッケージおよびタイプ・リファレンス (SQLの構文外のOracle独自のパッケージ・ライブラリー・関数群)
+* [https://docs.oracle.com/cd/F19136_01/sqlrf/toc.htm 目次]: SQL言語リファレンス
+==== TNSNAMES.ORA ====
+[https://docs.oracle.com/cd/E16338&#x20;01/network.112/b56287/tnsnames.htm ローカル・ネーミング・パラメータ(tnsnames.ora)]
+Oracle DBのDBへの接続情報を記載した設定ファイル。
+TNS=Transparent Network Substrate
+OracleのDNS相当。
+DBの接続情報を記載した=の左側の識別子。DNSの項目名みたいなもの。
+デフォルトでORACLE_HOME/network/adminディレクトリーに配置されている。以下の順序で検索する。
+# 環境変数TNS_ADMIN
+# ORACLE_HOME/network/admin (例: C:\oracle\product\10.2.0\client_1\network\ADMIN)
+個の項目で最低限必要なもの。
+ <net service name>=
+   (DESCRIPTION =
+     (ADDRESS = (PROTOCOL = TCP)(HOST = <HOST>)(PORT = <PORT>))
+     (CONNECT_DATA =
+       (SERVICE_NAME = <service name>)
+     )
+   )
+省略可能な項目が少ない。
+SERVICE_NAMEは少々厄介。以下の方法で確認する。
+* データベースに接続後、SQL実行 (SELECT name FROM v$active_services;)
+* lsnrctl servicesコマンド実行
+==== Owner/Schema/User ====
+https://grok.com/share/c2hhcmQtMw%3D%3D_5d0fd582-b217-4615-9cce-4bbc3311d256
+Oracle database特有の概念、用語がある。
+Oracle Databaseは他のRDBに比べて、エンタープライズ向けの設計が多い。
+Owner: DB内のオブジェクトの所有者。オブジェクトの作成者がそのオブジェクトのownerになって所有権を持つ。他のユーザーがそのテーブルにアクセスするには、権限の付与が必要。
+セキュリティーと名前空間の管理を厳格に扱うため、オブジェ区ごととに明確な所有者を定義している。これにより、複数ユーザーが同じDBで作業しても衝突を防げる。
+このOwnerがSchemaと密接な関係になっていてややこしい。
+SchemaはMySQLだとDBのこと。PostgreSQLだと名前空間相当。
+Oracleの場合、スキーマは、ユーザーが所有するオブジェクトの集合 (名前空間)。スキーマ名はユーザー名と同じ。ユーザーを作成すると同名のスキーマも作成される。オブジェクトを論理的にグループ化するためのもの。
+例: ユーザーSCOTTがテーブルEMPを作成すると、テーブルはSCOTTスキーマに属し、フルネームはSCOTT.EMPになる。
+Ownerはスキーマ内のオブジェクトの所有者で、基本的にスキーマのユーザー自身。つまり、User = Schema Owner。UserとSchema/Ownerが連動している。
+==== 日付処理 ====
+https://grok.com/share/c2hhcmQtMw%3D%3D_dd5722c0-b2d0-43f5-83e9-655ca84c8b8d
+[https://docs.oracle.com/cd/E57425_01/121/SQLRF/functions237.htm TRUNC(数値)]
+DATE型のカラムに対して、日付で絞り込みたい場合、TRUNK関数で、小数点以下 (時刻部分) を切り捨てて、日付部分にして比較する。
+ SELECT *
+ FROM your_table
+ WHERE TRUNC(created_date) = TRUNC(SYSDATE);
+範囲指定の場合。
+ SELECT *
+ FROM your_table
+ WHERE created_date >= TRUNC(SYSDATE)
+   AND created_date < TRUNC(SYSDATE) + 1;
+==== トランザクション処理 ====
+[https://qiita.com/wsigma21/items/49116b155f66f16ca020 Oracle DatabaseのトランザクションはBEGINがいらない #SQL - Qiita]
+Oracle Databaseは常にトランザクションが有効。なので、BEGINは省略可能。COMMITが毎回必要。
+=== Client ===
+==== OSqlEdit ====
+Oracle DBの軽量クライアント。
+手順書には記載がないのですが、追加でOSqlEditのインストールもお願いします。
+[https://code73.mydns.jp/a&#x20;ogawa/osqledit/index.htm https://code73.mydns.jp/a_ogawa/osqledit/index.htm]
+ライセンスは未取得のため、お手数ですが起動時の「シェアウェアです」の警告は無視して進めてください。
+これの扱いがよくわからない。
+[https://ruka.app/php環境でのdb操作は、phpmyadminよりもadminerをおすすめします/ PHP環境でのDB操作は、phpMyAdminよりもAdminerをおすすめします | やってみルカ]
+[https://kinsta.com/jp/blog/adminer/ Adminerを利用してたった1つのPHPファイルで簡単にデータベースを管理する方法]
+OSqlEditより、Adminerでいいかも。phpMyAdminはMySQL/MariaDBだけだけど、AdminerはDBならなんでも対応している。
+Zipファイルを解凍して、[osqledit.exe] を実行すると起動できる模様。ただ、起動すると真っ先に、 [OCIライブラリの初期化に失敗しました] と表示される。
+[https://souiunogaii.hatenablog.com/entry/WIn10-OsqlEdit Windows10にOsqlEdit(64bit版)をインストールして接続できるようにする手順 - そういうのがいいブログ]
+[https://souiunogaii.hatenablog.com/entry/OracleInstantClient-Windows10 Windows10(64bit)にOracle Instant ClientをインストールしSQL*Plusを使えるようにする手順 - そういうのがいいブログ]
+OsqlEditを使うのに、Oracle Instant Clientが必要とのこと。
+[Oracle Instant Client] はOracle社が提供する、無料のDB接続ツール。ダウンロードして環境変数をちょっと設定するだけで使えるようになる。
+[[https://www.oracle.com/jp/database/technologies/instant-client.html Oracle Instant Client - Oracle Databaseに接続するための無償のツールとライブラリ | Oracle 日本]]-[今すぐダウンロード]-[Instant Client for Microsoft Windows (x64)] を選ぶ。
+以下のファイルを保存する (Basic Light Package とPrecompiler Package以外)。
+* Basic Package
+* SQL*Plus Package
+* Tools Package
+* SDK Package
+* JDBC Supplement Package
+* ODBC Package
+これらを全部展開する。instantclient_23_8のようなディレクトリーに全部展開される。
+これを任意の場所に配置する。例えば、C:\oracle\instantclient_23_8 になる。
+以下の環境変数を設定する。
+ :: NLS_LANG=Japanese_Japan.JA16SJISTILDE
+ :: TNS_ADMIN=C:\oracle\instantclient_23_8
+ Path=%TNS_ADMIN%
+Pathだけ最低設定していればよさそう。
+Pathの設定をすると、osqleditの起動時のエラーが解消される。メッセージが以下になる。
+ OSqlEditはシェアウェアです。
+ 継続して利用される場合、ライセンスをご購入下さい。
+いったん無視してOK。Login画面が表示される。
+== Client ==
+=== General ===
+==== 複数対応 ====
+https://grok.com/share/c2hhcmQtMw%3D%3D_c8665fa7-fa85-4db2-89d3-b18c521ff38b
+複数DBに対応したクライアントがほしい。特に、Oracle database
+* Adminer: テーブルを見るだけ。oracleのpackageとかはSQLで手動。
+* DBeaver: 本命。
+* OmniDB: 開発停止。
+DBeaverがよさそう。
+=== phpMyAdmin ===
+==== Install ====
+===== phpMyAdmin configuration storage =====
+[https://docs.phpmyadmin.net/en/latest/setup.html#linked-tables Installation — phpMyAdmin 5.2.3-dev documentation]
+phpmyadmin 3.4.0未満までLinked Tables Infrastructureと呼んでいた、configuration storageという機能がある。この機能を有効にすることで、ブックマークやコメントなどの機能が使えるようになる。
+種類の設定方法がある。
+* Zero configuration
+* Manual configuration
+それぞれの方法を検討する限り、結局保存用のテーブルが必要なので、Zero configurationでtestデータベースに作るのがいいだろう。
+====== Zero configuration ======
+Zero configurationは多くの場合に自動構成されるもの。特に共有ホスティングで役立つ。デフォルトオンになっている。
+以下の3のシナリオをカバーしている。
+# configuration storageテーブル不在で、[Operation] タブにアクセスすると、作成を提案する ([The phpMyAdmin configuration storage has been deactivated. Find out why.] の表示)。このリンク[Find out why]-[Create the phpMyAdmin configuration storage in the current database.]の[Create]をクリックして作る。
+# configuration storageテーブルがすでに存在するDBの場合、自動的に検知して使う。ユーザーが1DBにしかアクセスできない共有ホスティングで役立つ。
+# 複数のDBにユーザーがアクセス可能な場合、configuration storageテーブルを含むDBにアクセスしたら、その後は別のDBでもこのDBのconfiguration storageテーブルを参照する。
+ただ、上記のケースは開発などで、余計なテーブルを作りたくない場合にはダメにみえる。
+====== Manual configuration ======
+sqlディレクトリー内に、create_tables.sqlが見つかる。これを使って手動でテーブルを作る。
+====== Table ======
+以下のテーブルをphpmyadminで使っている模様。
+ pma__bookmark
+ pma__central_columns
+ pma__column_info
+ pma__designer_settings
+ pma__export_templates
+ pma__favorite
+ pma__history
+ pma__navigationhiding
+ pma__pdf_pages
+ pma__recent
+ pma__relation
+ pma__savedsearches
+ pma__table_coords
+ pma__table_info
+ pma__table_uiprefs
+ pma__tracking
+ pma__userconfig
+ pma__usergroups
+ pma__users
+==== Other ====
+===== テーブル名一覧 =====
+DB内のテーブルが多い場合、ページネーションで全テーブルが表示されず、目当てのテーブル名での検索・到達できない。
+以下の手順で該当テーブルを表示できる。
+# [Search]-[Inside tables:] にはテーブル一覧があるので、これでテーブル名を把握。
+# &table=のURLクエリーにテーブル名を指定して表示。
+===== テーブル定義出力 =====
+[https://superuser.com/questions/168595/how-to-export-mysql-schema-from-phpmyadmin database - How to export mysql schema from phpmyadmin? - Super User]
+[エクスポート]-[詳細]
+* フォーマット=Texy! text
+* 出力:◎出力をテキストで表示する
+* フォーマット特有のオプション:ダンプするテーブル=構造
+これで出力・表示すると使いやすい。
+===== Import =====
+[https://docs.phpmyadmin.net/en/latest/import_export.html Import and export — phpMyAdmin 5.1.4 documentation]
+ネット上のSQLのサンプルを試したいことがある。
+例: [https://qiita.com/kyamadahoge/items/fdbc04790f620dddbf3c MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita]
+以下のようなSQLの実行結果を取り込んで自分でもサンプルを試したい。
+ mysql> select * from history;
+ +----+----------+--------------------+------------+
+ | id | group_id | name               | created_at |
+ +----+----------+--------------------+------------+
+ |  1 |        1 | Cupcake            | 2020-01-01 |
+ |  2 |        1 | Donut              | 2020-01-02 |
+ |  3 |        2 | Eclair             | 2020-01-03 |
+ |  4 |        2 | Froyo              | 2020-01-04 |
+ |  5 |        2 | Gingerbread        | 2020-01-05 |
+ |  6 |        3 | Honeycomb          | 2020-01-06 |
+ |  7 |        4 | Ice Cream Sandwich | 2020-01-07 |
+ |  8 |        4 | Jelly Bean         | 2020-01-08 |
+ |  9 |        4 | KitKat             | 2020-01-09 |
+ +----+----------+--------------------+------------+
+rows in set (0.00 sec)
+これをこのまま取り込む方法はない。スプレッドシートでCSVに変換して取り込む。
+* 部分インポート: 行ヘッダーがある場合ここに1を指定。
+* フォーマット: CSV
+* カラムの区切り記号: デフォルトの,の他\tでタブも認識する模様。
+===== テーブル一覧表示数 =====
+* https://chatgpt.com/c/67b3df2c-b26c-800b-a60d-46febafe8bcf
+* [https://docs.phpmyadmin.net/ja/latest/config.html 設定 — phpMyAdmin 6.0.0-dev ドキュメント]
+DB選択時 (index.php?route=/database/structure&db=) のテーブル一覧が、1ページ250テーブルでページネーションになっている。
+この最大値を増やすには、config.inc.phpの$cfg['MaxTableList']を変更するしかない。
+===== 文字化け =====
+DBとphpmyadminのServer connection collationが異なると、他のPHPのアプリにも影響がある模様。
+ここはDBの文字エンコーディングと一致させる必要がある。
+文字化けして表示されたり、されなかったりしていた。apacheを再起動したら、一旦解消される。
+[https://stackoverflow.com/questions/153706/change-default-collation-in-phpmyadmin mysql - change default collation in phpmyadmin - Stack Overflow]
+config.inc.phpに以下の設定でデフォルト値をいれるとよさそう。
+ $cfg['DefaultConnectionCollation'] = 'utf8_general_ci';
+===== Bookmarks =====
+[https://docs.phpmyadmin.net/en/latest/bookmarks.html Bookmarks — phpMyAdmin 5.2.3-dev documentation]
+入力したクエリーなどを保存しておく機能がある。
+ただし、事前に [phpMyAdmin configuration storage] の設定が必要。
+SQLのブックマークは、DBごとに保存される。例えば、テーブル名の検索などで、information_schemaを検索することはよくある。これは、information_schemaのDBを開かないとブックマークを表示できない。
+末尾に [ブックマークされているSQL] のリストボックスが表示されるので、ここから呼び出せる。
+=== Adminer ===
+==== Oracle ====
+===== Docker =====
+https://grok.com/share/c2hhcmQtMw%3D%3D_f7a332ac-d5e4-40b9-8168-79bde08bfbe1
+「[https://hub.docker.com/_/adminer/ adminer - Official Image | Docker Hub]」に公式イメージがある。デフォルトで、MySQL/PostgreSQL/SQLite/SimpleDB/Elasticsearchに対応している。が、Oracleは未対応。対応するなら、このイメージをベースにPHP拡張の追加インストールが必要。面倒くさい。
+Grokによると、対応イメージを用意してくれている人がいるので、これを使うといいとのこと。
+[https://hub.docker.com/r/soivangoi/nginx-php-adminer-oci8 soivangoi/nginx-php-adminer-oci8 - Docker Image | Docker Hub]
+ docker run -d -p 8088:80 soivangoi/nginx-php-adminer-oci8
+これでlocalhost:8008でアクセスできる。
+だが、デフォルト違うオーナーのテーブルが、メニュー一覧に表示されなくて、扱いにくい。認識はできている。
+.3.1でバージョンが古いのが原因らしい。バージョンを上げたら解決するらしい。が、自分でociを使ったdockerを作る必要があって少々面倒くさい。
+https://grok.com/share/c2hhcmQtMw%3D%3D_ba20c1c3-4ee3-47e4-8e42-63f51b854915
+試行錯誤の末、以下のDockerfileだと、ビルドできて解消できた (2025-09-12 Fri)。adminer 5.1.0でも表示は問題なかった。
+ FROM php:7.4.0-apache
+ # 環境変数の設定
+ ENV ADMINER_VERSION=5.4.0
+ ENV MEMORY=256M
+ ENV UPLOAD=2048M
+ ENV LD_LIBRARY_PATH="/usr/local/instantclient"
+ ENV ORACLE_HOME="/usr/local/instantclient"
+ ENV TNS_ADMIN="/usr/local/instantclient/network/admin"
+ RUN sed -i 's|<nowiki>http://deb.debian.org/debian|http://archive.debian.org/debian|g'</nowiki> /etc/apt/sources.list && \
+     sed -i 's|<nowiki>http://security.debian.org/debian-security|http://archive.debian.org/debian-security|g'</nowiki> /etc/apt/sources.list && \
+     echo 'Acquire::Check-Valid-Until "false";' > /etc/apt/apt.conf.d/99ignore-release-date
+ # 必要なパッケージと依存関係のインストール
+ RUN apt-get update && apt-get install -y \
+     libaio1 \
+     unzip \
+     wget \
+     ca-certificates \
+     && apt-get clean \
+     && rm -rf /var/lib/apt/lists/*
+ # Oracle Instant Clientのインストール
+ ADD <nowiki>https://download.oracle.com/otn_software/linux/instantclient/214000/instantclient-basic-linux.x64-21.4.0.0.0dbru.zip</nowiki> /tmp
+ ADD <nowiki>https://download.oracle.com/otn_software/linux/instantclient/214000/instantclient-sdk-linux.x64-21.4.0.0.0dbru.zip</nowiki> /tmp
+ RUN unzip /tmp/instantclient-basic-linux.x64-21.4.0.0.0dbru.zip -d /usr/local/ && \
+     unzip /tmp/instantclient-sdk-linux.x64-21.4.0.0.0dbru.zip  -d /usr/local/ &&\
+     ln -s /usr/local/instantclient_21_4 /usr/local/instantclient
+ ENV NLS_LANG=Japanese_Japan.JA16SJISTILDE
+ ENV LD_LIBRARY_PATH=/usr/local/instantclient
+ # OCI8拡張のインストール
+ RUN docker-php-ext-configure oci8 --with-oci8=instantclient,/usr/local/instantclient && \
+     docker-php-ext-install oci8 && \
+     docker-php-ext-configure pdo_oci --with-pdo-oci=instantclient,/usr/local/instantclient,21.4 && \
+     docker-php-ext-install pdo_oci && \
+     docker-php-ext-enable oci8
+ # Adminerのインストール
+ RUN wget <nowiki>https://github.com/vrana/adminer/releases/download/v${ADMINER_VERSION}/adminer-${ADMINER_VERSION}.php</nowiki> -O /var/www/html/index.php
+ # PHP設定
+ RUN echo "memory_limit=${MEMORY}" > /usr/local/etc/php/conf.d/adminer.ini && \
+     echo "upload_max_filesize=${UPLOAD}" >> /usr/local/etc/php/conf.d/adminer.ini && \
+     echo "post_max_size=${UPLOAD}" >> /usr/local/etc/php/conf.d/adminer.ini
+ # 作業ディレクトリの設定
+ WORKDIR /var/www/html
+ docker build -t adminer-oracle .
+ docker run -d -p 8088:80 adminer-oracle
+これでlocalhost:8088をブラウザーで開く。解決！
+===== login =====
+Oracle DB接続時は、ログイン時の入力内容に注意が必要。
+[サーバー] 欄に以下の形式で項目入力が必要。
+ host:port/service_name
+* host: サーバーのホスト名またはIPアドレス。
+* port: ポート。通常は1521
+* service_name: DBのサービス名。TSNの識別子。
+ ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
+間違えていると以下のようなエラーが出る。
+これで表示はできる。
+===== owner =====
+Oracleではスキーマ=オーナー。
+Adminerでは、DBとスキーマを同じ名前にすると、そのオーナーのテーブルを一覧できる模様。
+===== 制限 =====
+Oracle database対応は不十分で、いくつかの機能がない。
+* テーブルの複製 (移動や削除は可能)
+* SQL形式でのエクスポート (CSVなどなら対応)
+* テーブル以外のfunction/procedureなどの閲覧。SELECT * FROM ALL_PROCEDURESなどのSQLで確認可能。
+テーブルの複製はSQLでやるしかなさそう。
+テーブル以外を閲覧できないのが痛い。いまいち。DBeaverのほうがよさそう。
+=== DBeaver ===
+==== Basic ====
+[https://itsakura.com/dbeaver-oracle-sql DBeaverの使い方(Oracleに接続してSQLを実行) | ITSakura]
+[https://docs.oracle.com/ja-jp/iaas/Content/data-flow/using/sql-reporting-dbeaver-setup.htm SQL開発ツールとしてのDBeaverの設定]
+[Database Navigator] ペーン-[Create]-[Connection]-[Oracle]-[Next]
+[Oracle Connection Settings] で接続情報を入力。
+[Test Connection] を選ぶ。必要なドライバーを自動的に取得する。
+==== driver files are missing ====
+[Test Connection]-[Download driver files]-[Download] を選んでも [Oracle driver files are missing.] で失敗する。
+[https://qiita.com/taisa1108/items/d738d0fcb53f5273ee5d DBeaverからドライバインストールに失敗した場合の対応手順 #Mac - Qiita]
-utf8mb4_0900_binなどの0900はMySQL 8.0から使用可能。0900はUnicode 9.0の意味。MySQL専用でMariaDBにはない。
+プロキシー設定などで取得に失敗しているのが原因の模様。自分でファイルを取得して配置してもいい。
-utf8mb4_binは終端スペースを除去する。0900は除去しない。
+[Edit Connection]-[Connection settings]-[Main]-[Driver settings]
-utf8mb4_binは照合時に先頭に0を追加する場合があるが、0900はそれがなく、0900のほうが並べ替えがはるかに高速。らしい。
+[Libraries]
-基本はutf8mb4_0900_binを使うとよさそう。
+デフォルトを全部削除。
-===== language =====
+%AppData%\Local\DBeaver\lib\oracleとかに配置する。
-[https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html MySQL :: MySQL 8.0 Reference Manual :: 12.10.1 Unicode Character Sets]
-generalとかunicodeとかの言語名部分の違いが上記に記載がある。
+これでOK。
-なお、日本語の異体字はDBでは吸収できない。アプリ側で対応必要 ([https://msnr.net/2021/02/mysql_itaiji.html MySQLでの異体字対応検索 - まぁそれなりに ねっと])。
-===== 設定・変更 =====
-[https://web.gnusocial.jp/post/2023/05/13/6804/ MariaDBのDBの文字コードと照合順の変更 | GNU social JP Web]
-指定したDBの文字コード・照合順の情報は以下のコマンド・SQLで確認可能です。
- DB_USER=root
- DB_NAME=mysql
- mysql -u $DB_USER -p -e \
- 'SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
-     FROM INFORMATION_SCHEMA.SCHEMATA;'
- +--------------------+----------------------------+------------------------+
- | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
- +--------------------+----------------------------+------------------------+
- | information_schema | utf8                       | utf8_general_ci        |
- | performance_schema | utf8                       | utf8_general_ci        |
- | mysql              | utf8mb4                    | utf8mb4_general_ci     |
- +--------------------+----------------------------+------------------------+
-設定は以下で行えます。
- DB_USER=root
- DB_NAME=mysql
- ## MariaDB
- mysql -u $DB_USER -p -e \
-  "ALTER DATABASE \`$DB_NAME\` DEFAULT CHARACTER SET 'utf8mb4' DEFAULT COLLATE 'utf8mb4_bin';"
- ## MySQL
- mysql -u $DB_USER -p -e \
-   "ALTER DATABASE $DB_NAME DEFAULT CHARACTER SET 'utf8mb4' DEFAULT COLLATE 'utf8mb4_0900_bin';"
-=== Other ===
-==== データベース一覧 ====
- show databases;
-==== テーブルの検索 ====
-https://chatgpt.com/c/6789de8b-11ac-800b-bb21-45ba3735aa3f
- SELECT TABLE_SCHEMA, TABLE_NAME
- FROM information_schema.TABLES
- WHERE TABLE_NAME LIKE '%検索するテーブル名%'
- ORDER BY TABLE_SCHEMA, TABLE_NAME;
-information_schemaから検索できる。
-==== testデータベース ====
-https://chatgpt.com/c/67b686cc-fdc0-800b-9538-03d86cf046de
-MySQL 5.7までMySQLインストール時に、自動的にtestデータベースが作られる。MySQL 8.0からは自動では作られない。
+==== ER図 ====
+テーブルを選択-[Diagram] タブ。これだけで勝手に作ってくれている。めちゃくちゃ楽。
-テスト用のデータベースで全ユーザーがアクセス可能。
+==== CLOBの閲覧 ====
+https://grok.com/share/c2hhcmQtMw%3D%3D_14def65d-074e-4453-b21c-5251822d09aa
-本番では削除が推奨される。phpmyadminなどのツールのデータ保存用に使うと良い。
+データ量が大きいと、途中で打ち切られる。いくつか対策がある。
+右クリック-[Export data]
-==== 予約語 ====
+* Binary data
+** [Window]-[Preference]-[Editors]-[Data Editor]-[Binary Editor]
-* https://chatgpt.com/c/67ce4824-41f8-800b-b7d0-ff41365b5d91
+** [Maximum LOB length to keep in memory (10000):
-* [https://dev.mysql.com/doc/refman/8.0/ja/keywords.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 9.3 キーワードと予約語]
+* Content
-* [https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0170 第170回　MySQLのキーワードと予約語をテーブルに使用したい場合 | gihyo.jp]
+** Maximum text editor content length (KB) (100): テキストエディターに表示するサイズ。これを増やすと表示に反映される。
-MySQLは組込関数がたくさんあり、それらが全て予約語。以下でも確認できる。
- select * from information_schema.KEYWORDS;
-予約語をカラム名などに使う場合、バッククオートかANSIモードで二重引用符でエスケープが必要。
-ただ、だからといって全部にエスケープすると可読性が悪い。変数や予約語だけエスケープするのがいい模様。
-* rank
 [[Category:IT]]

匿名

検索

「DB」の版間の差分

2025年10月20日 (月) 11:55時点における最新版

Other

Performance

SQL vs. app

カラム順序

マスター

Sharding

親子関係

Standard

制約

Error

SQLSTATE[01000]: Warning: 1265 Data truncated for column

Transfer

import

Design

ID

DB ID

議論

IDとコード

Naming

正規化

非正規形

第1正規形

第2正規形

第3正規形

Naming

日本語カラム

命名規則

Convention

List

大文字小文字

単語区切り

共通カラム

Data

enum/string

画像データのパス/BLOBでの格納

NULL

int/string

INT/BIGINT

NULLと空文字

NULL

VARCHARの文字数定義

TEXT型

外部キー制約

Relation

多対多

ポリモーフィック

Function/Operator

Flow control function

SQL

About

DML

Expression/式

CASE

DELETE

INSERT

basic

bulk

UPDATE

Basic

別のテーブルで更新

UPSERT

MySQL

INSERT ... ON DUPULICATE KEY UPDATE

AUTO_INCREMENT

UNIQUE

注意すべき挙動の整理

Oracle

Basic

GROUP BY

Basic

連番

SELECT

JOIN

列名が重複する場合の対応

JOINとWHERE

information schema