DBのソースを表示

== Other ==

=== Performance ===

==== SQL vs. app ====
[https://gihyo.jp/dev/serial/01/game_mysql/0005 第5回　DB側でやること、アプリ側でやることを見極めよう | gihyo.jp]

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

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

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

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

* [https://teratail.com/questions/26532 MySQLでカラムの順番は重要ですか？]
* [https://teratail.com/questions/97539 定義するカラムの順番]
* [https://qiita.com/Tachy_Pochy/items/7d8d16611adcb5a08cbe テーブルのカラムを増やす際に末尾に追加するのではなく、カラムの並び順を変更したい #考え方 - Qiita]
* [https://qiita.com/pea1205/items/9caf3f37fc4ce9f401b1 PostgreSQLのカラム順序がテーブルサイズに与える影響 #PostgreSQL - Qiita]
* [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

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

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

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

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

created_at/updated_atのような全テーブルに付随するようなカラムは、主キーの直後でいい。全テーブルで共通にできるから。
=== マスター ===
[https://jsol-datamanagement.com/solution/solution-575/ マスタデータって何？マスタデータについて詳しく解説！ - J-MDM／Snowflake]

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

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

[https://www.praha-inc.com/lab/posts/database-normalization 【データベース正規化完全ガイド】実務観点での正規化手順やポイントを詳しく解説   | PrAhaENGINEERLAB]

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

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

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

=== Sharding ===
[https://e-words.jp/w/%E3%82%B7%E3%83%A3%E3%83%BC%E3%83%87%E3%82%A3%E3%83%B3%E3%82%B0.html シャーディングとは - 意味をわかりやすく - IT用語辞典 e-Words]

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

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

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

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

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

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

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

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

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

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

[https://teratail.com/questions/281348 １つのテーブルで親子関係を管理する方法]

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

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

=== Standard ===
[[wikipedia:ISO/IEC_9075|ISO/IEC 9075 - Wikipedia]]

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

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

* [[wikipedia:List_of_SQL_reserved_words|List of SQL reserved words - Wikipedia]]: 標準SQLの予約語一覧
* [[wikipedia:SQL_syntax|SQL syntax - Wikipedia]]: 標準SQLの構文。

=== 制約 ===

* [https://qiita.com/MuscleProgramer/items/b34c6d83037bae3b5070 ユニークキーはNULL許容します（圧） #MySQL - Qiita]
* [https://qiita.com/s_ryota/items/d04cbc633f1b1f4958f9 データベースにおける制約の種類 #DB - Qiita]

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が使えないなら変数で行えるらしい ([https://qiita.com/sand_bash/items/851f08a36381a4f01b75 MySQLのSELECT結果に行番号を振る。 #mariadb - Qiita])。

== Design ==

=== ID ===

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

* [https://zenn.dev/j5ik2o/articles/a085ab3e3d0f197f6559 ID生成方法についてあれこれ]
* [https://zenn.dev/praha/articles/3c84e3818891c3 idをautoincrementして何が悪いの？]

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

[https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c Sharding & IDs at Instagram. With more than 25 photos and 90 likes… | by Instagram Engineering | Instagram Engineering]

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

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

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

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

==== 議論 ====

* [https://nippondanji.blogspot.com/2013/12/id.html 漢(オトコ)のコンピュータ道: IDの設計についてのさらに突っ込んだ議論]

==== IDとコード ====
* [https://atmarkit.itmedia.co.jp/bbs/phpBB/viewtopic.php?topic=30818&forum=26 「列名の付け方（～CD or ～ID ？）」（1） Database Expert － ＠IT]
* [https://irof.hateblo.jp/entry/2021/10/26/181920 コード、区分、フラグ、IDと名前。 - 日々常々]

コードは広い概念。

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

==== Naming ====

* [https://softwareengineering.stackexchange.com/questions/114728/why-is-naming-a-tables-primary-key-column-id-considered-bad-practice sql - Why is naming a table's Primary Key column "Id" considered bad practice? - Software Engineering Stack Exchange]
* [https://stackoverflow.com/questions/1369593/primary-key-foreign-key-naming-convention sql - Primary key/foreign Key naming convention - Stack Overflow]
* [https://stackoverflow.com/questions/208580/naming-of-id-columns-in-database-tables/ sql - Naming of ID columns in database tables - Stack Overflow]
* [https://stackoverflow.com/questions/465136/is-prefixing-each-field-name-in-a-table-with-abbreviated-table-name-a-good-pract database design - Is prefixing each field name in a table with abbreviated table name a good practice? - Stack Overflow]
* [https://stackoverflow.com/questions/529863/do-you-prefer-verbose-naming-when-it-comes-to-database-columns sql - Do you prefer verbose naming when it comes to database columns? - Stack Overflow]
* [https://qiita.com/ayayo/items/ba38853bca0c2cc2acb7 SQLアンチパターン勉強会　第三回：IDリクワイアド #SQL - Qiita]

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

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

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

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

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

=== 正規化 ===

* [https://oss-db.jp/dojo/dojo_info_04 第4回　データベースの正規化]
* [https://servithink.co.jp/blog/archives/tech-blog-db-normalization-ornot 正規化よりも実装のしやすさを優先させる｜東京のWeb制作会社・ホームページ制作会社｜Servithink]
* [https://ripurun.com/media/db-design/normalization-3-steps/ 失敗しないデータベース正規化の3ステップをプロのエンジニアが解説 - みんなのシステム企画]

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

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

==== 非正規形 ====
正規化されていない状態のテーブル。具体的には、1セルに複数のデータが入っている状態。
{| class="wikitable"
|+学生テーブル
!学生ID
!学生名
!コース名
!教授名
|-
|1
|山田太郎
|サッカー、軽音
|山田、鈴木
|-
|2
|菊池太郎
|野球
|山田
|-
|3
|川上太郎
|バレーボール
|山根
|}
例えば、所属サークルに [サークル、軽音] の2個のデータが入っている。これがまずい。

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

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

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

行で分けると以下のようになる。
{| class="wikitable"
|+学生テーブル
!学生ID
!学生名
!コース名
!教授名
|-
|1
|山田太郎
|サッカー
|山田
|-
|1
|山田太郎
|軽音
|鈴木
|-
|2
|菊池太郎
|野球
|山田
|-
|3
|川上太郎
|バレーボール
|山根
|}
主キーが重複するが、ひとまず第一正規形にはなった。

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

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

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

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

==== 第2正規形 ====
主キーの一部への依存関係をなくす。「部分関数従属性をなくす」。

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

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

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

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

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

{| class="wikitable"
|+学生テーブル
!学生ID
!学生名
|-
|1
|山田太郎
|-
|2
|菊池太郎
|-
|3
|川上太郎
|}
{| class="wikitable"
|+コーステーブル
!コースID
!コース名
!教授名
|-
|1
|サッカー
|山田
|-
|2
|軽音
|鈴木
|-
|3
|野球
|山田
|-
|4
|バレーボール
|山根
|}
{| class="wikitable"
|+関連テーブル、学生詳細テーブル
!学生ID
!コースID
|-
|1
|1
|-
|1
|2
|-
|2
|3
|-
|3
|4
|}
残ったIDだけのテーブル。関連テーブル、中間テーブルと呼ぶ。IDを片方に残して、複合主キーとすることもできる。

第２正規形の解決課題。

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

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

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

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

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

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

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

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

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

{| class="wikitable"
|+コーステーブル
!コースID
!コース名
!教授ID
|-
|1
|サッカー
|1
|-
|2
|軽音
|2
|-
|3
|野球
|1
|-
|4
|バレーボール
|3
|}
{| class="wikitable"
|+教授テーブル
!教授ID
!教授名
|-
|1
|山田
|-
|2
|鈴木
|-
|3
|山根
|}

=== Naming ===

==== 日本語カラム ====
https://chatgpt.com/share/686b356d-2698-800b-974e-3ff1d34ae7ef

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

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

==== 命名規則 ====
* [https://webapp.perlzemi.com/blog/20191113074356.html データベースのテーブル名とフィールド名の命名規約 - Perl Webアプリ開発入門]
* [https://www.yiiframework.com/wiki/227/guidelines-for-good-schema-design Guidelines for good schema design | Wiki | Yii PHP Framework]

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

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

==== Convention ====

===== List =====
* [https://stackoverflow.com/questions/5951245/are-there-any-published-coding-style-guidelines-for-sql mysql - Are there any published coding style guidelines for SQL? - Stack Overflow]
* [https://www.sqlstyle.guide/ SQL style guide by Simon Holywell]
* [https://docs.telemetry.mozilla.org/concepts/sql_style#boolean-at-the-beginning-of-line SQL Style Guide - Mozilla Data Documentation]
* [https://www.drupal.org/docs/develop/standards/sql/sql-coding-conventions SQL coding conventions | SQL | Drupal Wiki guide on Drupal.org]
* [https://www.ibm.com/docs/en/openpages/8.2.0?topic=guide-sql-coding-guidelines SQL Coding Guidelines - IBM Documentation]

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

===== 大文字小文字 =====
* [https://style.potepan.com/articles/26931.html SQLで大文字・小文字のルールはある？区別・変換方法も一緒に教えます！ | ポテパンスタイル]
* [https://zenn.dev/miya_tech/articles/bcdef7245e4969 MySQLの予約語は大文字に統一したほうがいいのは本当か？]

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

構文強調がないと、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

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

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

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

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

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

=== Data ===

==== enum/string ====
* [https://zenn.dev/tonbi_attack/articles/fb796ac3cf9720 DBで区分値などのCODEは数値ではなく文字列を利用するべき]
* [https://teratail.com/questions/154006 区分値は数値にすべきか意味のある文字列にすべきか]

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

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

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

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

* [https://stackoverflow.com/questions/1347461/saving-images-files-or-blobs database - Saving images: files or blobs? - Stack Overflow]
* [https://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay database - Storing Images in DB - Yea or Nay? - Stack Overflow]
* [https://www.microsoft.com/en-us/research/publication/to-blob-or-not-to-blob-large-object-storage-in-a-database-or-a-filesystem/ To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem - Microsoft Research]
* [https://teratail.com/questions/296506 データベースに画像を保存するメリット・デメリットと必要性について]
* [https://teratail.com/questions/81233 データベースに画像を保存するのはありでしょうか？]

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

==== NULL ====
Ref: [https://qiita.com/daichi_yamazaki/items/ea3516080948e95bbf26 NOT NULL 制約をつけるべきカラムとは #PostgreSQL - Qiita].

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

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

==== int/string ====
* [https://stackoverflow.com/questions/747802/integer-vs-string-in-database language agnostic - Integer vs String in database - Stack Overflow]
* [https://teratail.com/questions/259465 データベースの値の持ち方は文字列にするか数字にするか迷っています]

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

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

==== INT/BIGINT ====
* [https://qiita.com/tk20211228/items/fbed24e7c97bf10112a0 MySQLにおけるint型IDの限界とクラッシュ回避のための対策 #MySQL - Qiita]
* [https://gihyo.jp/article/2022/08/mysql-rcn0178 第178回　MySQLのAUTO_INCREMENTなINTEGERってどのくらいで使い切るの？ | gihyo.jp]
型

* 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と空文字 ====

* [https://stackoverflow.com/questions/5618357/sql-server-null-vs-empty-string SQL Server: Null VS Empty String - Stack Overflow]
* [https://softwareengineering.stackexchange.com/questions/32578/sql-empty-string-vs-null-value design - SQL: empty string vs NULL value - Software Engineering Stack Exchange]

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

NULLと空文字の意味論

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

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

* ミドルネームなし=<nowiki>''</nowiki>
* ミドルネームの有無不明=NULL

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

NULLがないほうがSQLはシンプルになる。が、値が空なのか、不定なのか判別できない。判別したいなら、別のカラムが必要になる ([https://zenn.dev/pei0804/articles/donot-use-nullable-in-star-schema 分析用途のテーブルにnullはいらない])。

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

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

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

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

==== NULL ====
[https://oss-db.jp/dojo/dojo_08 第8回　NULLって何？]

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

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

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

==== VARCHARの文字数定義 ====

* [https://stackoverflow.com/questions/8295131/best-practices-for-sql-varchar-column-length mysql - Best practices for SQL varchar column length - Stack Overflow]
* [https://dev.mysql.com/doc/refman/8.4/en/char.html MySQL :: MySQL 8.4 リファレンスマニュアル :: 13.3.2 CHAR 型と VARCHAR 型]

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

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

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

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

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

==== TEXT型 ====
[https://yama-weblog.com/mysql-difference-between-varchar-and-text/ DB設計でtext型とvarchar型の違いや最大文字数を調べてみる | 東京初めてエンジニア]

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

半角→65,535字

全角→32,767字

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

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

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

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

==== 外部キー制約 ====
[https://kagamihoge.hatenablog.com/entry/20130513/1368449162 整合性制約の有無によるINSERTの速度差 - kagamihogeの日記]

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

=== Relation ===

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

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

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

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

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

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

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

* [https://rails.densan-labs.net/form/many_to_many_form.html 1.4. 多対多の関連を持つオブジェクトを編集可能なフォーム — Railsアプリケーション構築ガイド]
* [https://rails.densan-labs.net/form/many_to_many_checkbox_form.html 1.5. 多対多の関連を持つオブジェクトをチェックボックスで編集可能なフォーム — Railsアプリケーション構築ガイド]

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

==== ポリモーフィック ====

* [https://zenn.dev/ukiyocreate_dev/articles/b184ea1c3c39be ORM のポリモーフィック関連は使うべきか？]
* [https://techlife.cookpad.com/entry/2023/04/13/141200 ポリモーフィック関連を活用し、森羅万象の「いいね」を実現する手法 - クックパッド開発者ブログ]
* [https://qiita.com/onosi/items/270e87740a4c3e24eae6 SQLアンチパターン6章　ポリモーフィック関連 #SQLアンチパターン - Qiita]
* [https://qiita.com/dai329/items/1db8fbe37f43a465d801 SQLアンチパターン勉強会　第6回：ポリモーフィック関連 #SQLアンチパターン - Qiita]

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

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

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

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

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

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

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

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

== Function/Operator ==
[https://dev.mysql.com/doc/refman/8.0/ja/functions.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 12 関数と演算子]

=== Flow control function ===
'''表 12.7 「フロー制御演算子」'''
{| class="wikitable"
!名前
!説明
|-
|[[/dev.mysql.com/doc/refman/8.0/ja/flow-control-functions.html#operator%20case|<code>CASE</code>]]
|CASE 演算子
|-
|[[/dev.mysql.com/doc/refman/8.0/ja/flow-control-functions.html#function%20if|<code>IF()</code>]]
|If/else 構文
|-
|[[/dev.mysql.com/doc/refman/8.0/ja/flow-control-functions.html#function%20ifnull|<code>IFNULL()</code>]]
|Null if/else 構文
|-
|[[/dev.mysql.com/doc/refman/8.0/ja/flow-control-functions.html#function%20nullif|<code>NULLIF()</code>]]
|expr1 = expr2 の場合に NULL を返します
|}
CASEとIFはSQL文としても同名が存在する。ただし、使うタイミングがSQL文だとストアドプロシージャであまり使うことはない。演算子と関数をよく使う。

== 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 ===
DML (Data Manipulation Language)。データ操作用言語。

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]

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 ====
[https://products.sint.co.jp/topsic/blog/sql-delete DELETE文（SQLを基本から学ぶシリーズ）]
 DELETE FROM [table] [条件]
構文がシンプル。

==== INSERT ====

===== basic =====
* [https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14150336179 SQL文についての質問です。INSERT文でINTOの後のカラム名指定のところ... - Yahoo!知恵袋]
* [https://products.sint.co.jp/topsic/blog/sql-insert INSERT文（SQLを基本から学ぶシリーズ）]

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

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

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

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

[https://qiita.com/shakechi/items/c5be910d924b9661c216 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 =====
[https://blog.yuyat.jp/archives/2018 PHP/MySQL でレコードを N 件ずつバルクインサート | Born Too Late]

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

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

==== UPDATE ====

===== Basic =====
[https://products.sint.co.jp/topsic/blog/sql-update UPDATE文（SQLを基本から学ぶシリーズ）]
 UPDATE (表名) SET (カラム名1) = (値1) WHERE (条件);
 UPDATE (表名) SET (カラム名1) = (値1), (カラム名2) = (値2) WHERE (条件);
一括で更新する。

==== 別のテーブルで更新 ====
[https://products.sint.co.jp/topsic/blog/sql-update#toc-7 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: [https://resanaplaza.com/2023/01/22/%e3%80%90%e7%b7%8f%e3%81%be%e3%81%a8%e3%82%81%e3%80%91upsert%e3%81%a8%e3%81%af%ef%bc%9fdb%e3%81%94%e3%81%a8%e3%81%ae%e9%81%95%e3%81%84%e3%81%af%ef%bc%9f/ 【総まとめ】UPSERTとは？DBごとの違いは？ | 初心者DIYプログラミング入門].

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

===== MySQL =====

====== INSERT ... ON DUPULICATE KEY UPDATE ======
MySQLにMERGE文はない ([https://stackoverflow.com/questions/42663074/is-merge-statement-available-in-mysql sql - Is merge statement available in MySQL - Stack Overflow])。代わりにINSERT ... ON DUPULICATE KEY UPDATEという文を使う。

「[https://stackoverflow.com/questions/9168928/what-are-practical-differences-between-replace-and-insert-on-duplicate-ke sql - What are practical differences between `REPLACE` and `INSERT ... ON DUPLICATE KEY UPDATE` in MySQL? - Stack Overflow]」によると、REPLACE文も使えるが、内部的に一度削除して、副作用があるので、INSERT ... ON DUPULICATE KEY UPDATEがいい模様。

[https://dev.mysql.com/doc/refman/8.4/en/insert-on-duplicate.html 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;
複数レコードを一括で更新したい場合。「[https://journal.lampetty.net/entry/mysql-bulk-insert-and-on-duplicate-key-update 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);
[https://stackoverflow.com/questions/11235501/mysql-insert-row-on-duplicate-key-update-multiple-columns 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 ======
* [https://stackoverflow.com/questions/23516958/on-duplicate-key-auto-increment-issue-mysql ON DUPLICATE KEY + AUTO INCREMENT issue mysql - Stack Overflow]
* [https://ameblo.jp/stylagy/entry-12434472506.html 【MySQL】DUPLICATE で自動採番が飛ぶケースについて（２） | 株式会社スタイラジーの「居心地の良い」ブログ]
* [https://commerce-engineer.rakuten.careers/entry/tech/0005 MySQL で ON DUPLICATE KEY UPDATE を使ってたら、カンスト(カウンターストップ)した話 - R-Hack（楽天グループ株式会社）]
* [https://tech.mobilefactory.jp/entry/2020/12/12/000000 MySQLでのINSERT ON DUPLICATE構文との付き合い方 - Mobile Factory Tech Blog]
* [https://qiita.com/QBMK_IQU/items/4515c935b48acd0c953e INSERT ON DUPLICATE KEY UPDATEの利点と注意点 #MySQL - Qiita]

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 ======

* [https://ramble.impl.co.jp/4159/ 【MySQL】Nullable カラムを含めて複合ユニーク制約を設ける方法 | ramble - ランブル -]
* [https://tgk.hatenadiary.org/entry/20120913/1347506946 「NULLがUNIQUE制約に縛られないことを利用する」のは、正当なNULLの使い方 - 極北データモデリング]

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

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

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

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

===== Basic =====
[https://amg-solution.jp/blog/64244 【SQL入門】複数のデータをグループ化するGROUP BYの使い方 | 株式会社AMG Solution]

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

基本構文
 SELECT 要素名 FROM テーブル名 GROUP BY グループ化したい要素名;
SELECT部分でGROUP BYで指定したカラム名の指定が必須？

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

===== 連番 =====
[https://qiita.com/kyamadahoge/items/fdbc04790f620dddbf3c 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
 <nowiki> </nowiki> @rownum := IF(@prev_group_id = group_id, @rownum + 1, 1) AS rownum, -- 連番の列
 <nowiki> </nowiki> history.id,
 <nowiki> </nowiki> @prev_group_id := group_id AS group_id,
 <nowiki> </nowiki> history.name,
 <nowiki> </nowiki> history.created_at
 FROM
 <nowiki> </nowiki> history,
 <nowiki> </nowiki> (select @rownum := 0) hoge,
 <nowiki> </nowiki> (select @prev_group_id := <nowiki>''</nowiki>) fuga
 ORDER BY
 <nowiki> </nowiki> group_id,
 <nowiki> </nowiki> 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 ====
[https://dev.mysql.com/doc/refman/8.0/ja/select.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.10 SELECT ステートメント]

====== JOIN ======

* [https://products.sint.co.jp/topsic/blog/sql-bootcamp-07 SQLのテーブル結合（JOIN, UNION, NATURAL）を覚えよう！基本構文と使用例を解説]
* [https://zenn.dev/argi_berri/articles/74e518f2f703c1 SQL の内部結合(INNER JOIN)と内部結合 (OUTER 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で地道に追加する。これが確実。プリペアードステートメントなどを使うと、プログラム側でできなくはない。

[https://zenn.dev/kasamifutoshi/scraps/8c8cf445f50445 【Laravel】目的別クエリビルダの記述方法まとめ（記事を書く前の整理用）]

[https://rimane.net/inner-join-1037/ 結合したテーブルに同じ名前のカラムがあっても正しく取り出す方法【MySQL+PHP】 - りまねどっとねっと]

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

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

[https://stackoverflow.com/questions/329931/sql-select-join-is-it-possible-to-prefix-all-columns-as-prefix SQL select join: is it possible to prefix all columns as 'prefix.*'? - Stack Overflow]

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

[https://qiita.com/tomoya_ozawa/items/0cc147623d6cfe0ce0b8 同じテーブルを複数回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 ======
* [https://blog.mosuke.tech/entry/2014/02/28/232846/ INNER JOIN とWHERE結合の違いについて理解する]
* [https://www.ritolab.com/posts/258 テーブル結合は JOIN と FROM どちらで行うべきか。両者の違いと注意点 | Ritolabo]
* [https://zenn.dev/kou_hikaru/articles/c49dec03a7d37b WHERE句とON句の適切な使い方]

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

ただ、外部結合は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 ===
[[wikipedia:Information_schema|Information schema - Wikipedia]]

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

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

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

「[https://www.quora.com/Why-doesnt-SQLite-support-INFORMATION_SCHEMA Why doesn't SQLite support INFORMATION_SCHEMA? - Quora]」にあるように、SQLiteのような軽量な実装にすると、このメタデータのテーブル実装はコストが大きいのだと思われる。
=== VIEW ===

* [https://qiita.com/ryo-ka-notti/items/341310a6f0673fa2a7b5 ビュー(DB)について #SQL - Qiita]
* [https://style.potepan.com/articles/25504.html SQLのビュー(VIEW)って何？基本的な使い方理解しよう【サンプル有り】 | ポテパンスタイル]
* [https://and-engineer.com/articles/YRsqCxAAAPDr7vrW SQLのビューとは？考え方から実際にできることまで解説！ | アンドエンジニア]

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

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

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




=== 一括更新 ===
Ref: [https://zenn.dev/rescuenow/articles/5b16ab6c4d9cf9 MySQLで複数行を一括更新するクエリの実装方法].

ELT/FIELDを併用すれば一括更新可能とのこと。
=== NULLを含むLIKE ===
[https://stackoverflow.com/questions/3924400/like-does-not-accept-null-value sql - like '%' does not accept NULL value - Stack Overflow]

[https://qiita.com/umekikazuya/items/61c16a36824c334e31e5 【SQL】NULL値を制御/SQLマスターへの道「COALESCE」 #初心者 - Qiita]

LIKEで文字列検索する際、SELECT * FROM DB WHERE <column> LIKE '%%';であいまい検索してもNULLの場合ヒットしない。
 where COALESCE([table].[column],<nowiki>''</nowiki>) like '<parameter>'
 IFNULL([table].[column], <nowiki>''</nowiki>) like '%'
COALESCEかISNULLで引数に空文字を指定するとうまくいく模様。

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

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

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

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

「[https://mysql.sql55.com/sql/mysql-isnull-coalesce.php MySQL の ISNULL()・COALESCE() - MySQL の基礎 - MySQL 入門]」MySQLのISNULLは引数1個で、boolを返すだけ。

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

NULLIF/COALESCEは標準。

MYSQLのNULLIFは引数1=引数2の場合、trueでNULLを、そうでなければ1個目の引数を返す。IFNULLと逆。
 <code>NULLIF(引数1, 引数2);</code>
以下の CASE 文と同等です。
 <CASE 
 	WHEN 引数1 = 引数2 
 		THEN NULL 
 		ELSE 引数1
 END;
なお、Oracle databaseの場合、NVL/NVL2という関数もある ([https://qiita.com/waokitsune/items/08d955741783f31eb31d OracleのNULL置換（NVL、NVL2、COALESCE、三者の違いやどれを使うか選ぶ時の参考など） #oracle - Qiita])。

=== 日付範囲 ===

* [https://dbflute.seasar.org/ja/manual/function/genbafit/implfit/datefromto/ 日付範囲の条件指定を定型化(安全に) | DBFlute]
* [https://zenn.dev/shuhei_takada/articles/d1444e409330ad SQLで日付の範囲指定をしたいときにありがちなミス]

注意点がある。

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

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

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

=== Echo/SELECTだけでtable作成 ===

* [https://takuya-1st.hatenablog.jp/entry/2017/01/18/133015 SQLでecho/print するための代替方法 - それマグで！]

* [https://qiita.com/Hiraku/items/aafc3b58d2efa4e97511 SQL SELECT基礎(3) その場で表を作る #SQL - Qiita]

SQLでechoのようにリテラル表示したいことがある。
 select '##  A ' as <nowiki>''</nowiki>;
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の表を作れる。

=== 引用符 ===
[https://qiita.com/Ping/items/d5d8468dadd9c1287f5e DBごとのSQLのクォーテーションを整理したった #MySQL - Qiita]
{| class="wikitable"
!
!'シングル'
!"ダブル"
!<code>`バック`</code>
![大カッコ]
!備考
|-
|MySQL
|[[/dev.mysql.com/doc/refman/5.6/ja/string-literals.html|文字列定数]]
|[[/dev.mysql.com/doc/refman/5.6/ja/string-literals.html|文字列定数]]
|[[/dev.mysql.com/doc/refman/5.6/ja/identifiers.html|引用識別子]]
|（なし）
|引用識別子はUNIX系では大文字小文字を区別[[/qiita.com/Ping/items/d5d8468dadd9c1287f5e#fn1|1]]
|-
|MySQL
（[[/dev.mysql.com/doc/refman/5.6/ja/sql-mode.html#sql-mode-full|ANSI_QUOTES モード]]）
|文字列定数
|引用識別子
|引用識別子
|（なし）
|
|-
|PostgreSQL
|[[/www.postgresql.jp/document/11/html/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS|文字列定数]]
|[[/www.postgresql.jp/document/11/html/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS|引用識別子]]
|（なし）
|（なし）
|引用識別子では大文字小文字を区別
|-
|Oracle
|[[/dev.mysql.com/doc/refman/5.6/ja/sql-mode.html#sql-mode-full|文字列定数]]
|[[/docs.oracle.com/cd/E96517 01/sqlrf/Database-Object-Names-and-Qualifiers.html#GUID-75337742-67FD-4EC0-985F-741C93D918DA|引用識別子]]
|（なし）
|（なし）
|引用識別子では大文字小文字を区別
|-
|[[/www.sqlite.org/lang keywords.html|SQLite]]
|文字列定数
|引用識別子
|引用識別子
|引用識別子
|
|-
|Microsoft SQL Server
(Transact-SQL)
|文字列定数
|引用識別子
|（なし）
|引用識別子
|QUOTED_IDENTIFIER が ON (既定値) の場合
|}
基本的に、以下と思っておけばいい。

* 一重引用符: 文字列定数

* 二重引用符: 引用識別子

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

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

=== 引用識別子 ===
[https://dev.mysql.com/doc/refman/8.0/ja/identifiers.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 9.2 スキーマオブジェクト名]

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

識別子の条件は以下。

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

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

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

=== COMMENT ===
[https://analytics-okinawa.jp/sql/1076/ SQL記述文内でコメントアウトする。単一行・複数行用の書き方 | アナリティクス沖縄│DataAnalytics]

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

* [-- ]: ハイフン2個+スペースの後がコメント扱い。
* /* */: ブロックコメントアウト。
他に、テーブルやカラムにはCOMMENT文でコメントを残すことができる。

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

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

=== 行番号 ===

* [https://qiita.com/sand_bash/items/851f08a36381a4f01b75 MySQLのSELECT結果に行番号を振る。 #mariadb - Qiita]
* [https://qiita.com/taka777n/items/f473450b8623dd1f43d2 MySQL8.0で便利なROW_NUMBER()を使ってみた #MySQL8.0 - Qiita]

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)
{| class="wikitable"
!命令
!説明
|-
|CREATE
|テーブルを新規作成する
|-
|DROP
|既存のデータベースオブジェクトを削除する
|-
|ALTER
|既存のデータベースオブジェクトを変更する
|-
|TRUNCATE
|テーブル内のデータを全削除する
|-
|RENAME
|既存のデータベースのオブジェクトの名前を変更する
|-
|COMMENT
|データベースのオブジェクトのコメントを定義、または変更する
|}

==== CREATE TABLE ====

===== 複合制約 =====
[https://letitride.hatenablog.com/entry/20120907/1347013609 複合unique制約のつけ方 - テックメモ跡地]
 CREATE TABLE example(
     column1    SERIAL       PRIMARY KEY,
     column2    INTEGER      NOT NULL,
     column3    INTEGER      NOT NULL,
     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 ====

* [https://stackoverflow.com/questions/16179578/check-if-table-exist-using-pdo php - Check if table exist using PDO - Stack Overflow]
* [https://stackoverflow.com/questions/1227921/portable-sql-to-determine-if-a-table-exists-or-not portability - Portable SQL to determine if a table exists or not? - Stack Overflow]

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

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

* information_schema.tables
* MySQL: SHOW TABLES
* SQLite: .tables/SELECT * FROM dbname.sqlite_master WHERE type='table' [[https://stackoverflow.com/questions/82875/how-can-i-list-the-tables-in-a-sqlite-database-file-that-was-opened-with-attach sql - How can I list the tables in a SQLite database file that was opened with ATTACH? - Stack Overflow]];
* [https://stackoverflow.com/questions/769683/how-to-show-tables-in-postgresql 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毎の独自構文を理解する必要がある。

[https://analytics-okinawa.jp/sql/1010/#index_id19 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 ======
[https://www.postgresql.jp/document/16/html/sql-altertable.html ALTER TABLE]
 ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
     ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]

====== MySQL ======
[https://dev.mysql.com/doc/refman/8.4/en/alter-table.html#alter-table-redefine-column 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 ======

* [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,
     ...
 );

 -- 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 =====
[https://dev.mysql.com/doc/refman/8.0/ja/example-auto-increment.html 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);
テーブルをコピーして練習してからのほうがいいかも。

==== 削除 ====
[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 ===

==== String ====

===== LPAD/RPAD =====
SQL-2023標準
 LPAD(カラム, 桁数, 埋め文字)
 RPAD(カラム, 桁数, 埋め文字)

===== LEFT/RIGHT =====
SQL-2023標準。

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

===== CONCAT =====
* [https://www.oreilly.com/library/view/sql-in-a/9780596155322/re92.html Concatenation Operator - SQL in a Nutshell, 3rd Edition [Book]]
* [https://dev.mysql.com/doc/refman/8.0/ja/logical-operators.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.4.3 論理演算子]
* [https://proengineer.internous.co.jp/content/columnfeature/6836#section200 MySQLで使える文字列結合方法 - プロエンジニア]
* [https://stackoverflow.com/questions/1373238/string-concatenation-operator-in-oracle-postgres-and-sql-server String concatenation operator in Oracle, Postgres and SQL Server - Stack Overflow]

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

* ||: 標準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の場合。

* [https://dev.mysql.com/doc/refman/8.0/ja/user-variables.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 9.4 ユーザー定義変数]
* [https://dev.mysql.com/doc/refman/8.0/ja/set-variable.html MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.6.1 変数代入の SET 構文]

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

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

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

ただ、短縮時に便利なことがある ([https://stackoverflow.com/questions/54979058/getting-values-from-previous-rows-using-user-defined-variable-in-mysql 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で定義するしかない ([https://qiita.com/kyamadahoge/items/fdbc04790f620dddbf3c MySQLでSQL一発でグループごとに連番を振る #MySQL8.0 - Qiita])。
 SELECT
 <nowiki> </nowiki> @rownum := IF(@prev_group_id = group_id, @rownum + 1, 1) AS rownum, -- 連番の列
 <nowiki> </nowiki> history.id,
 <nowiki> </nowiki> @prev_group_id := group_id AS group_id,
 <nowiki> </nowiki> history.name,
 <nowiki> </nowiki> history.created_at
 FROM
 <nowiki> </nowiki> history,
 <nowiki> </nowiki> (select @rownum := 0) hoge,
 <nowiki> </nowiki> (select @prev_group_id := <nowiki>''</nowiki>) fuga
 ORDER BY
 <nowiki> </nowiki> group_id,
 <nowiki> </nowiki> created_at DESC
変数は定義しないと型が認識されなくて、値が更新されないらしい。

==== 階層テーブルの取得 ====
[https://medium.com/geekculture/sql-interview-question8-parent-child-relationship-use-case-of-self-join-4fe88146dd85 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句で実現するのが基本。
{| class="wikitable"
|+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;
{| class="wikitable"
!product_id
!Jan
!Feb
!Mar
|-
|1
|100
|150
|120
|-
|2
|200
|250
|210
|}
値が決まっているならこの方法でもいい。

==== 複数行データの複数列 ====

* [http://oraclemaster.boo.jp/%E5%90%8C%E3%81%98%E3%82%AD%E3%83%BC%E9%A0%85%E7%9B%AE%E6%AF%8E%E3%81%AE%E7%B8%A6%E8%A4%87%E6%95%B0%E8%A1%8C%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E6%A8%AA%E8%A4%87%E6%95%B0%E5%88%97%E3%83%87%E3%83%BC/ 同じキー項目毎の縦複数行データを横複数列データに変換するSQL | オラクルマスター11g ブロンズ＆シルバー ～取得のためにすべきこと～]
* [https://qiita.com/UMA9626/items/4eef67d7c57e6cd89e6e 複数レコードを単一レコード・複数カラムにする #SQL - Qiita]
ピボットターブルに近いが、値が決まっていない場合の行の列への変換。
 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する？
{| class="wikitable"
|括りオーナーコード
|代表オーナーコード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
これでうまくいく。
{| class="wikitable"
|括りオーナーコード
|代表オーナーコード
|代表オーナーコード
|-
|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

うまくいっていない。こういうデータが残って重複が残る。
{| class="wikitable"
|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 (
 <nowiki> </nowiki>   SELECT 
 `括り代表オーナーコード`, 
 @rownum := IF(@prev_group_id = `括りオーナーコード`, @rownum + 1, 1) AS rownum, 
 @prev_group_id := `括りオーナーコード` AS 括りオーナーコード,
 `代表オーナーコード`
 FROM `楽楽販売_括りオーナーマスタ_楽楽販売_代表オーナーマスタ`, (SELECT @rownum:=0) hoge, (SELECT @prev_group_id:=<nowiki>''</nowiki>) fuga  
 ORDER BY 括りオーナーコード, 括り代表オーナーコード
 ) numbered
 GROUP BY 括りオーナーコード;
{| class="wikitable"
|括りオーナーコード
|代表オーナーコード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 ===

==== カラム名検索 ====
[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 データ型]

データ型の書式がある。

* 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

* [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]

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

[https://zenn.dev/ikeo/articles/9b4d2513c0528f MySQLでAUTO_INCREMENTが指定されているカラムに「0」を入れるには]

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

==== Cast ====

* [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 キャスト関数と演算子]

暗黙の型変換がある。

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

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

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

==== 数値型の属性 ====

* [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/廃止予定扱い。使っちゃダメ。

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

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

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のテキストファイルとして出力する。

[https://dev.mysql.com/doc/refman/8.0/ja/mysqldump.html 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] ====
* [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]

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

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

==== Numeric value out of range: 1264 Out of range value for column ====
[https://qiita.com/mindwood/items/9a47dfabd0c5cebf1a62 "Out of range value for column 'カラム名' at 行番号" に対処した #MySQL - Qiita]

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

=== Config ===

==== Character ====
[https://qiita.com/tfunato/items/e48ad0a37b8244a788f6 MySQLの文字コードとCollation #MySQL - Qiita]

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

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

A ≠ a 区別する

≠  区別する

は≠ぱ≠ば 区別する

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

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

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

==== SQL mode ====
[https://dev.mysql.com/doc/refman/8.0/ja/sql-mode.html#sql-mode-combo 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 ====
[https://qiita.com/kazu56/items/6af85ffcf8d3954455ad 【MySQL】照合順序とは？ #MySQL - Qiita]

[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]

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 ([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

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 =====
[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とかの言語名部分の違いが上記に記載がある。

なお、日本語の異体字は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からは自動では作られない。

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

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

==== 予約語 ====

* 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]

MySQLは組込関数がたくさんあり、それらが全て予約語。以下でも確認できる。
 select * from information_schema.KEYWORDS;
予約語をカラム名などに使う場合、バッククオートかANSIモードで二重引用符でエスケープが必要。

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

* rank

== Oracle ==

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

==== 2 表と表クラスタ ====
[https://docs.oracle.com/cd/F19136_01/cncpt/tables-and-table-clusters.html 表と表クラスタ]

===== 2.2. 表の概要 =====

====== 2.2.4. Oracleデータ型 ======
数値データ型

* NUMBER
* 浮動小数点数

NUMBERデータ型

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

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

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

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

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

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

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

==== 6 データ・ディクショナリと動的パフォーマンス・ビュー ====
[https://docs.oracle.com/cd/F19136_01/cncpt/data-dictionary-and-dynamic-performance-views.html データ・ディクショナリと動的パフォーマンス・ビュー]

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

* 列のデフォルト値、整合性制約情報など、データベース内の各[https://docs.oracle.com/cd/F19136&#x20;01/cncpt/Chunk505218552.html#GUID-EC18AB9A-E51B-4B90-941C-44F0C35851F5 スキーマ・オブジェクト]の定義
* スキーマ・オブジェクトに割り当てられている領域、およびスキーマ・オブジェクトによって現在使用されている領域の容量
* Oracle Databaseユーザーの名前、ユーザーに付与された権限とロール、およびユーザーに関連する監査情報

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

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

表6-1 データ・ディクショナリ・ビューのセット
{| class="wikitable"
!接頭辞
!ユーザー・アクセス
!内容
!ノート
|-
|<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 ======
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,
 <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) 

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

[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という機能がある。この機能を有効にすることで、ブックマークやコメントなどの機能が使えるようになる。

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クエリーにテーブル名を指定して表示。

===== テーブル定義出力 =====
[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 |
 +----+----------+--------------------+------------+
 9 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でアクセスできる。

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

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|<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]

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

[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): テキストエディターに表示するサイズ。これを増やすと表示に反映される。

[[Category:IT]]