LemonWiki共筆 - User contributions [en]

Finding Files That Take Up Space on Ubuntu

2026-07-02T06:17:17Z

Planetoid: Created page with "Finding Files That Take Up Space on Ubuntu == Graphical User Interface (GUI) == # [https://snapcraft.io/filelight filelight]: Lets you choose which folder to scan {{Gd}} # baobab: Scans the entire computer directly; if there are too many files, it can take a long time. == Command-Line Interface (CLI) == # `df -h` quickly shows the usage status of different partitions == References == * [https://snapcraft.io/blog/how-to-keep-your-linux-disk-usage-nice-and-tidy-and-save..."

Finding Files That Take Up Space on Ubuntu

== Graphical User Interface (GUI) ==
# [https://snapcraft.io/filelight filelight]: Lets you choose which folder to scan {{Gd}}
# baobab: Scans the entire computer directly; if there are too many files, it can take a long time.

== Command-Line Interface (CLI) ==
# `df -h` quickly shows the usage status of different partitions

== References ==
* [https://snapcraft.io/blog/how-to-keep-your-linux-disk-usage-nice-and-tidy-and-save-space How to keep your Linux disk usage nice and tidy and save space | Snapcraft]
* [https://discourse.ubuntu.com/t/trying-to-understand-disk-usage/74126 Trying to understand disk usage - Support and Help - Ubuntu Community Hub]

[[Category: Tool]]
[[Category: Ubuntu]]
[[Category: Linux]]

Open data file format

2026-07-02T03:38:22Z

Planetoid: Redirected page to Comparison of common data file formats in Mandarin

#REDIRECT [[Comparison of common data file formats in Mandarin]]

Comparison of common data file formats in Mandarin

2026-07-02T03:37:58Z

Planetoid: /* References */

拿到資料檔案該怎麼開？各格式限制與注意事項

{{LanguageSwitcher | content = [[Comparison of common data file formats | EN]], [[Comparison of common data file formats in Mandarin | 漢字]] }}

== 常見格式快速比較表 ==
<table class="wikitable">
<tr>
<th>格式</th>
<th>階層性</th>
<th>人類易讀</th>
<th>筆數/容量限制</th>
<th>常見問題</th>
</tr>
<tr>
<td>'''CSV'''</td>
<td>無（表格式）</td>
<td>高</td>
<td>格式本身無上限，取決於開啟軟體（Excel 約 104 萬列）</td>
<td>中文亂碼、換行符號欄位值易錯位、數字型別被強制轉換（如電話號碼開頭 0 消失）</td>
</tr>
<tr>
<td>'''TSV'''</td>
<td>無（表格式）</td>
<td>高</td>
<td>同 CSV，格式本身無上限</td>
<td>與 CSV 相同，但可避免欄位值含逗號時的分隔誤判</td>
</tr>
<tr>
<td>'''Excel'''</td>
<td>無（表格式）</td>
<td>高</td>
<td>理論上限 1,048,576 列 × 16,384 欄；單一儲存格文字上限 32,767 字；超過 10 萬筆操作易頓</td>
<td>長數字被轉成科學記號、特定字串誤判為日期</td>
</tr>
<tr>
<td>'''JSON'''</td>
<td>有</td>
<td>中</td>
<td>格式本身無筆數上限，瓶頸為讀取時須整檔載入記憶體</td>
<td>大檔案（如 250MB+）易記憶體不足；建議改用 JSONL 串流讀取</td>
</tr>
<tr>
<td>'''SQLite'''</td>
<td>無（關聯式）</td>
<td>低（需工具開啟）</td>
<td>理論上限 2⁶⁴ 筆，實際受資料庫檔案 281 TB 上限限制（約 2×10¹³ 筆）</td>
<td>需透過資料庫工具讀寫，不利直接檢視</td>
</tr>
<tr>
<td>'''Parquet'''</td>
<td>無（欄式）</td>
<td>低（需工具開啟）</td>
<td>格式本身無理論筆數上限（以 Row Group 為單位，預設每組 100 萬筆，可有任意組數）</td>
<td>實務瓶頸為硬碟空間，非格式限制；已有成功案例寫入 5～10 億筆</td>
</tr>
<tr>
<td>'''SQL 檔'''</td>
<td>無</td>
<td>中</td>
<td>純文字格式本身無上限，限制來自匯入工具</td>
<td>檔案過大時文字編輯器易當機；不同資料庫引擎語法相容性問題</td>
</tr>
</table>

'''建議：'''
# 需要巢狀/自由格式欄位 → JSON（大量資料改 JSONL）
# 資料量超過百萬筆、只需搬移資料 → Parquet 或 CSV
# 需要還原完整資料庫結構（含關聯、索引） → SQL 或 SQLite
# 一般使用試算表軟體開啟操作 → CSV / Excel，如果要使用 CSV / Excel 顯示階層結構，在欄位名稱可透過分隔符號呈現，例如 {{kbd | key=<nowiki>aaa / bbb</nowiki>}}

== 常見資料格式與限制 ==
=== 1. CSV 檔案 ===
CSV 檔案：不同欄位值用逗號間隔。
CSV 格式本身沒有筆數上限，限制來自開啟它的軟體。<ref>Row Zero — CSV Row Limits by Spreadsheet: https://rowzero.com/blog/csv-row-limit</ref>

常見問題是
大部分人會使用 Microsoft Excel 開啟 CSV， (1) 中文亂碼：點選兩下發現中文亂碼，檔首需要額外加上 BOM 符號，或者改用匯入檔案方式，才不會遇到亂碼、(2) 如果欄位值包含換行符號的話，Excel 容易會出錯，導致明明是同一筆資料，卻出現在下一筆而出現欄位錯置的錯誤。(3) 資料類型強迫轉換：常見的問題是手機號碼的第一位號碼 0 被吃掉，因為被強制轉換成整數。

如果你拿到是包含換行符號欄位值的 CSV 檔案，則不建議使用 Excel，而是改用 LibreOffice Calc。

=== 2. TSV 檔案 ===
TSV 檔案：不同欄位值用定位鍵 (TAB) 間隔。如果欄位值包含逗號，可以改用 TSV 檔案。
TSV 與 CSV 同屬純文字格式，筆數上限同樣取決於開啟的軟體，格式本身無限制。

=== 3. Excel 檔案===
Excel 檔案：常見的檔案類型，但是儲存格文字長度與欄位數量都有上限：單一儲存格內的文字不能超過 32,767 字，而欄位數上限是 16,384 欄 <ref>Microsoft Support — Excel specifications and limits: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3</ref>。其次雖然資料筆數理論上限是 1,048,576 筆（= 2²⁰），但如果筆數超過 10 萬筆，電腦操作 Excel 就會頓頓的，反而不方便分析。如果是舊版 Excel（2003 以前）允許的筆數更少，僅 65,536 筆（= 2¹⁶）× 256 欄。<ref>[Did You Know] Microsoft Excel has a Limit of Maximum 1,048,576 Rows and 16,384 Columns – AskVG https://www.askvg.com/did-you-know-microsoft-excel-has-a-limit-of-maximum-1048576-rows-and-16384-columns/</ref><ref>[https://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User]</ref>

常見問題是
Excel 雖然方便但經常會把長數字（例如：訂單編號）自動轉成科學記號顯示<ref>[https://errerrors.blogspot.com/2023/04/how-to-fix-rounding-after-15-digits-on-excel.html 解決 Excel 長數字的最後一位歸零或出現科學記號的問題]</ref>，或把特定字串（如 1-2、MAR3）誤判為日期<ref>[https://www.techbang.com/posts/110723-excel-human-genome Excel功能出包，迫使科學家必須重新命名人類基因！現在這個Bug被修復了 | T客邦]</ref>，造成資料遺失或資料錯誤。

=== 4. JSON 檔案 ===
JSON 檔案：適合複雜格式或者說自由風格的欄位定義。曾經拿到包含多筆資料，約 250 MB 檔案大小的 JSON 檔案。因為發生檔案讀取錯誤，需要額外切割檔案內容。
JSON 格式本身沒有筆數上限，瓶頸是讀取時必須將整個檔案載入記憶體，因此檔案愈大愈容易發生記憶體不足的錯誤。<ref>Quora — What is the practical limit on the size of a JSON message?: https://www.quora.com/What-is-the-practical-limit-on-the-size-of-a-JSON-message-for-an-internet-application</ref>

實務上建議不要將多筆資料寫在 JSON，而改成 JSONL，也就是 JSON 加上換行符號區別多筆資料。適合使用程式用串流方式逐行讀取，而不致於發生記憶體錯誤。

=== 5. SQLite 或 Parquet 檔案 ===
SQLite 或 Parquet 檔案：當資料量超過 Excel 筆數上限，可以考慮使用這兩種格式。

SQLite 每張資料表的理論筆數上限是 2⁶⁴ 筆（約 1.8 × 10¹⁹），但這個數字實際上不可能達到，因為資料庫檔案大小上限 281 TB 會先被遇到。在最大資料庫容量下，可儲存的筆數約為 2 × 10¹³ 筆（前提是沒有索引且每筆資料極小）。<ref>SQLite 官方文件 — Implementation Limits For SQLite: https://sqlite.org/limits.html</ref>

Parquet 沒有硬性的筆數上限。格式本身以 Row Group 為單位儲存資料。Row Group 是 Parquet 檔案內部的水平分割單位，每個 Row Group 包含一段連續的資料列，以欄位為單位分開存放——例如一個有 100 萬筆、10 個欄位的資料集，在一個 Row Group 裡會被切成 10 段欄位資料分別儲存，而非逐列存放。這樣的設計讓查詢時只需讀取需要的欄位，不必掃描整列，大幅提升讀取效率。每個 Row Group 預設上限為 100 萬筆，但一個檔案可以包含任意數量的 Row Group，因此整個檔案的筆數沒有理論上限 <ref>Apache arrow-rs GitHub issue #5797 — "Row groups are limited to 1M rows by default": https://github.com/apache/arrow-rs/issues/5797</ref>。實務上已有人成功寫入 5 億~ 10 億筆資料<ref>Andy Cutler — 10 Billion Rows: Parquet File Size and Distribution When using CETAS: https://www.serverlesssql.com/row-size-and-parquet-file-distribution/</ref>，瓶頸通常是硬碟空間，而非格式本身。

=== 6. SQL 檔案 ===
SQL 檔案：關聯式資料庫的匯出格式，內容為多列的 SQL 語法（如 {{kbd | key=<nowiki>INSERT INTO ...</nowiki>}}），可直接匯入至資料庫。

SQL 檔案本身沒有筆數上限，格式本身只是純文字，限制同樣來自開啟或匯入它的工具。

常見問題是
* (1) 檔案過大：大量資料匯出後動輒數 GB，用文字編輯器開啟會導致當機或無回應。
* (2) 跨資料庫相容性：不同資料庫引擎（如 MySQL、PostgreSQL、SQLite）的 SQL 語法有差異，從 A 引擎匯出的 .sql 檔案，未必能直接匯入另一個引擎。可參考 [[排除_MySQL_技術疑難問題#從_MySQL_資料轉移至_MSSQL | 從_MySQL_資料轉移至_MSSQL]]

如果只需要搬移資料而非還原完整資料庫結構，建議改用 CSV 或 Parquet，避免上述相容性問題。

== 延伸閱讀 ==
* [http://opendatahandbook.org/guide/zh_TW/appendices/file-formats/ 檔案格式]
* [https://blog.othree.net/log/2016/01/11/json-universe/ JSON Universe : O3noBLOG]
* [[Open_Source_Community#.E9.96.8B.E6.94.BE.E8.B3.87.E6.96.99_.2F_.E5.B7.A8.E9.87.8F.E8.B3.87.E6.96.99|開放資料相關社群]]
* [https://www.loc.gov/preservation/resources/rfs/TOC.html Recommended Formats Statement – table of contents | Resources (Preservation, Library of Congress)]

== References ==
<references />

{{Template:Data factory flow}}

[[Category: Software]]
[[Category: Spreadsheet]]
[[Category: MySQL]]
[[Category: Data Science]]
[[Category: Revised with LLMs]]

Comparison of common data file formats in Mandarin

2026-07-02T03:37:33Z

Planetoid: /* 常見格式快速比較表 */

拿到資料檔案該怎麼開？各格式限制與注意事項

{{LanguageSwitcher | content = [[Comparison of common data file formats | EN]], [[Comparison of common data file formats in Mandarin | 漢字]] }}

== 常見格式快速比較表 ==
<table class="wikitable">
<tr>
<th>格式</th>
<th>階層性</th>
<th>人類易讀</th>
<th>筆數/容量限制</th>
<th>常見問題</th>
</tr>
<tr>
<td>'''CSV'''</td>
<td>無（表格式）</td>
<td>高</td>
<td>格式本身無上限，取決於開啟軟體（Excel 約 104 萬列）</td>
<td>中文亂碼、換行符號欄位值易錯位、數字型別被強制轉換（如電話號碼開頭 0 消失）</td>
</tr>
<tr>
<td>'''TSV'''</td>
<td>無（表格式）</td>
<td>高</td>
<td>同 CSV，格式本身無上限</td>
<td>與 CSV 相同，但可避免欄位值含逗號時的分隔誤判</td>
</tr>
<tr>
<td>'''Excel'''</td>
<td>無（表格式）</td>
<td>高</td>
<td>理論上限 1,048,576 列 × 16,384 欄；單一儲存格文字上限 32,767 字；超過 10 萬筆操作易頓</td>
<td>長數字被轉成科學記號、特定字串誤判為日期</td>
</tr>
<tr>
<td>'''JSON'''</td>
<td>有</td>
<td>中</td>
<td>格式本身無筆數上限，瓶頸為讀取時須整檔載入記憶體</td>
<td>大檔案（如 250MB+）易記憶體不足；建議改用 JSONL 串流讀取</td>
</tr>
<tr>
<td>'''SQLite'''</td>
<td>無（關聯式）</td>
<td>低（需工具開啟）</td>
<td>理論上限 2⁶⁴ 筆，實際受資料庫檔案 281 TB 上限限制（約 2×10¹³ 筆）</td>
<td>需透過資料庫工具讀寫，不利直接檢視</td>
</tr>
<tr>
<td>'''Parquet'''</td>
<td>無（欄式）</td>
<td>低（需工具開啟）</td>
<td>格式本身無理論筆數上限（以 Row Group 為單位，預設每組 100 萬筆，可有任意組數）</td>
<td>實務瓶頸為硬碟空間，非格式限制；已有成功案例寫入 5～10 億筆</td>
</tr>
<tr>
<td>'''SQL 檔'''</td>
<td>無</td>
<td>中</td>
<td>純文字格式本身無上限，限制來自匯入工具</td>
<td>檔案過大時文字編輯器易當機；不同資料庫引擎語法相容性問題</td>
</tr>
</table>

'''建議：'''
# 需要巢狀/自由格式欄位 → JSON（大量資料改 JSONL）
# 資料量超過百萬筆、只需搬移資料 → Parquet 或 CSV
# 需要還原完整資料庫結構（含關聯、索引） → SQL 或 SQLite
# 一般使用試算表軟體開啟操作 → CSV / Excel，如果要使用 CSV / Excel 顯示階層結構，在欄位名稱可透過分隔符號呈現，例如 {{kbd | key=<nowiki>aaa / bbb</nowiki>}}

== 常見資料格式與限制 ==
=== 1. CSV 檔案 ===
CSV 檔案：不同欄位值用逗號間隔。
CSV 格式本身沒有筆數上限，限制來自開啟它的軟體。<ref>Row Zero — CSV Row Limits by Spreadsheet: https://rowzero.com/blog/csv-row-limit</ref>

常見問題是
大部分人會使用 Microsoft Excel 開啟 CSV， (1) 中文亂碼：點選兩下發現中文亂碼，檔首需要額外加上 BOM 符號，或者改用匯入檔案方式，才不會遇到亂碼、(2) 如果欄位值包含換行符號的話，Excel 容易會出錯，導致明明是同一筆資料，卻出現在下一筆而出現欄位錯置的錯誤。(3) 資料類型強迫轉換：常見的問題是手機號碼的第一位號碼 0 被吃掉，因為被強制轉換成整數。

如果你拿到是包含換行符號欄位值的 CSV 檔案，則不建議使用 Excel，而是改用 LibreOffice Calc。

=== 2. TSV 檔案 ===
TSV 檔案：不同欄位值用定位鍵 (TAB) 間隔。如果欄位值包含逗號，可以改用 TSV 檔案。
TSV 與 CSV 同屬純文字格式，筆數上限同樣取決於開啟的軟體，格式本身無限制。

=== 3. Excel 檔案===
Excel 檔案：常見的檔案類型，但是儲存格文字長度與欄位數量都有上限：單一儲存格內的文字不能超過 32,767 字，而欄位數上限是 16,384 欄 <ref>Microsoft Support — Excel specifications and limits: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3</ref>。其次雖然資料筆數理論上限是 1,048,576 筆（= 2²⁰），但如果筆數超過 10 萬筆，電腦操作 Excel 就會頓頓的，反而不方便分析。如果是舊版 Excel（2003 以前）允許的筆數更少，僅 65,536 筆（= 2¹⁶）× 256 欄。<ref>[Did You Know] Microsoft Excel has a Limit of Maximum 1,048,576 Rows and 16,384 Columns – AskVG https://www.askvg.com/did-you-know-microsoft-excel-has-a-limit-of-maximum-1048576-rows-and-16384-columns/</ref><ref>[https://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User]</ref>

常見問題是
Excel 雖然方便但經常會把長數字（例如：訂單編號）自動轉成科學記號顯示<ref>[https://errerrors.blogspot.com/2023/04/how-to-fix-rounding-after-15-digits-on-excel.html 解決 Excel 長數字的最後一位歸零或出現科學記號的問題]</ref>，或把特定字串（如 1-2、MAR3）誤判為日期<ref>[https://www.techbang.com/posts/110723-excel-human-genome Excel功能出包，迫使科學家必須重新命名人類基因！現在這個Bug被修復了 | T客邦]</ref>，造成資料遺失或資料錯誤。

=== 4. JSON 檔案 ===
JSON 檔案：適合複雜格式或者說自由風格的欄位定義。曾經拿到包含多筆資料，約 250 MB 檔案大小的 JSON 檔案。因為發生檔案讀取錯誤，需要額外切割檔案內容。
JSON 格式本身沒有筆數上限，瓶頸是讀取時必須將整個檔案載入記憶體，因此檔案愈大愈容易發生記憶體不足的錯誤。<ref>Quora — What is the practical limit on the size of a JSON message?: https://www.quora.com/What-is-the-practical-limit-on-the-size-of-a-JSON-message-for-an-internet-application</ref>

實務上建議不要將多筆資料寫在 JSON，而改成 JSONL，也就是 JSON 加上換行符號區別多筆資料。適合使用程式用串流方式逐行讀取，而不致於發生記憶體錯誤。

=== 5. SQLite 或 Parquet 檔案 ===
SQLite 或 Parquet 檔案：當資料量超過 Excel 筆數上限，可以考慮使用這兩種格式。

SQLite 每張資料表的理論筆數上限是 2⁶⁴ 筆（約 1.8 × 10¹⁹），但這個數字實際上不可能達到，因為資料庫檔案大小上限 281 TB 會先被遇到。在最大資料庫容量下，可儲存的筆數約為 2 × 10¹³ 筆（前提是沒有索引且每筆資料極小）。<ref>SQLite 官方文件 — Implementation Limits For SQLite: https://sqlite.org/limits.html</ref>

Parquet 沒有硬性的筆數上限。格式本身以 Row Group 為單位儲存資料。Row Group 是 Parquet 檔案內部的水平分割單位，每個 Row Group 包含一段連續的資料列，以欄位為單位分開存放——例如一個有 100 萬筆、10 個欄位的資料集，在一個 Row Group 裡會被切成 10 段欄位資料分別儲存，而非逐列存放。這樣的設計讓查詢時只需讀取需要的欄位，不必掃描整列，大幅提升讀取效率。每個 Row Group 預設上限為 100 萬筆，但一個檔案可以包含任意數量的 Row Group，因此整個檔案的筆數沒有理論上限 <ref>Apache arrow-rs GitHub issue #5797 — "Row groups are limited to 1M rows by default": https://github.com/apache/arrow-rs/issues/5797</ref>。實務上已有人成功寫入 5 億~ 10 億筆資料<ref>Andy Cutler — 10 Billion Rows: Parquet File Size and Distribution When using CETAS: https://www.serverlesssql.com/row-size-and-parquet-file-distribution/</ref>，瓶頸通常是硬碟空間，而非格式本身。

=== 6. SQL 檔案 ===
SQL 檔案：關聯式資料庫的匯出格式，內容為多列的 SQL 語法（如 {{kbd | key=<nowiki>INSERT INTO ...</nowiki>}}），可直接匯入至資料庫。

SQL 檔案本身沒有筆數上限，格式本身只是純文字，限制同樣來自開啟或匯入它的工具。

常見問題是
* (1) 檔案過大：大量資料匯出後動輒數 GB，用文字編輯器開啟會導致當機或無回應。
* (2) 跨資料庫相容性：不同資料庫引擎（如 MySQL、PostgreSQL、SQLite）的 SQL 語法有差異，從 A 引擎匯出的 .sql 檔案，未必能直接匯入另一個引擎。可參考 [[排除_MySQL_技術疑難問題#從_MySQL_資料轉移至_MSSQL | 從_MySQL_資料轉移至_MSSQL]]

如果只需要搬移資料而非還原完整資料庫結構，建議改用 CSV 或 Parquet，避免上述相容性問題。

== References ==
<references />

{{Template:Data factory flow}}

[[Category: Software]]
[[Category: Spreadsheet]]
[[Category: MySQL]]
[[Category: Data Science]]
[[Category: Revised with LLMs]]

Comparison of common data file formats

2026-07-02T03:35:40Z

Planetoid: /* Common Data Formats and Limitations */

How to Open Data Files: Common Formats, Limitations, and Notes

{{LanguageSwitcher | content = [[Comparison of common data file formats | EN]], [[Comparison of common data file formats in Mandarin | 漢字]] }}

== Quick Comparison Table of Common Formats ==
<table class="wikitable">
<tr>
<th>Format</th>
<th>Hierarchical</th>
<th>Human-Readable</th>
<th>Row Count / Size Limit</th>
<th>Common Issues</th>
</tr>
<tr>
<td>'''CSV'''</td>
<td>No (tabular)</td>
<td>High</td>
<td>No inherent limit in the format itself; depends on the software used to open it (Excel: ~1.04 million rows)</td>
<td>Garbled text in non-English characters; newline characters within field values can cause row misalignment; data type coercion (e.g., leading zero in phone numbers gets dropped)</td>
</tr>
<tr>
<td>'''TSV'''</td>
<td>No (tabular)</td>
<td>High</td>
<td>Same as CSV; no inherent limit in the format itself</td>
<td>Same issues as CSV, but avoids delimiter misinterpretation when field values contain commas</td>
</tr>
<tr>
<td>'''Excel'''</td>
<td>No (tabular)</td>
<td>High</td>
<td>Theoretical limit of 1,048,576 rows × 16,384 columns; 32,767 character limit per cell; performance lags once row count exceeds ~100,000</td>
<td>Long numbers get converted to scientific notation; certain strings get misinterpreted as dates</td>
</tr>
<tr>
<td>'''JSON'''</td>
<td>Yes</td>
<td>Medium</td>
<td>No inherent row limit in the format itself; the bottleneck is that the entire file must be loaded into memory when read</td>
<td>Large files (250MB+) are prone to out-of-memory errors; JSONL is recommended for streaming/line-by-line reading instead</td>
</tr>
<tr>
<td>'''SQLite'''</td>
<td>No (relational)</td>
<td>Low (requires a tool to open)</td>
<td>Theoretical limit of 2⁶⁴ rows, but in practice constrained by the 281 TB database file size limit (roughly 2×10¹³ rows)</td>
<td>Requires a database tool for reading/writing; not convenient for direct viewing</td>
</tr>
<tr>
<td>'''Parquet'''</td>
<td>No (columnar)</td>
<td>Low (requires a tool to open)</td>
<td>No theoretical row limit in the format itself (organized by Row Groups, default limit of 1 million rows per group, but a file can contain any number of Row Groups)</td>
<td>The practical bottleneck is disk space rather than the format itself; there are documented cases of successfully writing 500 million to 1 billion rows</td>
</tr>
<tr>
<td>'''SQL file'''</td>
<td>No</td>
<td>Medium</td>
<td>No inherent limit as plain text; the limit comes from the tool used to import it</td>
<td>Text editors can crash or become unresponsive when the file is too large; SQL syntax compatibility issues across different database engines</td>
</tr>
</table>

'''Recommendations:'''
# Need nested/free-form fields → JSON (use JSONL for large datasets)
# Data exceeds a million rows and you only need to move the data → Parquet or CSV
# Need to restore the full database structure (including relations and indexes) → SQL or SQLite
# General use with spreadsheet software → CSV / Excel

== Common Data Formats and Limitations ==

=== CSV Files ===
CSV files: different field values are separated by commas.
The CSV format itself has no row limit — the limit comes from the software used to open it.<ref>Row Zero — CSV Row Limits by Spreadsheet: https://rowzero.com/blog/csv-row-limit</ref>

Common issues arise because most people open CSV files with Microsoft Excel: (1) '''Garbled Chinese characters''': double-clicking may result in garbled text; a BOM character needs to be prepended to the file, or the file must be imported rather than opened directly. (2) '''Newline characters in field values''': if a field value contains a newline, Excel tends to misinterpret it, causing data from the same record to appear on the next row and resulting in column misalignment. (3) '''Forced type conversion''': a common issue is that the leading zero of a phone number is dropped because the value is coerced into an integer.

If you receive a CSV file containing newline characters in field values, it is not recommended to use Excel; use LibreOffice Calc instead.

=== TSV Files ===
TSV files: different field values are separated by tab characters. If field values contain commas, TSV can be used as an alternative.
TSV and CSV are both plain-text formats; the row limit likewise depends on the software used to open them, not the format itself.

=== Excel Files ===
Excel files: a widely used file format, but with limits on cell text length and column count: a single cell cannot contain more than 32,767 characters, and the maximum number of columns is 16,384.<ref>Microsoft Support — Excel specifications and limits: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3</ref> Although the theoretical row limit is 1,048,576 rows (= 2²⁰), performance becomes noticeably sluggish beyond 100,000 rows, making analysis inconvenient. Older versions of Excel (2003 and earlier) allowed even fewer rows — only 65,536 rows (= 2¹⁶) × 256 columns.<ref>[Did You Know] Microsoft Excel has a Limit of Maximum 1,048,576 Rows and 16,384 Columns – AskVG: https://www.askvg.com/did-you-know-microsoft-excel-has-a-limit-of-maximum-1048576-rows-and-16384-columns/</ref><ref>What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User: https://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx</ref>

Common issues include Excel automatically converting long numbers (e.g., order IDs) into scientific notation,<ref>Fixing the issue of long numbers being rounded or displayed in scientific notation in Excel (written in Mandarin): https://errerrors.blogspot.com/2023/04/how-to-fix-rounding-after-15-digits-on-excel.html</ref> or misidentifying certain strings (such as <code>1-2</code> or <code>MAR3</code>) as dates,<ref>Excel bug forces scientists to rename human genes — and the bug has now been fixed | T客邦 (written in Mandarin): https://www.techbang.com/posts/110723-excel-human-genome</ref> resulting in data loss or corruption.

=== JSON Files ===
JSON files: suitable for complex or flexibly structured field definitions. A JSON file containing multiple records with a size of approximately 250 MB has been encountered in practice; a read error occurred and the file had to be split before processing.
The JSON format itself has no row limit, but the bottleneck is that the entire file must be loaded into memory when read — the larger the file, the more likely an out-of-memory error will occur.<ref>Quora — What is the practical limit on the size of a JSON message?: https://www.quora.com/What-is-the-practical-limit-on-the-size-of-a-JSON-message-for-an-internet-application</ref>

In practice, it is recommended to avoid storing multiple records in a single JSON file and to use JSONL instead — that is, JSON with newline characters delimiting individual records. This format is suitable for streaming line-by-line with a program, preventing out-of-memory errors.

=== SQLite or Parquet Files ===
SQLite or Parquet files: when the data volume exceeds Excel's row limit, these two formats are worth considering.

The theoretical row limit per table in SQLite is 2⁶⁴ rows (approximately 1.8 × 10¹⁹), but this number is practically unreachable because the database file size limit of 281 TB will be hit first. At maximum database capacity, the number of storable rows is approximately 2 × 10¹³ (assuming no indexes and minimal row size).<ref>SQLite official documentation — Implementation Limits For SQLite: https://sqlite.org/limits.html</ref>

Parquet has no hard row limit. The format stores data in units called Row Groups — the horizontal partitioning unit within a Parquet file. Each Row Group contains a contiguous segment of rows stored column by column. For example, a dataset with 1,000,000 rows and 10 columns would be split into 10 separate column chunks within a single Row Group, rather than stored row by row. This design allows queries to read only the required columns without scanning entire rows, greatly improving read performance. Each Row Group defaults to a limit of 1,000,000 rows, but a single file can contain any number of Row Groups, so there is no theoretical row limit for the file as a whole.<ref>Apache arrow-rs GitHub issue #5797 — "Row groups are limited to 1M rows by default": https://github.com/apache/arrow-rs/issues/5797</ref> In practice, people have successfully written 500 million to 1 billion rows,<ref>Andy Cutler — 10 Billion Rows: Parquet File Size and Distribution When using CETAS: https://www.serverlesssql.com/row-size-and-parquet-file-distribution/</ref> with the bottleneck typically being disk space rather than the format itself.

=== SQL Files ===
SQL files: the export format for relational databases, consisting of multiple lines of SQL statements (e.g., <code>INSERT INTO ...</code>), which can be imported directly into a database.

SQL files themselves have no row limit; the format is plain text, and limitations again come from the tools used to open or import them.

Common issues include:
* '''(1) Large file size''': bulk data exports can easily reach several gigabytes; opening such files in a text editor may cause it to freeze or become unresponsive.
* '''(2) Cross-database compatibility''': different database engines (such as MySQL, PostgreSQL, and SQLite) have differences in SQL syntax; a <code>.sql</code> file exported from engine A may not be directly importable into another engine. See also: Troubleshooting MySQL Technical Issues — Migrating Data from MySQL to MSSQL (written in Mandarin).

If the goal is simply to migrate data rather than restore a complete database structure, it is recommended to use CSV or Parquet instead, in order to avoid the compatibility issues described above.

== References ==
<references />

{{Template:Data factory flow}}

[[Category: Software]]
[[Category: Spreadsheet]]
[[Category: MySQL]]
[[Category: Data Science]]
[[Category: Revised with LLMs]]

Comparison of common data file formats in Mandarin

2026-07-02T03:34:31Z

Planetoid:

拿到資料檔案該怎麼開？各格式限制與注意事項

{{LanguageSwitcher | content = [[Comparison of common data file formats | EN]], [[Comparison of common data file formats in Mandarin | 漢字]] }}

== 常見格式快速比較表 ==
<table class="wikitable">
<tr>
<th>格式</th>
<th>階層性</th>
<th>人類易讀</th>
<th>筆數/容量限制</th>
<th>常見問題</th>
</tr>
<tr>
<td>'''CSV'''</td>
<td>無（表格式）</td>
<td>高</td>
<td>格式本身無上限，取決於開啟軟體（Excel 約 104 萬列）</td>
<td>中文亂碼、換行符號欄位值易錯位、數字型別被強制轉換（如電話號碼開頭 0 消失）</td>
</tr>
<tr>
<td>'''TSV'''</td>
<td>無（表格式）</td>
<td>高</td>
<td>同 CSV，格式本身無上限</td>
<td>與 CSV 相同，但可避免欄位值含逗號時的分隔誤判</td>
</tr>
<tr>
<td>'''Excel'''</td>
<td>無（表格式）</td>
<td>高</td>
<td>理論上限 1,048,576 列 × 16,384 欄；單一儲存格文字上限 32,767 字；超過 10 萬筆操作易頓</td>
<td>長數字被轉成科學記號、特定字串誤判為日期</td>
</tr>
<tr>
<td>'''JSON'''</td>
<td>有</td>
<td>中</td>
<td>格式本身無筆數上限，瓶頸為讀取時須整檔載入記憶體</td>
<td>大檔案（如 250MB+）易記憶體不足；建議改用 JSONL 串流讀取</td>
</tr>
<tr>
<td>'''SQLite'''</td>
<td>無（關聯式）</td>
<td>低（需工具開啟）</td>
<td>理論上限 2⁶⁴ 筆，實際受資料庫檔案 281 TB 上限限制（約 2×10¹³ 筆）</td>
<td>需透過資料庫工具讀寫，不利直接檢視</td>
</tr>
<tr>
<td>'''Parquet'''</td>
<td>無（欄式）</td>
<td>低（需工具開啟）</td>
<td>格式本身無理論筆數上限（以 Row Group 為單位，預設每組 100 萬筆，可有任意組數）</td>
<td>實務瓶頸為硬碟空間，非格式限制；已有成功案例寫入 5～10 億筆</td>
</tr>
<tr>
<td>'''SQL 檔'''</td>
<td>無</td>
<td>中</td>
<td>純文字格式本身無上限，限制來自匯入工具</td>
<td>檔案過大時文字編輯器易當機；不同資料庫引擎語法相容性問題</td>
</tr>
</table>

'''建議：'''
# 需要巢狀/自由格式欄位 → JSON（大量資料改 JSONL）
# 資料量超過百萬筆、只需搬移資料 → Parquet 或 CSV
# 需要還原完整資料庫結構（含關聯、索引） → SQL 或 SQLite
# 一般使用試算表軟體開啟操作 → CSV / Excel

== 常見資料格式與限制 ==
=== 1. CSV 檔案 ===
CSV 檔案：不同欄位值用逗號間隔。
CSV 格式本身沒有筆數上限，限制來自開啟它的軟體。<ref>Row Zero — CSV Row Limits by Spreadsheet: https://rowzero.com/blog/csv-row-limit</ref>

常見問題是
大部分人會使用 Microsoft Excel 開啟 CSV， (1) 中文亂碼：點選兩下發現中文亂碼，檔首需要額外加上 BOM 符號，或者改用匯入檔案方式，才不會遇到亂碼、(2) 如果欄位值包含換行符號的話，Excel 容易會出錯，導致明明是同一筆資料，卻出現在下一筆而出現欄位錯置的錯誤。(3) 資料類型強迫轉換：常見的問題是手機號碼的第一位號碼 0 被吃掉，因為被強制轉換成整數。

如果你拿到是包含換行符號欄位值的 CSV 檔案，則不建議使用 Excel，而是改用 LibreOffice Calc。

=== 2. TSV 檔案 ===
TSV 檔案：不同欄位值用定位鍵 (TAB) 間隔。如果欄位值包含逗號，可以改用 TSV 檔案。
TSV 與 CSV 同屬純文字格式，筆數上限同樣取決於開啟的軟體，格式本身無限制。

=== 3. Excel 檔案===
Excel 檔案：常見的檔案類型，但是儲存格文字長度與欄位數量都有上限：單一儲存格內的文字不能超過 32,767 字，而欄位數上限是 16,384 欄 <ref>Microsoft Support — Excel specifications and limits: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3</ref>。其次雖然資料筆數理論上限是 1,048,576 筆（= 2²⁰），但如果筆數超過 10 萬筆，電腦操作 Excel 就會頓頓的，反而不方便分析。如果是舊版 Excel（2003 以前）允許的筆數更少，僅 65,536 筆（= 2¹⁶）× 256 欄。<ref>[Did You Know] Microsoft Excel has a Limit of Maximum 1,048,576 Rows and 16,384 Columns – AskVG https://www.askvg.com/did-you-know-microsoft-excel-has-a-limit-of-maximum-1048576-rows-and-16384-columns/</ref><ref>[https://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User]</ref>

常見問題是
Excel 雖然方便但經常會把長數字（例如：訂單編號）自動轉成科學記號顯示<ref>[https://errerrors.blogspot.com/2023/04/how-to-fix-rounding-after-15-digits-on-excel.html 解決 Excel 長數字的最後一位歸零或出現科學記號的問題]</ref>，或把特定字串（如 1-2、MAR3）誤判為日期<ref>[https://www.techbang.com/posts/110723-excel-human-genome Excel功能出包，迫使科學家必須重新命名人類基因！現在這個Bug被修復了 | T客邦]</ref>，造成資料遺失或資料錯誤。

=== 4. JSON 檔案 ===
JSON 檔案：適合複雜格式或者說自由風格的欄位定義。曾經拿到包含多筆資料，約 250 MB 檔案大小的 JSON 檔案。因為發生檔案讀取錯誤，需要額外切割檔案內容。
JSON 格式本身沒有筆數上限，瓶頸是讀取時必須將整個檔案載入記憶體，因此檔案愈大愈容易發生記憶體不足的錯誤。<ref>Quora — What is the practical limit on the size of a JSON message?: https://www.quora.com/What-is-the-practical-limit-on-the-size-of-a-JSON-message-for-an-internet-application</ref>

實務上建議不要將多筆資料寫在 JSON，而改成 JSONL，也就是 JSON 加上換行符號區別多筆資料。適合使用程式用串流方式逐行讀取，而不致於發生記憶體錯誤。

=== 5. SQLite 或 Parquet 檔案 ===
SQLite 或 Parquet 檔案：當資料量超過 Excel 筆數上限，可以考慮使用這兩種格式。

SQLite 每張資料表的理論筆數上限是 2⁶⁴ 筆（約 1.8 × 10¹⁹），但這個數字實際上不可能達到，因為資料庫檔案大小上限 281 TB 會先被遇到。在最大資料庫容量下，可儲存的筆數約為 2 × 10¹³ 筆（前提是沒有索引且每筆資料極小）。<ref>SQLite 官方文件 — Implementation Limits For SQLite: https://sqlite.org/limits.html</ref>

Parquet 沒有硬性的筆數上限。格式本身以 Row Group 為單位儲存資料。Row Group 是 Parquet 檔案內部的水平分割單位，每個 Row Group 包含一段連續的資料列，以欄位為單位分開存放——例如一個有 100 萬筆、10 個欄位的資料集，在一個 Row Group 裡會被切成 10 段欄位資料分別儲存，而非逐列存放。這樣的設計讓查詢時只需讀取需要的欄位，不必掃描整列，大幅提升讀取效率。每個 Row Group 預設上限為 100 萬筆，但一個檔案可以包含任意數量的 Row Group，因此整個檔案的筆數沒有理論上限 <ref>Apache arrow-rs GitHub issue #5797 — "Row groups are limited to 1M rows by default": https://github.com/apache/arrow-rs/issues/5797</ref>。實務上已有人成功寫入 5 億~ 10 億筆資料<ref>Andy Cutler — 10 Billion Rows: Parquet File Size and Distribution When using CETAS: https://www.serverlesssql.com/row-size-and-parquet-file-distribution/</ref>，瓶頸通常是硬碟空間，而非格式本身。

=== 6. SQL 檔案 ===
SQL 檔案：關聯式資料庫的匯出格式，內容為多列的 SQL 語法（如 {{kbd | key=<nowiki>INSERT INTO ...</nowiki>}}），可直接匯入至資料庫。

SQL 檔案本身沒有筆數上限，格式本身只是純文字，限制同樣來自開啟或匯入它的工具。

常見問題是
* (1) 檔案過大：大量資料匯出後動輒數 GB，用文字編輯器開啟會導致當機或無回應。
* (2) 跨資料庫相容性：不同資料庫引擎（如 MySQL、PostgreSQL、SQLite）的 SQL 語法有差異，從 A 引擎匯出的 .sql 檔案，未必能直接匯入另一個引擎。可參考 [[排除_MySQL_技術疑難問題#從_MySQL_資料轉移至_MSSQL | 從_MySQL_資料轉移至_MSSQL]]

如果只需要搬移資料而非還原完整資料庫結構，建議改用 CSV 或 Parquet，避免上述相容性問題。

== References ==
<references />

{{Template:Data factory flow}}

[[Category: Software]]
[[Category: Spreadsheet]]
[[Category: MySQL]]
[[Category: Data Science]]
[[Category: Revised with LLMs]]

Check Browser Compatibility

2026-07-01T10:24:52Z

Planetoid: /* Checking the compatibility */

Check Browser Compatibility

Noun explanation
# allowed to interact: the solutions which (1) allowed to interact the web pages e.g. click the link and test javascript (2) view the web snapshot
# view the snapshot only: only allowed to view the static web snapshot

==== Checking the compatibility of web page ====
* {{Gd}} [http://www.caniuse.com/ Can I use... Support tables for HTML5, CSS3, etc] "Compatibility tables for support of HTML5, CSS3, SVG and more in desktop and mobile browsers." {{access | date=2020-08-23}}
* [http://blog.miniasp.com/post/2013/07/14/HTML5-CSS3-JavaScript-JS-compatibility-resouces.aspx The Will Will Web | 前端工程研究：HTML/CSS/JavaScript 相容性檢查資源整理]

==== Checking the compatibility of email ====
* {{Gd}} [https://www.caniemail.com/ Can I email… Support tables for HTML and CSS in emails]
* [https://email-compatibility-checker.planetoid.info/ Email Newsletter Compatibility Checker]

==== allowed to interact ====
===== desktop browser + mobile browser =====
cloud services for testing desktop browsers and mobile browsers
* [http://www.browserstack.com/ BrowserStack]''($)'' "Cross Browser Testing Tool. 100+ Browsers, Mobile, Real IE." [http://www.browserstack.com/list-of-browsers-and-platforms List of Browsers and Platforms for Cross Browser Testing]
** [https://chrome.google.com/webstore/detail/test-ie/eldlkpeoddgbmpjlnpfblfpgodnojfjl/related Test IE] for {{Chrome}}
* [https://saucelabs.com/home Sauce Labs: Mobile and Web App Testing Tools For Developers]''($)'': [https://saucelabs.com/docs/platforms supported Device/OS/Browser Platforms]

testing different screen resolution
* [[CSS_tools#.E4.B8.8D.E5.90.8C.E8.9E.A2.E5.B9.95.E8.A7.A3.E6.9E.90.E5.BA.A6.E6.AA.A2.E8.A6.96.E7.B6.B2.E9.A0.81| css tools]]

===== mobile browsers =====
{{Tips}} WARNING: Simulators/emulators for mobile browser testing is '''different''' from the browser for real mobile device.
* [http://www.mozilla.org/en-US/mobile/ Mozilla Firefox | Firefox for Android | Download Firefox to your mobile device] {{access | date =2012-06-06}} {{win}} {{linux}}
* [http://www.opera.com/developer/tools/mini/ Opera Mini simulator]. Require: Java installed
* [https://developer.chrome.com/devtools/docs/device-mode Device Mode Mobile Emulation] for {{chrome}}
* [https://appetize.io/demo?device=iphone14pro&osVersion=16.2&record=true Demo] of appetize

alternative
* [http://bluestacks.com/ BlueStacks] Run Android on Windows with BlueStacks for {{win}} & {{Mac}}
* [http://www.genymotion.com/ Genymotion] + VirtualBox 教學: [http://www.azofreeware.com/2013/08/genymotion-110-bluestacksandroid.html (魔神模擬器) Genymotion 2.2.2 - 比BlueStacks還專業的Android安卓手機模擬器神魔之塔可以玩 - 阿榮福利味]

===== desktop browser =====
One PC with different Internet Explorer versions 同一電腦可以有不同版本的Internet Explorer

* [https://blogs.windows.com/msedgedev/2015/08/17/windows-10-virtual-machines-now-available-on-microsoft-edge-dev/#QZQ86LBwxcHOQOgo.97 Windows 10 virtual machines now available on Microsoft Edge Dev | Microsoft Edge Dev Blog] IE8, IE9, IE10, IE11, Microsoft Edge {{access | date = 2016-10-12}}
* [http://finalbuilds.edskes.net/iecollection.htm Internet Explorer Collection]([http://blog.miniasp.com/post/2009/03/Useful-tools-Internet-Explorer-Collection.aspx 介紹])
** various {{IE}} browser version: IE1.0 ~ IE8 which allow to choose to install '''IE developer toolbar''' & '''Firebug Web development extension''' during the installation
* [http://www.my-debugbar.com/wiki/IETester/HomePage IETester]
** various {{IE}} browser version: IE5.5 ~ IE11 emulator {{exclaim}} IE9+ emulator not available on Windows XP
* [http://browserling.com/ browserling - interactive cross-browser testing in your browser] Run IE5~9 (Simulated by [http://www.my-debugbar.com/wiki/IETester/HomePage IETester])
* Microsoft [http://expression.microsoft.com/en-us/dd565874.aspx Expression Web SuperPreview]([http://www.microsoft.com/downloads/details.aspx?FamilyID=8e6ac106-525d-45d0-84db-dccff3fae677&displaylang=en download]) IE6, IE7, IE8->7 (compatibility view), IE8, [http://support.microsoft.com/kb/2445851?Browser=Firefox&Version=5 Fx 3.0~3.6]
* Windows OS + IE v.10: go to menu: '''"developer tools"''' -> switch "browser mode" between: IE10, IE9, IE8, IE7

older version of IE also available at
* IE3.0 ~ IE6.0: [http://tredosoft.com/Multiple_IE Multiple IE]

other desktop browser available at
* [http://www.browseemall.com/ BrowseEmAll (Standalone tool to run all major Browsers)] (shareware)
* [http://spoon.net/browsers/ Browser Sandbox - Run any browser from the web](''$'') Run {{Fx}}, {{Chrome}}, {{Opera}}, {{Safari}} and mobile browsers from the plug-in, but no IE (last visited: 2011-02-09)
* [http://browserling.com/ browserling - interactive cross-browser testing in your browser] Run {{IE}} 5-9 (Simulated by [http://www.my-debugbar.com/wiki/IETester/HomePage IETester]), {{Fx}} v3~10, {{Chrome}} v1~16 , {{Opera}} v10~11 and {{Safari}} v4~5 (last visited: 2012-03-06) Guest can use 3 minute sessions
* portable version of browser such as portable firefox (Firefox 免安裝版)

alternative: Install different '''virtual machines''', ex:
* [http://www.virtualbox.org/ VirtualBox],
* [http://www.microsoft.com/windows/virtual-pc/support/virtual-pc-2007.aspx Windows Virtual PC] + [http://www.microsoft.com/en-us/download/details.aspx?id=11575 IE App Compat VHD from Official Microsoft] with different version of browser. For example you install IE v.6 on first virtual machine, and install IE v.7 on another virtual machine.
* [http://www.vmware.com/products/thinapp/ VMware ThinApp]''($)''
* ... more on [[Virtual machine images]]

See also
* [[Research_surveys#全球瀏覽器、作業系統、螢幕解析度、搜尋引擎市占率統計表 | 全球瀏覽器(Browser share)、作業系統、螢幕解析度、搜尋引擎市占率統計表]]
* [https://addons.mozilla.org/en-US/firefox/addon/59 User Agent Switcher :: Firefox Add-ons][http://chrispederick.com/work/user-agent-switcher/]for {{Fx}}

further reading
* [https://medium.com/@stothelios/9-tools-to-test-your-site-9ee997c79e48 9 tools to test your site — Medium]

<noinclude>{{Template:Build a website}}</noinclude>

[[Category:Design]] [[Category:Browser]] [[Category:Mobile]]

Day of week

2026-06-25T16:57:47Z

Planetoid: /* Weekday */

Numeric representation of the day of the week. MySQL, Excel or PHP approaches to specify these date time.

== Weekday ==
The value of weekday obtained from different approaches.

<div style="width:100%; min-height: .01%; overflow-x: auto;">
{| class="wikitable sortable nowrap"
|-
! style="text-align:right;" | Weekday values from different approaches
! Sunday
! Monday
! Tuesday
! Wednesday
! Thursday
! Friday
! Saturday
|-
! [http://php.net/manual/en/function.date.php PHP date] with N format character: Weekday ([https://en.wikipedia.org/wiki/ISO_week_date ISO-8601])
| 7
| 1
| 2
| 3
| 4
| 5
| 6
|-
! [https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_weekday MySQL WEEKDAY() function]
| 6
| 0
| 1
| 2
| 3
| 4
| 5
|-
! [https://www.w3schools.com/sql/func_mysql_dayofweek.asp MySQL DAYOFWEEK() Function]
| 1
| 2
| 3
| 4
| 5
| 6
| 7
|-
! [https://support.office.com/zh-tw/article/WEEKDAY-%E5%87%BD%E6%95%B8-60e44483-2ed1-439f-8bd0-e404c190949a Excel WEEKDAY() function] e.g. WEEKDAY(A2) or WEEKDAY(A2, 1)
| 1
| 2
| 3
| 4
| 5
| 6
| 7
|-
! [https://support.office.com/zh-tw/article/WEEKDAY-%E5%87%BD%E6%95%B8-60e44483-2ed1-439f-8bd0-e404c190949a Excel WEEKDAY() function] e.g. WEEKDAY(A2, 2)
| 7
| 1
| 2
| 3
| 4
| 5
| 6
|-
! [http://php.net/manual/en/function.date.php PHP date] with w format character
| 0
| 1
| 2
| 3
| 4
| 5
| 6
|-
|}
</div>

== Get the date ==
Get the date of weekday from another known date (today or another specific day)

<div style="width:100%; min-height: .01%; overflow-x: auto;">
{| class="wikitable sortable nowrap"
|-
! style="text-align:right;" | Day
! Sunday
! Monday
! Tuesday
! Wednesday
! Thursday
! Friday
! Saturday
|-

! Last week
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-8</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())-6</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Sunday -14 day")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-7</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())-5</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("monday this week -7 day")) . PHP_EOL;</nowiki>}}
|
|
|
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-3</span> - WEEKDAY(CURDATE()) DAY;}} <br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('Friday -7 day')) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-2</span> - WEEKDAY(CURDATE()) DAY;}} <br /> PHP<ref>[https://stackoverflow.com/questions/3742820/php-how-to-get-previous-sunday-of-a-specific-date-in-the-past PHP: How to get previous Sunday of a specific date in the past..? - Stack Overflow]</ref>: (1) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("last Saturday")) . PHP_EOL;</nowiki>}} (2) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('last Saturday', strtotime("2018-03-04 00:01:00"))) . PHP_EOL;</nowiki>}}
|-
! This week
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-1</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+1</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Sunday -7 day")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">0</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+2</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("monday this week")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">1</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+3</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">2</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+4</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">3</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+5</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">4</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+6</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('Friday')) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">5</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+7</nowiki>}} <br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Saturday")) . PHP_EOL;</nowiki>}}
|-
! Next week
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">6</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+8</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Sunday")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">7</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+9</nowiki>}}<br />PHP: (1) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("next monday")) . PHP_EOL;</nowiki>}} (2) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("monday this week +7 day")) . PHP_EOL;</nowiki>}}
|
|
|
| PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('Friday +7 day')) . PHP_EOL;</nowiki>}}
|
|-
|}
</div>

== References ==
<References />

== Further reading ==
* PHP [http://php.net/manual/en/function.date.php date] function: w character (0 for Sunday through 6 for Saturday) or N character (1 for Monday through 7 for Sunday)
* [https://support.office.com/zh-tw/article/WEEKDAY-%E5%87%BD%E6%95%B8-60e44483-2ed1-439f-8bd0-e404c190949a WEEKDAY 函數 - Office 支援]
* [https://en.wikipedia.org/wiki/ISO_8601 ISO 8601 - Wikipedia]
* [https://stackoverflow.com/questions/23020162/how-to-get-this-coming-sundays-date mysql - How to get this coming Sunday's date? - Stack Overflow]
* [https://support.office.com/zh-tw/article/today-%E5%87%BD%E6%95%B8-5eb3078d-a82c-4736-8930-2f51a028fdd9 TODAY 函數 - Office 支援]
* [https://errerrors.blogspot.com/2026/06/spreadsheet-weekday-formula.html 在 Google 試算表、Excel 與 LibreOffice Calc 如何顯示星期幾]

[[Category:PHP]]
[[Category:Programming]]
[[Category:Web_Dev]]
[[Category:MySQL]]
[[Category:Data Science]]

Day of week

2026-06-25T16:54:45Z

Planetoid: /* Further reading */

Numeric representation of the day of the week. MySQL, Excel or PHP approaches to specify these date time.

== Weekday ==
The value of weekday obtained from different approaches.

<div style="width:100%; min-height: .01%; overflow-x: auto;">
{| class="wikitable sortable nowrap"
|-
! style="text-align:right;" | Weekday values from different approaches
! Sunday
! Monday
! Tuesday
! Wednesday
! Thursday
! Friday
! Saturday
|-
! [http://php.net/manual/en/function.date.php PHP date] with N format character: Weekday ([https://en.wikipedia.org/wiki/ISO_week_date ISO-8601])
| 7
| 1
| 2
| 3
| 4
| 5
| 6
|-
! [https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_weekday MySQL WEEKDAY() function]
| 6
| 0
| 1
| 2
| 3
| 4
| 5
|-
! [https://www.w3schools.com/sql/func_mysql_dayofweek.asp MySQL DAYOFWEEK() Function]
| 1
| 2
| 3
| 4
| 5
| 6
| 7
|-
! [https://support.office.com/zh-tw/article/WEEKDAY-%E5%87%BD%E6%95%B8-60e44483-2ed1-439f-8bd0-e404c190949a Excel WEEKDAY() function]
| 1
| 2
| 3
| 4
| 5
| 6
| 7
|-
! [http://php.net/manual/en/function.date.php PHP date] with w format character
| 0
| 1
| 2
| 3
| 4
| 5
| 6
|-
|}
</div>

== Get the date ==
Get the date of weekday from another known date (today or another specific day)

<div style="width:100%; min-height: .01%; overflow-x: auto;">
{| class="wikitable sortable nowrap"
|-
! style="text-align:right;" | Day
! Sunday
! Monday
! Tuesday
! Wednesday
! Thursday
! Friday
! Saturday
|-

! Last week
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-8</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())-6</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Sunday -14 day")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-7</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())-5</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("monday this week -7 day")) . PHP_EOL;</nowiki>}}
|
|
|
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-3</span> - WEEKDAY(CURDATE()) DAY;}} <br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('Friday -7 day')) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-2</span> - WEEKDAY(CURDATE()) DAY;}} <br /> PHP<ref>[https://stackoverflow.com/questions/3742820/php-how-to-get-previous-sunday-of-a-specific-date-in-the-past PHP: How to get previous Sunday of a specific date in the past..? - Stack Overflow]</ref>: (1) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("last Saturday")) . PHP_EOL;</nowiki>}} (2) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('last Saturday', strtotime("2018-03-04 00:01:00"))) . PHP_EOL;</nowiki>}}
|-
! This week
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-1</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+1</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Sunday -7 day")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">0</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+2</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("monday this week")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">1</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+3</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">2</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+4</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">3</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+5</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">4</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+6</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('Friday')) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">5</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+7</nowiki>}} <br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Saturday")) . PHP_EOL;</nowiki>}}
|-
! Next week
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">6</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+8</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Sunday")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">7</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+9</nowiki>}}<br />PHP: (1) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("next monday")) . PHP_EOL;</nowiki>}} (2) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("monday this week +7 day")) . PHP_EOL;</nowiki>}}
|
|
|
| PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('Friday +7 day')) . PHP_EOL;</nowiki>}}
|
|-
|}
</div>

== References ==
<References />

== Further reading ==
* PHP [http://php.net/manual/en/function.date.php date] function: w character (0 for Sunday through 6 for Saturday) or N character (1 for Monday through 7 for Sunday)
* [https://support.office.com/zh-tw/article/WEEKDAY-%E5%87%BD%E6%95%B8-60e44483-2ed1-439f-8bd0-e404c190949a WEEKDAY 函數 - Office 支援]
* [https://en.wikipedia.org/wiki/ISO_8601 ISO 8601 - Wikipedia]
* [https://stackoverflow.com/questions/23020162/how-to-get-this-coming-sundays-date mysql - How to get this coming Sunday's date? - Stack Overflow]
* [https://support.office.com/zh-tw/article/today-%E5%87%BD%E6%95%B8-5eb3078d-a82c-4736-8930-2f51a028fdd9 TODAY 函數 - Office 支援]
* [https://errerrors.blogspot.com/2026/06/spreadsheet-weekday-formula.html 在 Google 試算表、Excel 與 LibreOffice Calc 如何顯示星期幾]

[[Category:PHP]]
[[Category:Programming]]
[[Category:Web_Dev]]
[[Category:MySQL]]
[[Category:Data Science]]

Check Browser Compatibility

2026-06-24T09:12:02Z

Planetoid: /* view the snapshot only */

Check Browser Compatibility

Noun explanation
# allowed to interact: the solutions which (1) allowed to interact the web pages e.g. click the link and test javascript (2) view the web snapshot
# view the snapshot only: only allowed to view the static web snapshot

==== Checking the compatibility ====
* {{Gd}} [http://www.caniuse.com/ Can I use... Support tables for HTML5, CSS3, etc] "Compatibility tables for support of HTML5, CSS3, SVG and more in desktop and mobile browsers." {{access | date=2020-08-23}}
* {{Gd}} [https://www.caniemail.com/ Can I email… Support tables for HTML and CSS in emails]
* [http://blog.miniasp.com/post/2013/07/14/HTML5-CSS3-JavaScript-JS-compatibility-resouces.aspx The Will Will Web | 前端工程研究：HTML/CSS/JavaScript 相容性檢查資源整理]

==== allowed to interact ====
===== desktop browser + mobile browser =====
cloud services for testing desktop browsers and mobile browsers
* [http://www.browserstack.com/ BrowserStack]''($)'' "Cross Browser Testing Tool. 100+ Browsers, Mobile, Real IE." [http://www.browserstack.com/list-of-browsers-and-platforms List of Browsers and Platforms for Cross Browser Testing]
** [https://chrome.google.com/webstore/detail/test-ie/eldlkpeoddgbmpjlnpfblfpgodnojfjl/related Test IE] for {{Chrome}}
* [https://saucelabs.com/home Sauce Labs: Mobile and Web App Testing Tools For Developers]''($)'': [https://saucelabs.com/docs/platforms supported Device/OS/Browser Platforms]

testing different screen resolution
* [[CSS_tools#.E4.B8.8D.E5.90.8C.E8.9E.A2.E5.B9.95.E8.A7.A3.E6.9E.90.E5.BA.A6.E6.AA.A2.E8.A6.96.E7.B6.B2.E9.A0.81| css tools]]

===== mobile browsers =====
{{Tips}} WARNING: Simulators/emulators for mobile browser testing is '''different''' from the browser for real mobile device.
* [http://www.mozilla.org/en-US/mobile/ Mozilla Firefox | Firefox for Android | Download Firefox to your mobile device] {{access | date =2012-06-06}} {{win}} {{linux}}
* [http://www.opera.com/developer/tools/mini/ Opera Mini simulator]. Require: Java installed
* [https://developer.chrome.com/devtools/docs/device-mode Device Mode Mobile Emulation] for {{chrome}}
* [https://appetize.io/demo?device=iphone14pro&osVersion=16.2&record=true Demo] of appetize

alternative
* [http://bluestacks.com/ BlueStacks] Run Android on Windows with BlueStacks for {{win}} & {{Mac}}
* [http://www.genymotion.com/ Genymotion] + VirtualBox 教學: [http://www.azofreeware.com/2013/08/genymotion-110-bluestacksandroid.html (魔神模擬器) Genymotion 2.2.2 - 比BlueStacks還專業的Android安卓手機模擬器神魔之塔可以玩 - 阿榮福利味]

===== desktop browser =====
One PC with different Internet Explorer versions 同一電腦可以有不同版本的Internet Explorer

* [https://blogs.windows.com/msedgedev/2015/08/17/windows-10-virtual-machines-now-available-on-microsoft-edge-dev/#QZQ86LBwxcHOQOgo.97 Windows 10 virtual machines now available on Microsoft Edge Dev | Microsoft Edge Dev Blog] IE8, IE9, IE10, IE11, Microsoft Edge {{access | date = 2016-10-12}}
* [http://finalbuilds.edskes.net/iecollection.htm Internet Explorer Collection]([http://blog.miniasp.com/post/2009/03/Useful-tools-Internet-Explorer-Collection.aspx 介紹])
** various {{IE}} browser version: IE1.0 ~ IE8 which allow to choose to install '''IE developer toolbar''' & '''Firebug Web development extension''' during the installation
* [http://www.my-debugbar.com/wiki/IETester/HomePage IETester]
** various {{IE}} browser version: IE5.5 ~ IE11 emulator {{exclaim}} IE9+ emulator not available on Windows XP
* [http://browserling.com/ browserling - interactive cross-browser testing in your browser] Run IE5~9 (Simulated by [http://www.my-debugbar.com/wiki/IETester/HomePage IETester])
* Microsoft [http://expression.microsoft.com/en-us/dd565874.aspx Expression Web SuperPreview]([http://www.microsoft.com/downloads/details.aspx?FamilyID=8e6ac106-525d-45d0-84db-dccff3fae677&displaylang=en download]) IE6, IE7, IE8->7 (compatibility view), IE8, [http://support.microsoft.com/kb/2445851?Browser=Firefox&Version=5 Fx 3.0~3.6]
* Windows OS + IE v.10: go to menu: '''"developer tools"''' -> switch "browser mode" between: IE10, IE9, IE8, IE7

older version of IE also available at
* IE3.0 ~ IE6.0: [http://tredosoft.com/Multiple_IE Multiple IE]

other desktop browser available at
* [http://www.browseemall.com/ BrowseEmAll (Standalone tool to run all major Browsers)] (shareware)
* [http://spoon.net/browsers/ Browser Sandbox - Run any browser from the web](''$'') Run {{Fx}}, {{Chrome}}, {{Opera}}, {{Safari}} and mobile browsers from the plug-in, but no IE (last visited: 2011-02-09)
* [http://browserling.com/ browserling - interactive cross-browser testing in your browser] Run {{IE}} 5-9 (Simulated by [http://www.my-debugbar.com/wiki/IETester/HomePage IETester]), {{Fx}} v3~10, {{Chrome}} v1~16 , {{Opera}} v10~11 and {{Safari}} v4~5 (last visited: 2012-03-06) Guest can use 3 minute sessions
* portable version of browser such as portable firefox (Firefox 免安裝版)

alternative: Install different '''virtual machines''', ex:
* [http://www.virtualbox.org/ VirtualBox],
* [http://www.microsoft.com/windows/virtual-pc/support/virtual-pc-2007.aspx Windows Virtual PC] + [http://www.microsoft.com/en-us/download/details.aspx?id=11575 IE App Compat VHD from Official Microsoft] with different version of browser. For example you install IE v.6 on first virtual machine, and install IE v.7 on another virtual machine.
* [http://www.vmware.com/products/thinapp/ VMware ThinApp]''($)''
* ... more on [[Virtual machine images]]

See also
* [[Research_surveys#全球瀏覽器、作業系統、螢幕解析度、搜尋引擎市占率統計表 | 全球瀏覽器(Browser share)、作業系統、螢幕解析度、搜尋引擎市占率統計表]]
* [https://addons.mozilla.org/en-US/firefox/addon/59 User Agent Switcher :: Firefox Add-ons][http://chrispederick.com/work/user-agent-switcher/]for {{Fx}}

further reading
* [https://medium.com/@stothelios/9-tools-to-test-your-site-9ee997c79e48 9 tools to test your site — Medium]

<noinclude>{{Template:Build a website}}</noinclude>

[[Category:Design]] [[Category:Browser]] [[Category:Mobile]]

User agent

2026-06-24T09:10:46Z

Planetoid: /* Mail user clients / Mail user agents */

使用者代理程式(user agent): Search Engine Spiders (Bots), Browsers, Code Validators

== Search Engine Spiders (Bots) ==

{| border="1"
| User Agent || Search Engine
|-
| Scooter || [http://www.altavista.com/web/legal/termsofuse AltaVista]
|-
| [http://www.wisenut.com/ WISENutbot] || Looksmart
|-
| IA Archiver || [http://pages.alexa.com/help/webmasters/ Alexa]
|-
| [http://search.msn.com/msnbot.htm MSNBot] || MSN
|-
| Slurp || [http://www.inktomi.com/slurp.html Inktomi]
|-
| Fast-Webcrawler || [http://www.fastsearch.com/us/products/fast_web_search/crawler_faq AllTheWeb]
|-
| [http://www.walhello.com/aboutgl.html Walhello appie] || -
|-
| Googlebot || ([http://www.google.com/bot.html Google] or [http://www.yam.com/help/doc_sre.html 中文說明])
|-
| [http://gais.cs.ccu.edu.tw/robot.php Gaisbot] || -
|-
|}

* Robotstxt.org: [http://www.robotstxt.org/wc/active.html The Web Robots Database]
* WebmasterWorld Forms: [http://www.webmasterworld.com/category1.htm The Search Engine World]
* [http://www.hkwebs.net/catalog/teach/setting/robots.php 設定 robots.txt]

{{News feed | title = 搜尋引擎相關新聞聯播 | feed = <rss>https://news.google.com/rss/search?q=%E6%90%9C%E5%B0%8B%E5%BC%95%E6%93%8E&hl=zh-TW&gl=TW&ceid=TW:zh-Hant</rss>
}}

<div style="clear:both"></div>

== Web Browsers ==

=== list of web browsers ===
* https://planetoid.info/images/browser_msie.png [http://www.microsoft.com/windows/ie/default.asp MS Internet Explorer]
* https://planetoid.info/images/browser_firefox.png [http://www.mozilla.org/ Mozilla]及瘦身版 Mozilla Firefox [http://moztw.org/ 中文化]([http://portableapps.com/apps/internet/firefox_portable Portable Edition]),
* https://planetoid.info/images/browser_chrome.png [http://www.google.com/chrome Google chrome]([http://portableapps.com/apps/internet/google_chrome_portable Portable Edition], [http://briian.com/?p=5754 「鍵盤快速鍵」與「快捷操作技巧」])
** [http://briian.com/?p=6043 Google Chrome Channel Changer 2.0 快速取得最新測試版瀏覽器　　【重灌狂人】]
** [http://tools.google.com/dlpage/chromesxs Google Chrome Canary Build]
** 開放原始碼專案: [http://code.google.com/chromium/ Chromium] ([http://blog.chromium.org/ Chromium Blog], [http://dev.chromium.org/getting-involved/dev-channel/ Dev Channel], [http://build.chromium.org/buildbot/snapshots/chromium-rel-xp/?O=D Download]) via [http://www.freegroup.org/free-web-browser-chromium]
*** 下載說明: [http://www.freegroup.org/free-web-browser-chromium Chromium - Google Chrome 免安裝版！ - 免費資源網路社群]
*** 非官方的版本: [http://soft.onlyone.idv.tw/article.asp?id=467 Google Chrome 有免安裝 + 滑鼠手勢版囉！ - 硬是要學！網路資源學習站]
* https://planetoid.info/images/browser_opera.png [http://www.opera.com/ Opera]([http://www.opera-usb.com/operausben.htm Portable Edition]), [http://jedi.org/p4/Opera/pub/ Opera 台灣版──三太子（Nalakuvara）]
* https://planetoid.info/images/browser_safari.png [http://www.apple.com/safari/ Safari]([http://azo-freeware.blogspot.com/2009/02/safari-452816-beta.html Safari 5.1.7 免安裝中文版])
* [http://www.w3.org/Library/ LibWWW],
* [http://www.konqueror.org/ Konqueror], and 文字界面的瀏覽軟體: [http://lynx.isc.org/release/ Lynx]
* more on [http://en.wikipedia.org/wiki/List_of_web_browsers List of web browsers]([http://zh.wikipedia.org/wiki/%E7%B6%B2%E9%A0%81%E7%80%8F%E8%A6%BD%E5%99%A8%E5%88%97%E8%A1%A8 網頁瀏覽器列表])

{{News feed | title = 瀏覽器相關新聞聯播 | feed = <rss>https://news.google.com/rss/search?q=%E7%80%8F%E8%A6%BD%E5%99%A8&hl=zh-TW&gl=TW&ceid=TW:zh-Hant</rss>
}}

=== Chcek Browser Compatibility ===
{{:Check Browser Compatibility}}

=== Browser detect ===
* {{Gd}} [http://www.useragentstring.com/ User Agent String.Com ]
* [http://www.quirksmode.org/js/detect.html JavaScript - Browser detect]
* [http://php.net/manual/en/function.get-browser.php PHP get_browser function]
* [http://whatsmyuseragent.com/ Whats My User Agent?]

=== Display HTTP headers of a web page ===
[[HTTP request and response data tool]]

== Mail user clients / Mail user agents ==
* {{Gd}} [https://www.caniemail.com/ Can I email… Support tables for HTML and CSS in emails] {{access | date=2026-04-24}} e.g. [https://www.caniemail.com/features/image-webp/ Can I email… webP image format]

== Grabber ==
* [http://curl.haxx.se/ Curl] "curl is a command line tool for transferring files with URL syntax, supporting FTP, FTPS, HTTP, HTTPS, SCP, SFTP, TFTP, TELNET, DICT, LDAP, LDAPS and FILE." Cited from official website.
* wget

== Code Validators (驗證工具) ==

{{Validator}}

== Serverside RSS Agregator ==
[http://www.bloglines.com Bloglines], [http://www.pubsub.com/ PubSub.com RSS reader], [http://www.breakingblogs.com/timbo_bot.html timboBot], [http://magpierss.sf.net MagpieRSS],

----
Some data came from http://www.siteware.ch/webresources/useragents/
* [http://blog.miniasp.com/post/2009/12/Useful-website-UserAgentString.aspx 介紹好用網站：UserAgentString.com]

== Further readings ==
* [https://developer.chrome.com/blog/user-agent-reduction-android-model-and-version/ Prepare for Chrome's user‑agent reduction - Chrome Developers]

[[Category: Programming]]

Xpath tools

2026-06-23T05:55:40Z

Planetoid: /* References */

List of tools for xpath expression selection/query

== Comparison of xpath tools ==

<table border=1 class="wikitable sortable">
<tr>
<th>Method</th>
<th>Pros</th>
<th>Cons</th>
</tr>
<tr>
<td>chrome extension [https://chrome.google.com/webstore/detail/xpath-helper/hgimnogjllphhhkhlmebbmlgjoejdpjl XPath Helper]</td>
<td>worked on online webpage</td>
<td>NOT work on offline file</td>
</tr>
<tr>
<td>Sublime [https://packagecontrol.io/packages/xpath xpath] plugin</td>
<td>worked on offline file</td>
<td>NOT work on online webpage</td>
</tr>
</table>

== Browser Console ==

=== List matched elements by XPath rule ===

Open the browser console in {{chrome}} or {{Edge}}:

# Menu: Tools → Developer Tools → Console
# To verify elements matching an XPath rule, type the expression using $x(). For example, to find all images, enter {{kbd | key = <nowiki>$x("//img")</nowiki>}} and press Enter. The matched elements will be listed in the console<ref>[http://stackoverflow.com/questions/3030487/is-there-a-way-to-get-the-xpath-in-google-chrome Is there a way to get the xpath in google chrome? - Stack Overflow]</ref><ref>[https://yizeng.me/2014/03/23/evaluate-and-validate-xpath-css-selectors-in-chrome-developer-tools/ Evaluate and validate XPath/CSS selectors in Chrome Developer Tools | Yi Zeng's Blog]</ref>:

https://planetoid.info/images/xpath_result_on_browser_console.jpg

To extract and display the text content of matched elements — cleaned and separated by newlines — use the snippet below. The example uses XPath rule {{kbd | key = <nowiki>//div[@id="showvender"]/a</nowiki>}}:
<pre>
// Use document.evaluate to retrieve all matching elements
const result = document.evaluate('//div[@id="showvender"]/a', document, null, XPathResult.ORDERED_NODE_SNAPSHOT_TYPE, null);

// Collect text content from each element
const textArray = [];
for (let i = 0; i < result.snapshotLength; i++) {
const element = result.snapshotItem(i);
// Strip newlines and trim whitespace
const cleanedText = element.textContent.replace(/[\n\r]+/g, '').trim();
textArray.push(cleanedText);
}

// Print all results, one per line
console.log(textArray.join('\n'));
</pre>

=== List matched elements by CSS selector ===

To quickly list all elements matching a CSS selector, use {{kbd | key = <nowiki>$$("css-selector")</nowiki>}} in the console. For example, to find all images, enter {{kbd | key = <nowiki>$$("img")</nowiki>}} and press Enter. The matched elements will be listed in the console.

To extract and display the text content of matched elements — cleaned and separated by newlines — use the snippet below. The example uses CSS selector {{kbd | key = <nowiki>div#showvender a</nowiki>}}:
<pre>
// Use querySelectorAll to retrieve all matching elements
const elements = document.querySelectorAll('div#showvender a');

// Collect text content from each element
const textArray = [];
elements.forEach(el => {
// Strip newlines and trim whitespace
const cleanedText = el.textContent.replace(/[\n\r]+/g, '').trim();
textArray.push(cleanedText);
});

// Print all results, one per line
console.log(textArray.join('\n'));
</pre>

=== Highlight matched elements by XPath rule ===

By XPath rule:
<pre>
const xpath = '//main[contains(@class, "htmlview") and @data-cat="article"]';

// Helper: resolve XPath to an array of elements
function $x(xpath) {
const result = document.evaluate(xpath, document, null, XPathResult.ORDERED_NODE_SNAPSHOT_TYPE, null);
return Array.from({ length: result.snapshotLength }, (_, i) => result.snapshotItem(i));
}

// Scroll to the first matched element
$x(xpath)[0]?.scrollIntoView();

// Highlight all matched elements with a red outline
$x(xpath).forEach(el => {
el.style.outline = '3px solid red';
});
</pre>

=== Highlight matched elements by CSS selector ===

By CSS selector:
<pre>
const cssSelector = "main.htmlview[data-cat='article']";

// Scroll to the first matched element
document.querySelector(cssSelector)?.scrollIntoView();

// Highlight all matched elements with a red outline
document.querySelectorAll(cssSelector).forEach(el => {
el.style.outline = '3px solid red';
});
</pre>

== XPath Browser extensions ==
[https://chrome.google.com/webstore/detail/xpath-helper/hgimnogjllphhhkhlmebbmlgjoejdpjl XPath Helper] for {{Chrome}}

# 不支援 {{kbd | key=<nowiki>file:///</nowiki>}}
# 輸入 ctrl + shift + x 或點選工具列上的 x 圖示，開啟 XPath Helper console (顯示在網頁上方)
# 輸入 xpath 規則。就會凸顯符合 xpath 規則的網頁元件地方
: https://planetoid.info/images/xpath_helper.jpg

[https://chrome.google.com/webstore/detail/selectorshub/ndgimibanhlabgdgjcpbbndiehljcpfh SelectorsHub] for {{Chrome}}

# After adding it to browser, restart the browser. 安裝後，重新啟動瀏覽器
# Open DevTools. 打開「開發人員工具」
# Click on SelectorsHub tab. 點選「元素」標籤，再點選最右邊的「SelectorsHub」
# Here it will show all possible xpath, selectors for inspected element.
# To build your own xpath or CSS Selector, start typing in the selector input box.

[https://chrome.google.com/webstore/detail/natu-websync/aohpgnblncapofbobbilnlfliihianac/related Natu WebSync - Chrome 線上應用程式商店] for {{Chrome}}

# 開啟「開發人員工具」
# 點選標籤「WebSync」-> 輸入 xpath 規則

[https://chrome.google.com/webstore/detail/css-selector/dobcgekgcmhjmfahepgbpmiaejlpaalc CSS Selector][https://css-selector-gadget.dllplayer.com/welcome] for {{Chrome}}

* 直接點選網頁元素，則顯示 CSS Selector。點選 xpath 按鈕則顯示 xpath 路徑。

[https://addons.mozilla.org/zh-TW/firefox/addon/firebug/ Firebug] + [https://addons.mozilla.org/zh-TW/firefox/addon/firepath/ FirePath] for {{Fx}}
# 選單: 工具 --> 網頁開發者 --> Firebug --> 開啟 Firebug
# 切換到 FirePath 標籤
# 輸入 xpath 規則。就會凸顯符合 xpath 規則的網頁元件地方

== Sublime text editor ==
[https://packagecontrol.io/packages/xpath xpath] plugin: "[https://www.sublimetext.com/ Sublime Text] plugin for easier cursor navigation of XML and HTML files using XPath 1.0."

Steps
# Open the Command Palette
#* Menu: Tools -> Command Palette
#* or press the keyboard shortcut<ref>[https://stackoverflow.com/questions/45586508/sublime-text-xpath-plugin-shortcut sublimetext3 - Sublime Text - XPath Plugin shortcut - Stack Overflow]</ref>: {{kbd | key=ctrl + shift + p}} on {{Win}} or {{kbd | key=command + shift + p}} on {{Mac}})
# and select {{kbd | key=Xpath: Query document}}
# input the Xpath expression

== Other tools ==
* [https://xpath.curiousconcept.com/ XPath Expression Tester] for XML {{access | date=2020-06-09}}
* [https://www.freeformatter.com/xpath-tester.html Free Online XPath Tester / Evaluator] for XML {{access | date=2020-06-09}}
* [http://xpather.com/ XPath online real-time tester, evaluator and generator for XML & HTML] {{access | date=2023-03-22}}

== Xpath syntax examples ==
* SVG {{kbd | key=<nowiki>//*[local-name() = 'svg']</nowiki>}}
* SVG elements {{kbd | key=<nowiki>//*[name()='svg']//*[name()='YOUR OBJECT']</nowiki>}} <ref>[https://stackoverflow.com/questions/31520642/how-to-use-xpath-in-selenium-webdriver-to-grab-svg-elements java - How to use xPath in Selenium WebDriver to grab SVG elements? - Stack Overflow]</ref>

== References ==
* References

<references/>

Related articles

* [https://devhints.io/xpath Xpath cheatsheet]

{{Template:Troubleshooting}}

[[Category: Programming]]
[[Category: Web_Dev]]
[[Category: Javascript]]
[[Category: Data collecting]]
[[Category: Revised with LLMs]]

Xpath tools

2026-06-23T05:54:33Z

Planetoid: /* Browser console */

List of tools for xpath expression selection/query

== Comparison of xpath tools ==

<table border=1 class="wikitable sortable">
<tr>
<th>Method</th>
<th>Pros</th>
<th>Cons</th>
</tr>
<tr>
<td>chrome extension [https://chrome.google.com/webstore/detail/xpath-helper/hgimnogjllphhhkhlmebbmlgjoejdpjl XPath Helper]</td>
<td>worked on online webpage</td>
<td>NOT work on offline file</td>
</tr>
<tr>
<td>Sublime [https://packagecontrol.io/packages/xpath xpath] plugin</td>
<td>worked on offline file</td>
<td>NOT work on online webpage</td>
</tr>
</table>

== Browser Console ==

=== List matched elements by XPath rule ===

Open the browser console in {{chrome}} or {{Edge}}:

# Menu: Tools → Developer Tools → Console
# To verify elements matching an XPath rule, type the expression using $x(). For example, to find all images, enter {{kbd | key = <nowiki>$x("//img")</nowiki>}} and press Enter. The matched elements will be listed in the console<ref>[http://stackoverflow.com/questions/3030487/is-there-a-way-to-get-the-xpath-in-google-chrome Is there a way to get the xpath in google chrome? - Stack Overflow]</ref><ref>[https://yizeng.me/2014/03/23/evaluate-and-validate-xpath-css-selectors-in-chrome-developer-tools/ Evaluate and validate XPath/CSS selectors in Chrome Developer Tools | Yi Zeng's Blog]</ref>:

https://planetoid.info/images/xpath_result_on_browser_console.jpg

To extract and display the text content of matched elements — cleaned and separated by newlines — use the snippet below. The example uses XPath rule {{kbd | key = <nowiki>//div[@id="showvender"]/a</nowiki>}}:
<pre>
// Use document.evaluate to retrieve all matching elements
const result = document.evaluate('//div[@id="showvender"]/a', document, null, XPathResult.ORDERED_NODE_SNAPSHOT_TYPE, null);

// Collect text content from each element
const textArray = [];
for (let i = 0; i < result.snapshotLength; i++) {
const element = result.snapshotItem(i);
// Strip newlines and trim whitespace
const cleanedText = element.textContent.replace(/[\n\r]+/g, '').trim();
textArray.push(cleanedText);
}

// Print all results, one per line
console.log(textArray.join('\n'));
</pre>

=== List matched elements by CSS selector ===

To quickly list all elements matching a CSS selector, use {{kbd | key = <nowiki>$$("css-selector")</nowiki>}} in the console. For example, to find all images, enter {{kbd | key = <nowiki>$$("img")</nowiki>}} and press Enter. The matched elements will be listed in the console.

To extract and display the text content of matched elements — cleaned and separated by newlines — use the snippet below. The example uses CSS selector {{kbd | key = <nowiki>div#showvender a</nowiki>}}:
<pre>
// Use querySelectorAll to retrieve all matching elements
const elements = document.querySelectorAll('div#showvender a');

// Collect text content from each element
const textArray = [];
elements.forEach(el => {
// Strip newlines and trim whitespace
const cleanedText = el.textContent.replace(/[\n\r]+/g, '').trim();
textArray.push(cleanedText);
});

// Print all results, one per line
console.log(textArray.join('\n'));
</pre>

=== Highlight matched elements by XPath rule ===

By XPath rule:
<pre>
const xpath = '//main[contains(@class, "htmlview") and @data-cat="article"]';

// Helper: resolve XPath to an array of elements
function $x(xpath) {
const result = document.evaluate(xpath, document, null, XPathResult.ORDERED_NODE_SNAPSHOT_TYPE, null);
return Array.from({ length: result.snapshotLength }, (_, i) => result.snapshotItem(i));
}

// Scroll to the first matched element
$x(xpath)[0]?.scrollIntoView();

// Highlight all matched elements with a red outline
$x(xpath).forEach(el => {
el.style.outline = '3px solid red';
});
</pre>

=== Highlight matched elements by CSS selector ===

By CSS selector:
<pre>
const cssSelector = "main.htmlview[data-cat='article']";

// Scroll to the first matched element
document.querySelector(cssSelector)?.scrollIntoView();

// Highlight all matched elements with a red outline
document.querySelectorAll(cssSelector).forEach(el => {
el.style.outline = '3px solid red';
});
</pre>

== XPath Browser extensions ==
[https://chrome.google.com/webstore/detail/xpath-helper/hgimnogjllphhhkhlmebbmlgjoejdpjl XPath Helper] for {{Chrome}}

# 不支援 {{kbd | key=<nowiki>file:///</nowiki>}}
# 輸入 ctrl + shift + x 或點選工具列上的 x 圖示，開啟 XPath Helper console (顯示在網頁上方)
# 輸入 xpath 規則。就會凸顯符合 xpath 規則的網頁元件地方
: https://planetoid.info/images/xpath_helper.jpg

[https://chrome.google.com/webstore/detail/selectorshub/ndgimibanhlabgdgjcpbbndiehljcpfh SelectorsHub] for {{Chrome}}

# After adding it to browser, restart the browser. 安裝後，重新啟動瀏覽器
# Open DevTools. 打開「開發人員工具」
# Click on SelectorsHub tab. 點選「元素」標籤，再點選最右邊的「SelectorsHub」
# Here it will show all possible xpath, selectors for inspected element.
# To build your own xpath or CSS Selector, start typing in the selector input box.

[https://chrome.google.com/webstore/detail/natu-websync/aohpgnblncapofbobbilnlfliihianac/related Natu WebSync - Chrome 線上應用程式商店] for {{Chrome}}

# 開啟「開發人員工具」
# 點選標籤「WebSync」-> 輸入 xpath 規則

[https://chrome.google.com/webstore/detail/css-selector/dobcgekgcmhjmfahepgbpmiaejlpaalc CSS Selector][https://css-selector-gadget.dllplayer.com/welcome] for {{Chrome}}

* 直接點選網頁元素，則顯示 CSS Selector。點選 xpath 按鈕則顯示 xpath 路徑。

[https://addons.mozilla.org/zh-TW/firefox/addon/firebug/ Firebug] + [https://addons.mozilla.org/zh-TW/firefox/addon/firepath/ FirePath] for {{Fx}}
# 選單: 工具 --> 網頁開發者 --> Firebug --> 開啟 Firebug
# 切換到 FirePath 標籤
# 輸入 xpath 規則。就會凸顯符合 xpath 規則的網頁元件地方

== Sublime text editor ==
[https://packagecontrol.io/packages/xpath xpath] plugin: "[https://www.sublimetext.com/ Sublime Text] plugin for easier cursor navigation of XML and HTML files using XPath 1.0."

Steps
# Open the Command Palette
#* Menu: Tools -> Command Palette
#* or press the keyboard shortcut<ref>[https://stackoverflow.com/questions/45586508/sublime-text-xpath-plugin-shortcut sublimetext3 - Sublime Text - XPath Plugin shortcut - Stack Overflow]</ref>: {{kbd | key=ctrl + shift + p}} on {{Win}} or {{kbd | key=command + shift + p}} on {{Mac}})
# and select {{kbd | key=Xpath: Query document}}
# input the Xpath expression

== Other tools ==
* [https://xpath.curiousconcept.com/ XPath Expression Tester] for XML {{access | date=2020-06-09}}
* [https://www.freeformatter.com/xpath-tester.html Free Online XPath Tester / Evaluator] for XML {{access | date=2020-06-09}}
* [http://xpather.com/ XPath online real-time tester, evaluator and generator for XML & HTML] {{access | date=2023-03-22}}

== Xpath syntax examples ==
* SVG {{kbd | key=<nowiki>//*[local-name() = 'svg']</nowiki>}}
* SVG elements {{kbd | key=<nowiki>//*[name()='svg']//*[name()='YOUR OBJECT']</nowiki>}} <ref>[https://stackoverflow.com/questions/31520642/how-to-use-xpath-in-selenium-webdriver-to-grab-svg-elements java - How to use xPath in Selenium WebDriver to grab SVG elements? - Stack Overflow]</ref>

== References ==
* References

<references/>

Related articles

* [https://devhints.io/xpath Xpath cheatsheet]

{{Template:Troubleshooting}}

[[Category:Programming]]
[[Category:Web_Dev]]
[[Category:Javascript]]
[[Category:Data collecting]]

Markdown editors

2026-06-22T06:49:46Z

Planetoid: /* Free softwares */

Comparison of markdown editors: (1) Supported Operated System, (2) Price, (3) Table of content (TOC): Improve the navigation through a long-form content, (4) License, (5) Export format (6) Draw diagram which support [https://mermaid.js.org/intro/ mermaid syntax]

__TOC__

== Free softwares ==
[https://macdown.uranusjr.com/ MacDown: The open source Markdown editor for macOS] v. 0.7.2.x
* Supported {{OS}}: Mac
* Price: free
* Table of content: not available {{exclaim}}
* Software license: MIT license & open source[https://github.com/MacDownApp/macdown] {{Gd}}
* Custom Font:
* Export: HTML & PDF
* Syndication:
* Draw diagram:

[https://coteditor.com/ CotEditor -Text Editor for macOS]
* Supported {{OS}}: Mac
* Price: free
* Table of content: available on ''Outline menu'' {{Gd}}
* Software license: Apache License, Version 2.0 & open source on [https://github.com/coteditor/CotEditor github] {{Gd}}
* Custom Font:
* Export: not available
* Syndication: not available
* Draw diagram:

[http://www.bear-writer.com/ Bear - Notes for iPhone, iPad and Mac] v. 1.1.2
* Supported {{OS}}: Mac
* Price: free & paid for advanced features [http://www.bear-writer.com/#pricing]
* Table of content: not available {{exclaim}}
* Software license: Proprietary
* Custom Font:
* Export: RTF for free version. PDF, HTML, DOCS, JPG for paid version. Reference: [http://www.bear-writer.com/faq/Export%20your%20notes/ Export your notes]
* Syndication: available for paid version: [http://www.bear-writer.com/faq/Sync/Syncing%20your%20notes%20with%20Bear%20Pro/ Syncing your notes with Bear Pro - Bear - Faq and support].
* Draw diagram:

[https://atom.io/ Atom]
* Supported {{OS}}: Win & Mac
* Price: free
* Table of content: available on [https://atom.io/packages/markdown-toc markdown-toc]
* Software license: [https://atom.io/faq MIT license & open source][https://github.com/atom/atom] {{Gd}}
* Custom Font: Available<ref>[https://atom.io/packages/fonts fonts]</ref>
* Export:
* Syndication: n/a
* Draw diagram:
* Notes: Press ctrl+shift+m to toggle the preview panel

[http://brackets.io/ Brackets]
* Supported {{OS}}: Win & Mac
* Price: free
* Table of content: available on plugin [https://github.com/naokazuterada/MarkdownTOC GitHub - naokazuterada/MarkdownTOC: MarkdownTOC(Table Of Contents) Plugin for Sublime Text]
* Software license: [https://github.com/adobe/brackets/blob/master/LICENSE MIT License]
* Custom Font:
* Export:
* Syndication: n/a
* Draw diagram:

[https://code.visualstudio.com/ Visual Studio Code - Code Editing. Redefined] v. 1.22.2
* Supported {{OS}}: Win, Mac & Linux
* Price: free
* Table of content: Available on extension [https://code.visualstudio.com/docs/languages/markdown Markdown editing with Visual Studio Code]. After installed the extension --> Open the markdown file --> Right menu: '''Markdown TOC: Insert/Update'''
* Software license: "The source code is available under the [https://github.com/Microsoft/vscode/blob/master/LICENSE.txt MIT license agreement]." Details on [https://code.visualstudio.com/License License - Visual Studio Code].
* Custom Font: Available
* Export: n/a
* Syndication: n/a
* Draw diagram:

[https://subethaedit.net/ SubEthaEdit 5. Code, Write, Edit. Together.]
* Supported {{OS}}: Mac
* Price: free
* Table of content: not available {{exclaim}}
* Software license: [https://github.com/subethaedit/SubEthaEdit/blob/develop/LICENSE.txt MIT License (MIT)]. Source code available on [https://github.com/subethaedit/SubEthaEdit github]
* Custom Font: Available
* Export: HTML
* Syndication:
* Draw diagram:

[https://github.com/tamlok/vnote tamlok/vnote] v. 2.2
* Supported {{OS}}: Windows, Mac & Linux
* Price: free
* Table of content: Available on Menu > View > Tools: Outline
* Software license: [https://github.com/tamlok/vnote/blob/master/LICENSE MIT License]. Source code available on [https://github.com/tamlok/vnote github]
* Custom Font: CSS
* Export: PDF, HTML & Markdown
* Syndication:
* Draw diagram:

[https://marktext.app/ Mark Text] v0.16.3
* Supported {{OS}}: Windows, Mac & Linux
* Price: free
* Table of content: not available {{exclaim}}
* Software license: [https://github.com/marktext/marktext/blob/develop/LICENSE MIT License]. Source code available on [https://github.com/marktext/marktext github]
* Custom Font: Available on Menu > Preferences > Editor
* Export: HTML & PDF
* Syndication:
* Draw diagram:

[https://github.com/KDE/ghostwriter/releases/tag/2.1.6 ghostwriter] v.2.1.1
* Supported {{OS}}: Windows, Mac & Linux
* Price: free
* Table of content: available
* Software license: GNU General Public License v3.0
* Custom Font:
* Export:
* Syndication:
* Draw diagram:

[https://obsidian.md/ Obsidian] v.1.8.7

* Supported {{OS}}: Windows, macOS & Linux
* Price: Free. Commercial use requires a commercial license.
* Table of contents:
* Software license: Proprietary
* Custom Font:
* Export: PDF, Markdown, etc.
* Syndication: available: [https://obsidian.md/sync Obsidian Sync] ''$''
* Draw diagrams: Available. Supports Mermaid syntax via the [https://obsidian.md/plugins?id=mermaid-tools Mermaid plugin]. {{Gd}}

[https://github.com/craig7351/bookMDViewer craig7351/bookMDViewer: Open-source MD viewer] <ref>https://www.facebook.com/groups/1024463572071191/</ref> Version 1.0.2
* Supported {{OS}}: Windows, macOS & Linux
* Price: Free
* Table of Contents: Yes
* Software License: [https://github.com/craig7351/bookMDViewer/blob/main/LICENSE MIT]
* Custom Font: Not supported
* Export: HTML
* Syndication: Not supported
* Diagram Support: Yes, Support Mermaid syntax

[https://notepad-plus-plus.org/downloads/ Notepad++] + [https://nea.github.io/MarkdownViewerPlusPlus/ MarkdownViewerPlusPlus]<ref>[https://hackmd.io/@yizhewang/Hk2S0VVDI Notepad++ 及 Markdown 外掛 - HackMD]</ref>
* Supported {{OS}}: Windows
* Price: free
* Table of content: not available {{exclaim}}
* Software license: [https://github.com/nea/MarkdownViewerPlusPlus/blob/master/LICENSE.md MIT license] {{Gd}}
* Custom Font: Yes, On the HTML tab, You are able to cutom the CSS.
* Export: HTML & PDF: Yes
* Syndication:
* Draw diagram: No, Not support Mermaid syntax

<pre>
(left blank intentionally)

title
* Supported {{OS}}: Windows, Mac & Linux
* Price: Free or need to paid
* Table of content: Available or not
* Software license: Proprietary ...
* Custom Font:
* Export:
* Syndication:
* Draw diagram:
</pre>

== Paid softwares ==
$ [https://www.typora.io/ Typora — a minimal markdown reading & writing app] v. 0.9.9.9.4.2 ([https://www.virustotal.com/zh-tw/file/9a35ffef9e10ccc66d67097744a0e76bd3f30ae340b8916c3e0172709c116dfa/analysis/ virus scan result of mac version: ok], [https://www.virustotal.com/zh-tw/file/33b5cc178f24f44567f8d7155d2eb60776db3d005fdc5edaad36267e66bec8d0/analysis/ virus scan result of windows version 0.9.48 (beta): ok])
* Supported {{OS}}: Win, Mac & Linux
* Price: Paid. Free trial 15 days.
* Table of content: available {{Gd}} Menu --> View --> Toggle Outline Panel. Or input {{kbd | key = <nowiki>[toc]</nowiki>}}[http://support.typora.io/Markdown-Reference/#table-of-contents-toc]
* Software license: Proprietary
* Custom Font: Available<ref>[http://support.typora.io/Custom-Font/ Custom Font]</ref>
* Export: PDF, HTML, Word (.docx), OpenOffice (.odt), RTF ... Some export formats require [https://github.com/jgm/pandoc/releases/tag/1.19.2.1 pandoc] to be installed. Reference: [http://support.typora.io/Install-and-Use-Pandoc/ Install and Use Pandoc]
* Syndication: n/a
* Draw diagram: Available. Support Mermaid syntax. [https://support.typora.io/Draw-Diagrams-With-Markdown/ Draw Diagrams With Markdown - Typora Support]

''$'' [https://www.sublimetext.com/ Sublime Text]
* Supported {{OS}}: Win & Mac
* Price: free
* Table of content: available on package [https://packagecontrol.io/packages/MarkdownTOC MarkdownTOC - Packages - Package Control]
* Software license: Proprietary
* Custom Font: Available<ref>[https://www.sublimetext.com/docs/3/font.html Font Settings – Sublime Text 3 Documentation]</ref>
* Export:
* Syndication: n/a
* Draw diagram:

''$'' [http://marked2app.com/ Marked] v. 2.5.10
* Supported {{OS}}: Mac
* Price: trial
* Table of content: Available
* Software license: Proprietary
* Custom Font: CSS
* Export: PDF, HTML, RTFD, RTF, DOC, DOCX, ODT, Markdown, OPML
* Syndication: n/a
* Draw diagram:

''$'' [https://caret.io/ Caret] v. 2.0.11
* Supported {{OS}}: Win, Mac & Linux
* Price: trial
* Table of content: not available {{exclaim}}
* Software license: Proprietary
* Custom Font:
* Export: PDF, HTML
* Syndication:
* Draw diagram:

''$'' [https://www.jetbrains.com/phpstorm/ PhpStorm IDE] v. 2017.1 + [https://plugins.jetbrains.com/plugin/7793-markdown-support Markdown support Plugin]
* Supported {{OS}}: Win, Mac & Linux
* Price: trial
* Table of content: not available {{exclaim}}
* Software license: Proprietary
* Custom Font:
* Export: not available
* Syndication: n/a
* Draw diagram:

''$'' [https://www.emeditor.com/ EmEditor (Text Editor)] + [https://www.emeditor.com/library/?wpfb_s=markdown Markdown syntax file]
* Supported {{OS}}: Win
* Price: Paid
* Table of content: not available {{exclaim}}
* Software license: Proprietary
* Custom Font:
* Export:
* Syndication: n/a
* Draw diagram:

''$'' [https://www.momothink.com/wonderpen 妙笔 - WonderPen] v. 1.3.1
* Supported {{OS}}: Mac
* Price: Paid
* Table of content: Available {{Gd}}
* Software license: Proprietary
* Custom Font: Available
* Export: Available: PNG, PDF, Word, Text, Html
* Syndication: Available
* Draw diagram:

== Online services ==
[https://hackmd.io/ HackMD - Collaborative markdown notes] ([https://hackmd.io/features features]) {{access | date = 2017-05-06}}
* Supported {{OS}}: OS independent. It's the online service.
* Price: free
* Table of content: available {{Gd}}
* Software license: [https://github.com/hackmdio/hackmd/blob/master/LICENSE hackmd/LICENSE MIT license] & open source[https://github.com/hackmdio/hackmd] {{Gd}}
* Custom Font:
* Export: Markdown, HTML & PDF

== Related articles ==
* [https://zh.wikipedia.org/wiki/Markdown#Markdown_.E7.BC.96.E8.BE.91.E5.99.A8 Markdown 編輯器 - 維基百科，自由的百科全書]
* [[Make Link]]
* [[Text Formatting Rules]]
* {{Gd}} [https://www.tablesgenerator.com/markdown_tables Markdown Tables generator - TablesGenerator.com]

== References ==

<references />

[[Category: Software]]

Markdown editors

2026-06-22T06:45:36Z

Planetoid: /* Free softwares */ + MarkdownViewerPlusPlus

Comparison of markdown editors: (1) Supported Operated System, (2) Price, (3) Table of content (TOC): Improve the navigation through a long-form content, (4) License, (5) Export format (6) Draw diagram which support [https://mermaid.js.org/intro/ mermaid syntax]

__TOC__

== Free softwares ==
[https://macdown.uranusjr.com/ MacDown: The open source Markdown editor for macOS] v. 0.7.2.x
* Supported {{OS}}: Mac
* Price: free
* Table of content: not available {{exclaim}}
* Software license: MIT license & open source[https://github.com/MacDownApp/macdown] {{Gd}}
* Custom Font:
* Export: HTML & PDF
* Syndication:
* Draw diagram:

[https://coteditor.com/ CotEditor -Text Editor for macOS]
* Supported {{OS}}: Mac
* Price: free
* Table of content: available on ''Outline menu'' {{Gd}}
* Software license: Apache License, Version 2.0 & open source on [https://github.com/coteditor/CotEditor github] {{Gd}}
* Custom Font:
* Export: not available
* Syndication: not available
* Draw diagram:

[http://www.bear-writer.com/ Bear - Notes for iPhone, iPad and Mac] v. 1.1.2
* Supported {{OS}}: Mac
* Price: free & paid for advanced features [http://www.bear-writer.com/#pricing]
* Table of content: not available {{exclaim}}
* Software license: Proprietary
* Custom Font:
* Export: RTF for free version. PDF, HTML, DOCS, JPG for paid version. Reference: [http://www.bear-writer.com/faq/Export%20your%20notes/ Export your notes]
* Syndication: available for paid version: [http://www.bear-writer.com/faq/Sync/Syncing%20your%20notes%20with%20Bear%20Pro/ Syncing your notes with Bear Pro - Bear - Faq and support].
* Draw diagram:

[https://atom.io/ Atom]
* Supported {{OS}}: Win & Mac
* Price: free
* Table of content: available on [https://atom.io/packages/markdown-toc markdown-toc]
* Software license: [https://atom.io/faq MIT license & open source][https://github.com/atom/atom] {{Gd}}
* Custom Font: Available<ref>[https://atom.io/packages/fonts fonts]</ref>
* Export:
* Syndication: n/a
* Draw diagram:
* Notes: Press ctrl+shift+m to toggle the preview panel

[http://brackets.io/ Brackets]
* Supported {{OS}}: Win & Mac
* Price: free
* Table of content: available on plugin [https://github.com/naokazuterada/MarkdownTOC GitHub - naokazuterada/MarkdownTOC: MarkdownTOC(Table Of Contents) Plugin for Sublime Text]
* Software license: [https://github.com/adobe/brackets/blob/master/LICENSE MIT License]
* Custom Font:
* Export:
* Syndication: n/a
* Draw diagram:

[https://code.visualstudio.com/ Visual Studio Code - Code Editing. Redefined] v. 1.22.2
* Supported {{OS}}: Win, Mac & Linux
* Price: free
* Table of content: Available on extension [https://code.visualstudio.com/docs/languages/markdown Markdown editing with Visual Studio Code]. After installed the extension --> Open the markdown file --> Right menu: '''Markdown TOC: Insert/Update'''
* Software license: "The source code is available under the [https://github.com/Microsoft/vscode/blob/master/LICENSE.txt MIT license agreement]." Details on [https://code.visualstudio.com/License License - Visual Studio Code].
* Custom Font: Available
* Export: n/a
* Syndication: n/a
* Draw diagram:

[https://subethaedit.net/ SubEthaEdit 5. Code, Write, Edit. Together.]
* Supported {{OS}}: Mac
* Price: free
* Table of content: not available {{exclaim}}
* Software license: [https://github.com/subethaedit/SubEthaEdit/blob/develop/LICENSE.txt MIT License (MIT)]. Source code available on [https://github.com/subethaedit/SubEthaEdit github]
* Custom Font: Available
* Export: HTML
* Syndication:
* Draw diagram:

[https://github.com/tamlok/vnote tamlok/vnote] v. 2.2
* Supported {{OS}}: Windows, Mac & Linux
* Price: free
* Table of content: Available on Menu > View > Tools: Outline
* Software license: [https://github.com/tamlok/vnote/blob/master/LICENSE MIT License]. Source code available on [https://github.com/tamlok/vnote github]
* Custom Font: CSS
* Export: PDF, HTML & Markdown
* Syndication:
* Draw diagram:

[https://marktext.app/ Mark Text] v0.16.3
* Supported {{OS}}: Windows, Mac & Linux
* Price: free
* Table of content: not available {{exclaim}}
* Software license: [https://github.com/marktext/marktext/blob/develop/LICENSE MIT License]. Source code available on [https://github.com/marktext/marktext github]
* Custom Font: Available on Menu > Preferences > Editor
* Export: HTML & PDF
* Syndication:
* Draw diagram:

[https://github.com/KDE/ghostwriter/releases/tag/2.1.6 ghostwriter] v.2.1.1
* Supported {{OS}}: Windows, Mac & Linux
* Price: free
* Table of content: available
* Software license: GNU General Public License v3.0
* Custom Font:
* Export:
* Syndication:
* Draw diagram:

[https://obsidian.md/ Obsidian] v.1.8.7

* Supported {{OS}}: Windows, macOS & Linux
* Price: Free. Commercial use requires a commercial license.
* Table of contents:
* Software license: Proprietary
* Custom Font:
* Export: PDF, Markdown, etc.
* Syndication: available: [https://obsidian.md/sync Obsidian Sync] ''$''
* Draw diagrams: Available. Supports Mermaid syntax via the [https://obsidian.md/plugins?id=mermaid-tools Mermaid plugin]. {{Gd}}

[https://github.com/craig7351/bookMDViewer craig7351/bookMDViewer: Open-source MD viewer] <ref>https://www.facebook.com/groups/1024463572071191/</ref> Version 1.0.2
* Supported {{OS}}: Windows, macOS & Linux
* Price: Free
* Table of Contents: Yes
* Software License: [https://github.com/craig7351/bookMDViewer/blob/main/LICENSE MIT]
* Custom Font: Not supported
* Export: HTML
* Syndication: Not supported
* Diagram Support: Yes, Support Mermaid syntax

[https://notepad-plus-plus.org/downloads/ Notepad++] + [https://nea.github.io/MarkdownViewerPlusPlus/ MarkdownViewerPlusPlus]
* Supported {{OS}}: Windows
* Price: free
* Table of content: not available {{exclaim}}
* Software license: [https://github.com/nea/MarkdownViewerPlusPlus/blob/master/LICENSE.md MIT license] {{Gd}}
* Custom Font: Yes
* Export: HTML & PDF: Yes
* Syndication:
* Draw diagram: No, Not support Mermaid syntax

<pre>
(left blank intentionally)

title
* Supported {{OS}}: Windows, Mac & Linux
* Price: Free or need to paid
* Table of content: Available or not
* Software license: Proprietary ...
* Custom Font:
* Export:
* Syndication:
* Draw diagram:
</pre>

== Paid softwares ==
$ [https://www.typora.io/ Typora — a minimal markdown reading & writing app] v. 0.9.9.9.4.2 ([https://www.virustotal.com/zh-tw/file/9a35ffef9e10ccc66d67097744a0e76bd3f30ae340b8916c3e0172709c116dfa/analysis/ virus scan result of mac version: ok], [https://www.virustotal.com/zh-tw/file/33b5cc178f24f44567f8d7155d2eb60776db3d005fdc5edaad36267e66bec8d0/analysis/ virus scan result of windows version 0.9.48 (beta): ok])
* Supported {{OS}}: Win, Mac & Linux
* Price: Paid. Free trial 15 days.
* Table of content: available {{Gd}} Menu --> View --> Toggle Outline Panel. Or input {{kbd | key = <nowiki>[toc]</nowiki>}}[http://support.typora.io/Markdown-Reference/#table-of-contents-toc]
* Software license: Proprietary
* Custom Font: Available<ref>[http://support.typora.io/Custom-Font/ Custom Font]</ref>
* Export: PDF, HTML, Word (.docx), OpenOffice (.odt), RTF ... Some export formats require [https://github.com/jgm/pandoc/releases/tag/1.19.2.1 pandoc] to be installed. Reference: [http://support.typora.io/Install-and-Use-Pandoc/ Install and Use Pandoc]
* Syndication: n/a
* Draw diagram: Available. Support Mermaid syntax. [https://support.typora.io/Draw-Diagrams-With-Markdown/ Draw Diagrams With Markdown - Typora Support]

''$'' [https://www.sublimetext.com/ Sublime Text]
* Supported {{OS}}: Win & Mac
* Price: free
* Table of content: available on package [https://packagecontrol.io/packages/MarkdownTOC MarkdownTOC - Packages - Package Control]
* Software license: Proprietary
* Custom Font: Available<ref>[https://www.sublimetext.com/docs/3/font.html Font Settings – Sublime Text 3 Documentation]</ref>
* Export:
* Syndication: n/a
* Draw diagram:

''$'' [http://marked2app.com/ Marked] v. 2.5.10
* Supported {{OS}}: Mac
* Price: trial
* Table of content: Available
* Software license: Proprietary
* Custom Font: CSS
* Export: PDF, HTML, RTFD, RTF, DOC, DOCX, ODT, Markdown, OPML
* Syndication: n/a
* Draw diagram:

''$'' [https://caret.io/ Caret] v. 2.0.11
* Supported {{OS}}: Win, Mac & Linux
* Price: trial
* Table of content: not available {{exclaim}}
* Software license: Proprietary
* Custom Font:
* Export: PDF, HTML
* Syndication:
* Draw diagram:

''$'' [https://www.jetbrains.com/phpstorm/ PhpStorm IDE] v. 2017.1 + [https://plugins.jetbrains.com/plugin/7793-markdown-support Markdown support Plugin]
* Supported {{OS}}: Win, Mac & Linux
* Price: trial
* Table of content: not available {{exclaim}}
* Software license: Proprietary
* Custom Font:
* Export: not available
* Syndication: n/a
* Draw diagram:

''$'' [https://www.emeditor.com/ EmEditor (Text Editor)] + [https://www.emeditor.com/library/?wpfb_s=markdown Markdown syntax file]
* Supported {{OS}}: Win
* Price: Paid
* Table of content: not available {{exclaim}}
* Software license: Proprietary
* Custom Font:
* Export:
* Syndication: n/a
* Draw diagram:

''$'' [https://www.momothink.com/wonderpen 妙笔 - WonderPen] v. 1.3.1
* Supported {{OS}}: Mac
* Price: Paid
* Table of content: Available {{Gd}}
* Software license: Proprietary
* Custom Font: Available
* Export: Available: PNG, PDF, Word, Text, Html
* Syndication: Available
* Draw diagram:

== Online services ==
[https://hackmd.io/ HackMD - Collaborative markdown notes] ([https://hackmd.io/features features]) {{access | date = 2017-05-06}}
* Supported {{OS}}: OS independent. It's the online service.
* Price: free
* Table of content: available {{Gd}}
* Software license: [https://github.com/hackmdio/hackmd/blob/master/LICENSE hackmd/LICENSE MIT license] & open source[https://github.com/hackmdio/hackmd] {{Gd}}
* Custom Font:
* Export: Markdown, HTML & PDF

== Related articles ==
* [https://zh.wikipedia.org/wiki/Markdown#Markdown_.E7.BC.96.E8.BE.91.E5.99.A8 Markdown 編輯器 - 維基百科，自由的百科全書]
* [[Make Link]]
* [[Text Formatting Rules]]
* {{Gd}} [https://www.tablesgenerator.com/markdown_tables Markdown Tables generator - TablesGenerator.com]

== References ==

<references />

[[Category: Software]]

排除 MySQL 技術疑難問題

2026-06-19T15:28:31Z

Planetoid: /* 錯誤代碼: 13 拒絕存取 */

排除 MySQL 技術疑難問題

{{LanguageSwitcher | content = [[Troubleshooting of MySQL errors | English]], [[排除 MySQL 技術疑難問題|漢字]]}}

本文探討了使用 MySQL 時遇到的各種常見問題，並根據其性質和影響將它們分為七個不同的類別。這些類別有助於識別潛在的問題並促進高效的故障排除。這些問題分類如下：

# 安裝和配置問題：與 MySQL 設置和配置相關的問題。
# 資料庫和表操作錯誤：在創建、刪除或訪問資料庫和表時出現的問題。
# 訪問和認證錯誤：涉及用戶權限和認證的錯誤。
# SQL 和查詢錯誤：與 SQL 查詢的語法和執行相關的問題。
# 連接和網路錯誤：用戶端與 MySQL 伺服器之間的連接問題。
# 資源和環境錯誤：由系統資源如磁碟空間、記憶體限制或文件系統權限引起的錯誤。
# 其他工具特定錯誤：特定於資料庫工具（如 Navicat）的錯誤，或來自腳本環境（如 PHP）的問題。

== 安裝和配置問題 ==
=== 找不到檔案 mysqldump ===

# 尋找 mysqldump 檔案的位置
#* 在 {{Mac}} 或 {{Linux}} 上輸入命令 {{kbd | key=<nowiki>sudo find / -iname mysqldump</nowiki>}}
#* 如果你安裝了
#** [https://www.apachefriends.org/index.html XAMPP] 在 {{Mac}} 上: 完整路徑是 {{kbd | key=<nowiki>/Applications/XAMPP/xamppfiles/bin/mysqldump</nowiki>}}
#** [https://www.apachefriends.org/index.html XAMPP] 在 X 磁碟上的 {{Win}}: 完整路徑是 {{kbd | key=<nowiki>X:\xampp\mysql\bin\mysqldump.exe</nowiki>}}
#** [https://www.mamp.info/en/ MAMP & ''$'' MAMP PRO] 在 {{Mac}} 上: 完整路徑是 {{kbd | key=<nowiki>/Applications/MAMP/Library/bin/mysqldump</nowiki>}}
# 輸入 mysqldump 命令的完整路徑
#* 之前導致錯誤的命令 {{kbd | key=<nowiki>mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz</nowiki>}}
#* 新命令 {{kbd | key=<nowiki>/Applications/XAMPP/xamppfiles/bin/mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz</nowiki>}}

=== 無法在 Windows 上啟動 MySQL 服務 ===

# [https://dev.mysql.com/doc/refman/5.7/en/data-directory.html MySQL 數據目錄] 需被 {{kbd | key=NETWORK SERVICE}} 在 Windows Server 2008 上有寫入權限<ref>[https://errerrors.blogspot.com/2016/12/mysql-windows-server-2008-r2.html 如何修改 MySQL 伺服器的資料目錄 (Windows Server 2008 R2)]</ref>
# 目錄 {{kbd | key=secure-file-priv}} 需被 {{kbd | key=NETWORK SERVICE}} 在 Windows Server 2008 上有寫入權限

== 資料庫和表操作錯誤 ==
=== 無法開啟單表表空間檔案 filename.ibd ===
版本：XAMPP 5.6.15-1，運行於{{Mac}}

狀況：Mac 意外關機，且資料庫沒有正常關閉。重啟 Mac 後，無法啟動 MySQL 服務<ref>[http://stackoverflow.com/questions/35184367/error-could-not-open-single-table-tablespace-file-scrapers-records-ibd mysql - Error: could not open single-table tablespace file .\scrapers\records.ibd - Stack Overflow]</ref><ref>[http://chepri.com/our-blog/mysql-innodb-corruption-and-recovery/ MySQL Won't Start - InnoDB Corruption and Recovery. - Chepri]</ref>。

可能的解決方案：
# 編輯位於：/Applications/XAMPP/xamppfiles/etc/my.cnf 的 MySQL 設定檔
# 增加此行：{{kbd | key=<nowiki>innodb_force_recovery = 1</nowiki>}}
# 嘗試啟動 MySQL 服務
# 如果 MySQL 服務成功啟動，編輯 MySQL 設定檔並註釋掉這行：{{kbd | key=<nowiki>#innodb_force_recovery = 1</nowiki>}}
# 重啟 MySQL 服務

=== errno 41 - 刪除資料庫時出錯 ===
訊息：執行 '''DROP DATABASE `TABLE_NAME`''' 時出現 '''錯誤刪除資料庫（無法刪除目錄 '.\TABLE_NAME'，errno: 41）'''；

解決方案：[https://stackoverflow.com/questions/17947255/error-in-dropping-a-database-in-mysql-cant-rmdir-oro-errno-41/19888293 phpmyadmin - 刪除 MySQL 中的資料庫時出錯（無法刪除目錄 '.\oro'，errno: 41）- Stack Overflow]

=== ERROR 1005 (HY000) at line xx: 無法創建表 'TABLE_NAME' (errno: 28) ===

解決方案
* 檢查 mysql 數據文件夾所在的磁碟空間是否足夠。例如，在 {{Linux}} 上輸入 {{kbd | key=df -h}}
* 更多資訊請見 [http://stackoverflow.com/questions/11045279/error-1005-hy000-cant-create-table-errno-150 mysql - ERROR 1005 (HY000): 無法創建表 (errno: 150) - Stack Overflow]。

=== ERROR 1006 (HY000): 無法創建資料庫 'DATABASE_NAME' (errno: 28) ===
解決方案
* 檢查 mysql 數據文件夾所在的磁碟空間是否足夠。例如，在 {{Linux}} 上輸入 {{kbd | key=df -h}}
* 更多資訊請見 [http://stackoverflow.com/questions/18719748/error-1006-hy000-cant-create-database-errno-13-mysql-5-6-12 ERROR 1006 (HY000) 無法創建資料庫 (errno: 13) MySQL 5.6.12 - Stack Overflow]。

=== ERROR 1010: Error dropping database (can't rmdir) ===
Message: <pre>Error dropping database (can't rmdir '.\<db_name>\', errno: 17)</pre>

解決方式
* 手動刪除資料庫目錄中殘留的非 MySQL 檔案（例如 .sql 檔案），再重新執行 DROP DATABASE；或直接手動刪除整個資料夾。

根本原因
* MySQL 的 DROP DATABASE 只會移除它所管理的檔案。資料目錄中若存在其他外來檔案（例如 .sql 備份檔），將導致 rmdir 無法移除資料夾，進而觸發 errno 17（ENOTEMPTY）。

=== ERROR 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument) ===
訊息： [Err] 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument)

解決方案：
* 檢查 DATABASE\TABLE.frm 文件是否存在。如果不存在，可能需要在執行 MySQL 查詢前先創建該表。
* 檢查 DATABASE\TABLE.frm 文件或所在文件夾的權限<ref>[http://stackoverflow.com/questions/12106727/mysql-copying-tables-files-gives-rise-to-error-1017-hy000-cant-find-file MySQL，複製表文件出現 “ERROR 1017 (HY000): Can't find file:” 即使它在那裡 - Stack Overflow]</ref><ref>[https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/2762-error-1017-hy000-can-t-find-file 錯誤 1017 (HY000): 找不到文件 - Percona 社區]</ref>。''未經驗證''

=== ERROR 1052 - Column 'column_name' in field list is ambiguous ===
訊息：Error Code: 1052. Column 'column_name' in field list is ambiguous 欄位列表中的 'column_name' 模糊不清

原因：由於 'column_name' 出現在兩個或多個表中...<ref>[https://stackoverflow.com/questions/19351633/error-code-1052-column-admin-id-in-field-list-is-ambiguous mysql - 錯誤代碼：1052 欄位列表中的 'admin_id' 模糊不清 - Stack Overflow]</ref>

解決方案：只保留一個表名 'column_name' 或添加表名別名。

=== 錯誤 1054 - 'where 子句' 中未知的欄位 ===
訊息：[Err] 1054 - 'where 子句' 中未知的欄位 'xxx'

解決方案：
# 檢查 'xxx' 欄位名稱是否存在
# 如果 'xxx' 欄位名稱是由 [http://dev.mysql.com/doc/refman/5.7/en/example-user-variables.html 使用者定義的變數] 計算得出。將整個查詢放入另一個母查詢中。
<pre>
-- 遇到錯誤訊息的查詢：[Err] 1054 - 'where 子句' 中未知的欄位 'rank'
SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`
FROM
(
...
) semi
WHERE semi.rank <= 10
</pre>

將整個查詢放入另一個母查詢中。
<pre>
SELECT final.*
FROM
(
SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`
FROM
(
...
) semi
) final
WHERE final.rank <= 10
</pre>

=== 錯誤 1114 (HY000): 表格 `TABLE_NAME` 已滿 ===
可能的解決方案
* 因為 [https://dev.mysql.com/doc/refman/5.7/en/data-directory.html MySQL 數據目錄] 所在的硬碟分區已滿或幾乎已滿，釋放一些硬碟空間<ref>[http://stackoverflow.com/questions/730579/error-1114-hy000-the-table-is-full mysql - 錯誤 1114 (HY000): 表格已滿 - Stack Overflow]</ref>。

=== 錯誤 1170: 在鍵規格中使用 BLOB/TEXT 欄位且沒有鍵長 ===
情況
* 嘗試建立新表格時的 SQL 語法

<pre>
DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`url` text COLLATE utf8mb4_unicode_ci NOT NULL,
`status` tinyint(2) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY (`job_id`),
UNIQUE KEY `url` (`url`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
</pre>

解決方案：在鍵規格中使用 BLOB/TEXT 欄位並指定鍵長，例如 {{kbd | key=`url`(500)}}
<pre>
DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`url` text COLLATE utf8mb4_unicode_ci NOT NULL,
`status` tinyint(2) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`(500))
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
</pre>

=== 錯誤 1813: 表格 xxx 的表空間已存在 ===
訊息：錯誤 1813 表格 xxx 的表空間已存在。

解決方案
* [https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp mysql - 錯誤：表格 xxx 的表空間已存在。請在導入前丟棄表空間 - Stack Overflow]

=== 錯誤 1049 (42000): 未知的資料庫 ===
訊息：錯誤 1049 (42000): 未知的資料庫 'MY_DATABASE_p'

解決方案：
* 檢查 'MY_DATABASE_p' 資料庫是否存在
* 如果您在使用控制台時希望連接到 'MY_DATABASE' 資料庫，請檢查資料庫名稱後是否沒有 {{kbd | key=TAB}} 字元。

=== 錯誤 1070 (42000): 指定的鍵太長；最大鍵長為 767 位元組 ===
'''環境''': MySQL 5.6

'''根本原因''': "預設情況下，索引鍵前綴長度限制為 767 位元組。參見第 13.1.13 節，“CREATE INDEX 語句”。例如，在 TEXT 或 VARCHAR 列上，如果使用 utf8mb3 字元集，並且每個字元最多 3 位元組，您可能會在列前綴索引超過 255 個字元時達到此限制。當啟用 innodb_large_prefix 配置選項時，對於使用 DYNAMIC 或 COMPRESSED 行格式的 InnoDB 表，索引鍵前綴長度限制提高到 3072 位元組。" <ref>[https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html MySQL :: MySQL 5.6 參考手冊 :: 14.22 InnoDB 限制]</ref>

'''解決方案'''<ref>[https://www.opencli.com/mysql/mysql-%E5%95%8F%E9%A1%8C-1071-42000-specified-key-was-too-long MySQL 問題: 1071 (42000): 指定的鍵太長]</ref><ref>[https://terrytongcc.com/%E5%A6%82%E4%BD%95%E8%A7%A3%E6%B1%BAmysql%E5%87%BA%E7%8F%BEerror-1118-row-size-too-large-8126%E5%95%8F%E9%A1%8C/ 如何解決 MySQL 出現 Error 1118: row size too large (> 8126) 問題 | Terry Tong – 全棧 Web 開發人員]</ref>:
* 執行以下 SQL 查詢：

<pre>
SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_format_max = Barracuda;
</pre>

* 修改 MySQL 配置文件
<pre>
innodb_file_per_table = 1
innodb-file-format = BARRACUDA
innodb-large-prefix = ON
innodb_file_format_max = BARRACUDA
</pre>

* 重啟 MySQL 伺服器

* 執行以下 SQL 查詢：
<pre>
ALTER TABLE `table_name` ROW_FORMAT=COMPRESSED;
</pre>

== 訪問和認證錯誤 ==
=== [Warning] Using a password on the command line interface can be insecure ===
問題描述：
使用 bash 命令匯出或匯入 MySQL 資料庫時，可能會遇到以下警告：[警告] 在命令行介面上使用密碼可能不安全。

<pre>
mysql: [Warning] Using a password on the command line interface can be insecure.

or

mysqldump: [Warning] Using a password on the command line interface can be insecure.
</pre>

這通常發生在使用如下命令時：

<pre>
mysqldump -u USERNAME -p -h 127.0.0.1 --default-character-set=utf8 DATABASE > DATABASE.sql
</pre>

解決方案：要解決此警告並提高安全性，請按照以下步驟操作：
# 使用 [https://dev.mysql.com/doc/refman/8.4/en/mysql-config-editor.html mysql_config_editor] 設定登入路徑 (login path)：{{kbd | key=<nowiki>mysql_config_editor set --login-path=local --host=localhost --user=root --password</nowiki>}}
# 匯出或匯入 MySQL 資料庫時使用登入路徑 (1) 匯出時：{{kbd | key=<nowiki>mysqldump --login-path=local --default-character-set=utf8 資料庫名稱 > 資料庫名稱.sql</nowiki>}} (2) 匯入時：{{kbd | key=<nowiki>pv 資料庫名稱.sql.gz | gunzip | mysql --login-path=local --default_character_set utf8 --force 資料庫名稱</nowiki>}}
# 查看所有已設定的登入路徑 {{kbd | key=<nowiki>mysql_config_editor print --all</nowiki>}}

透過使用登入路徑，可以避免在命令行中暴露密碼，解決安全警告。

=== 錯誤 1044 (42000): 拒絕使用者 'USER'@'localhost' 存取資料庫 'DATABASE_NAME' ===
訊息：{{kbd | key=<nowiki>錯誤 1044 (42000): 拒絕使用者 'USER'@'localhost' 存取資料庫 'DATABASE_NAME'</nowiki>}}
<pre>
pv file.sql.gz | gunzip | mysql -u USER -p --host=127.0.0.1 --default_character_set utf8 DATABASE_NAME
</pre>

解決方案：
* 檢查指定使用者名稱和資料庫名稱的權限
<pre>
$ mysql -u USER -p --host=127.0.0.1
輸入密碼：

mysql> use DATABASE_NAME
</pre>

訊息：{{kbd | key=<nowiki>mysqldump: 執行 LOCK TABLES 時出錯: 1044: 拒絕使用者 'USER'@'localhost' 存取資料庫 'DATABASE_NAME'</nowiki>}}
<pre>
$ mysqldump -h 127.0.0.1 -u USER -p DATABASE_NAME TABLE_NAME > TABLE_NAME.sql
</pre>

解決方案：
* 如果無法給予使用者權限，添加 mysqldump 選項 {{kbd | key=<nowiki>--skip-lock-tables</nowiki>}} <ref>[https://stackoverflow.com/questions/7415698/skip-lock-tables-and-mysqldump mysql - 跳過鎖定表和 mysqldump - Stack Overflow]</ref>。
<pre>
$ mysqldump -h 127.0.0.1 -u USER -p --skip-lock-tables DATABASE_NAME TABLE_NAME > TABLE_NAME.sql
</pre>

=== 錯誤 1827 (HY000): 密碼哈希不符合預期格式。檢查是否使用了正確的密碼算法與 PASSWORD() 函數。 ===
錯誤的 SQL 查詢如下：
<pre>
CREATE USER 'test'@'localhost' IDENTIFIED BY PASSWORD 'my_password';
</pre>

解決方案：
(1) 檢查帳戶是否已創建
<pre>
SELECT User,Host FROM mysql.user;
</pre>

(2a) 如果帳戶已創建，為該帳戶設定密碼。
<pre>
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('my_password');
</pre>

(2b) 如果帳戶未創建，重新創建帳戶。
<pre>
CREATE USER 'test'@'localhost' IDENTIFIED BY 'my_password';
</pre>

參考資料
* [http://dev.mysql.com/doc/refman/5.7/en/create-user.html MySQL :: MySQL 5.7 參考手冊 :: 14.7.1.2 CREATE USER 語法]
* [http://dev.mysql.com/doc/refman/5.7/en/set-password.html MySQL :: MySQL 5.7 參考手冊 :: 14.7.1.7 SET PASSWORD 語法]
* [http://dev.mysql.com/doc/refman/5.7/en/drop-user.html MySQL :: MySQL 5.7 參考手冊 :: 14.7.1.3 DROP USER 語法]

=== 錯誤 1045 (28000): 拒絕使用者存取 ===
訊息：錯誤 1045 (28000): 拒絕使用者 'user'@'localhost' 存取（使用密碼：YES）

解決方案：
* 檢查使用者名稱的打字錯誤。
* 檢查密碼的打字錯誤。
* 如果您正在使用控制台命令，如果密碼包含特殊字元，請轉義密碼，例如 {{kbd | key=<nowiki>mysql -u root -p'PASSWORD'</nowiki>}}<ref>[http://superuser.com/questions/123928/escaping-a-password-using-mysqldump-console 使用 mysqldump 控制台轉義密碼 - Super User]</ref>
* 您可能需要刪除現有的帳戶設定並重新配置。

=== 錯誤！: SQLSTATE[28000]: 無效的授權規格：1045 拒絕訪問 ===
訊息：錯誤！: {{kbd | key=<nowiki>SQLSTATE[28000]: 無效的授權規格：1045 拒絕使用者 'user'@'localhost' 存取（使用密碼：YES）</nowiki>}}

解決方案：
* 如果您執行了查詢 {{kbd | key=INTO OUTFILE}}，您需要授予文件權限，例如：{{kbd | key=GRANT FILE ON *.* TO 'user'@'localhost';}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_file MySQL :: MySQL 5.7 參考手冊 :: 6.2.1 提供的權限]</ref><ref>[https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold MYSQL into outfile "拒絕訪問" - 但我的使用者有 "全部" 訪問權限.. 且文件夾是 CHMOD 777 - Stack Overflow]</ref>。

相關問題："錯誤！: SQLSTATE[HY000]: 一般錯誤：1290 MySQL 伺服器正在使用 --secure-file-priv 選項運行，所以它不能執行這個聲明"<ref>[https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql 資料庫 - 如何處理 MySQL 中的 --secure-file-priv？ - Stack Overflow]</ref>

== SQL 查詢語法錯誤 ==
=== 捕獲異常：SQLSTATE[HY093]：無效的參數編號：參數未定義 ===
解決方案

* 問號的數量與查詢值的數量不一致 <ref>[https://stackoverflow.com/questions/10966251/sqlstatehy093-invalid-parameter-number-parameter-was-not-defined php Invalid parameter number: parameter was not defined - Stack Overflow]</ref>
* 查詢值的數組不應該是關聯數組。使用順序數組！

=== 錯誤！：SQLSTATE[HY000]：一般錯誤 ===
訊息：錯誤！：SQLSTATE[HY000]：一般錯誤

狀況：當我使用 [http://php.net/manual/en/book.pdo.php PHP: PDO]

解決方案：
* "您不應該在 update 或 insert 查詢中使用 {{kbd | key=<nowiki>$result = $stmt->fetchAll();</nowiki>}}" <ref>[https://stackoverflow.com/questions/12979510/pdo-error-sqlstatehy000-general-error-when-updating-database php - PDO error: " SQLSTATE[HY000]: General error " When updating database - Stack Overflow]</ref>。
* 您不應該在查詢 {{kbd | key=<nowiki>INTO OUTFILE ...</nowiki>}} 中使用 {{kbd | key=<nowiki>$result = $stmt->fetchAll();</nowiki>}} <ref>[http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/ Save MySQL query results into a text or CSV file]</ref><ref>[https://stackoverflow.com/questions/13369164/cant-create-write-to-file-errcode-22 mysql - Can't create/write to file (Errcode: 22) - Stack Overflow]</ref>。

=== 錯誤：ASCII '\0' 出現在語句中，但這是不允許的，除非啟用了 --binary-mode 選項並且 mysql 以非互動模式運行 ===
訊息：{{kbd | key=<nowiki>錯誤：ASCII '\0' 出現在語句中，但這是不允許的，除非啟用了 --binary-mode 選項並且 mysql 以非互動模式運行。如果期望 ASCII '\0'，請將 --binary-mode 設為 1。查詢：''。</nowiki>}}

狀況：當我導入 sql 文件時遇到上述錯誤訊息。

解決方案：解壓縮文件然後再次導入文件<ref>[https://stackoverflow.com/questions/17158367/enable-binary-mode-while-restoring-a-database-from-an-sql-dump mysql - Enable binary mode while restoring a Database from an SQL dump - Stack Overflow]</ref>。sql 文件是壓縮文件。您可以使用 [https://en.wikipedia.org/wiki/File_(command) file (command)] 來識別文件的類型。
<pre>
$ file compressed.sql
compressed.sql：gzip 壓縮數據

$ file plain_text.sql
plain_text.sql：UTF-8 Unicode 文本，帶有非常長的行
</pre>

=== 錯誤 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ===
訊息
<pre>
查詢發生錯誤 (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.posts.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
</pre>

遇到錯誤的查詢
<pre>
-- 建立範例資料表
CREATE TABLE posts (
id INT,
post_id VARCHAR(10),
author VARCHAR(50),
content TEXT,
likes INT,
post_time DATETIME
);

-- 插入範例資料
INSERT INTO posts VALUES
(1, 'A123', 'Alice', '第一篇文章', 10, '2024-01-01'),
(2, 'A123', 'Bob', '推推', 5, '2024-01-02'),
(3, 'A123', 'Bob', '讚讚', 3, '2024-01-03');

-- 這會報錯
SELECT
id, -- 問題: 沒有在 GROUP BY 中
post_id,
author,
GROUP_CONCAT(content SEPARATOR "\n") AS content, -- 問題: 沒有在 GROUP BY 中
likes -- 問題: 沒有在 GROUP BY 中
FROM posts
GROUP BY post_id, author;
</pre>

解決方案：修正後的查詢 (1) SELECT 中的每個欄位要麼放在 GROUP BY 中、或 (2) 使用彙總函數 (如 MAX, MIN, SUM, COUNT, GROUP_CONCAT)
<pre>
-- 方法一：取消 `sql_mode=only_full_group_by`:
有兩種方法可以取消 `sql_mode=only_full_group_by`:

1. 暫時性修改 (僅對當前會話有效)
```sql
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
```

2. 永久性修改 (修改 MySQL 設定檔)
- 找到 MySQL 設定檔 (通常是 `my.cnf` 或 `my.ini`)
- 在 `[mysqld]` 區段加入或修改:
```ini
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
```

注意事項:
1. 修改設定檔後需要重啟 MySQL 服務才會生效
2. 不建議取消這個設定，因為:
- 可能導致查詢結果不可預測
- 違反 SQL 標準
- 可能在未來版本中產生相容性問題

建議的做法是修正 SQL 查詢而不是更改 SQL mode。

-- 方法二: 把所有欄位加入 GROUP BY
SELECT
id,
post_id,
author,
content,
likes
FROM posts
GROUP BY post_id, author, id, content, likes;

-- 方法三: 對非 GROUP BY 的欄位使用彙總函數
SELECT
MAX(id) as id,
post_id,
author,
GROUP_CONCAT(content) as contents,
SUM(likes) as total_likes
FROM posts
GROUP BY post_id, author;
</pre>

=== 錯誤 1235: 這個版本的 MySQL 尚未支持 'LIMIT & IN/ALL/ANY/SOME 子查詢' ===
訊息：錯誤 1235 (42000)：這個版本的 MySQL 尚未支持 'LIMIT & IN/ALL/ANY/SOME 子查詢'。

解決方案：將子查詢的語法更改為 {{kbd | key =column_name BETWEEN start_number to end_number}}

進一步閱讀：
* [https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.6/en/subquery-restrictions.html MySQL :: MySQL 限制和局限 :: 4 子查詢的限制]

=== 錯誤 1267: 字元集混合不合法 (utf8mb4_general_ci,COERCIBLE) 與 (latin1_swedish_ci,IMPLICIT) 用於 'concat' 操作 ===
錯誤情況：嘗試合併不同類型的數據，例如 CONCAT(string, int)

解決方案：{{kbd | key =<nowiki>SELECT CONCAT(`string_column`, CONVERT(`int_column`, CHAR))</nowiki>}} 或 {{kbd | key =<nowiki>SELECT CONCAT(`string_column`, CAST(`int_column` AS CHAR))</nowiki>}}<ref>[https://stackoverflow.com/questions/15368753/cast-int-to-varchar mysql - 將 int 轉換為 varchar - Stack Overflow]</ref><ref>[http://blog.51cto.com/bian5399/1092772 mysql字元集問題：不合法的字元集混合 - 51CTO部落格]</ref>

=== 錯誤 1305 - 函數 MY_TABLE.MY_FUNCTION 不存在 ===
訊息：MySQL 錯誤 1305 - 函數 MY_TABLE.MY_FUNCTION 不存在

解決方案：修正函數名稱中的打字錯誤

=== 錯誤 1690 - BIGINT UNSIGNED 值超出範圍 ===
訊息：MySQL 錯誤 #1690 (BIGINT UNSIGNED 值超出範圍)

解決方案 <ref>[https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html MySQL :: MySQL 8.0 參考手冊 :: 11.2.6 超出範圍和溢出處理]</ref><ref>[https://stackoverflow.com/questions/34115917/mysql-error-1690-bigint-unsigned-value-is-out-of-range-for-unix-timestamp MySQL 錯誤 #1690 (BIGINT UNSIGNED 值超出範圍) 用於 UNIX_TIMESTAMP() - Stack Overflow]</ref>:
<pre>
SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
</pre>

=== 錯誤 1366: 字串值不正確 ===
訊息：SQLSTATE[HY000]: 一般錯誤：1366 字串值 '\xF0\x9F\x87\xAF\xF0\x9F...' 對於欄位 'XXX' 不正確

解決方案：
* 檢查 PHP PDO 的字元集。並執行 {{kbd | key=set names utf8mb4}}<ref>[https://stackoverflow.com/questions/54947392/incorrect-string-value-when-trying-to-pass-emoji-to-the-db-encoded-with-utf8mb4 php - 嘗試將使用 utf8mb4 編碼的表情符號傳遞給資料庫時字串值不正確 - Stack Overflow]</ref>
* 檢查表的配置
** 字元集 (字元集、字元集): {{kbd | key=utf8mb4}}
** 排序規則 (定序、字元序): {{kbd | key=utf8mb4_unicode_ci}}

=== 錯誤！: SQLSTATE[42000]: 語法錯誤或存取違規 ===
訊息：{{kbd | key=<nowiki>錯誤！: SQLSTATE[42000]: 語法錯誤或存取違規：1064 您在 SQL 語法中有錯誤；請檢查手冊，了解您的 MySQL 伺服器版本對應的正確語法以用於 ...</nowiki>}}

解決方案：
* [[SQL syntax debug | SQL 語法除錯]]
* [https://www.eversql.com/sql-syntax-check-validator/ SQL 語法檢查在線上，SQL 驗證器，即時 SQL 編譯器在線上 - EverSQL] {{exclaim}} 不支持 [https://www.php.net/manual/en/pdo.prepare.php PHP: PDO::prepare]，其中查詢語法包含問號。

== 連接和網路錯誤 ==
=== MySQL 伺服器已斷開 (has gone away) ===
步驟
* 在 MySQL 設定檔啟用 {{kbd | key = log_error}} 選項，例如 {{kbd | key = <nowiki>log_error="file_name_of_error_log"</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/error-log.html MySQL :: MySQL 8.0 Reference Manual :: 5.4.2 The Error Log]</ref>。
* 範例錯誤日誌位於 {{kbd | key = <nowiki>file_name_of_error_log</nowiki>}} 如下：

<pre>
2019-05-23T08:52:19.989876Z 99980 [Note] 中斷連接 99980 到資料庫：'DB_NAME' 用戶：'DB_USER' 主機：'localhost' (收到的封包大於 'max_allowed_packet' 位元組)
</pre>

* 如果 MySQL 用戶具有 {{kbd | key = <nowiki>SUPER</nowiki>}} 權限<ref>[https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL]</ref>，則增加 {{kbd | key = <nowiki>max_allowed_packet</nowiki>}} 的值<ref>[https://stackoverflow.com/questions/5688403/how-to-check-and-set-max-allowed-packet-mysql-variable php - how to check and set max_allowed_packet mysql variable - Stack Overflow]</ref>。

=== 錯誤：SQLSTATE[HY000] [2002] 沒有這樣的文件或目錄 ===
錯誤訊息：SQLSTATE[HY000] [2002] 沒有這樣的文件或目錄

解決方案：
* 將資料庫主機從 {{kbd | key=localhost}} 修改為 {{kbd | key=127.0.0.1}}

=== 錯誤 2002: 無法連接: SQLSTATE[HY000] [2002] ===
Windows Cygwin 終端機狀況：
<pre>
$ mysql -u root -p
輸入密碼：
錯誤 2002 (HY000): 無法通過 '/var/run/mysql.sock' 連接到本地 MySQL 伺服器 (2)

$ mysql -h localhost -u root -p
輸入密碼：
錯誤 2002 (HY000): 無法通過 '/var/run/mysql.sock' 連接到本地 MySQL 伺服器 (2)
</pre>

解決方案：
* 將 {{kbd | key=<nowiki>-h localhost</nowiki>}} 更改為 {{kbd | key=<nowiki>-h 127.0.0.1</nowiki>}}
<pre>
$ mysql -h 127.0.0.1 -u root -p
</pre>
* 如果仍然不行，重啟伺服器並重新啟動 MySQL 服務。

=== 錯誤 2002: SQLSTATE[HY000] [2002] 通常只允許每個通訊端位址 (協議/網路位址/埠) 的一次使用 ===

訊息：(1) [2002] 通常只允許每個通訊端位址 (協議/網路位址/埠) 的一次使用 (2) "SQLSTATE[HY000] [2002] 一次只能用一個通訊端位址 (通訊協定/網路位址/連接埠)。" 用中文表示

解決方案：[https://stackoverflow.com/questions/10317974/mysql-php-error2002-only-one-usage-of-each-socket-address-protocol-network-a MySQL/PHP 錯誤:(2002) 通常只允許每個通訊端位址 (協議/網路位址/埠) 的一次使用 - Stack Overflow]

=== 錯誤 2003 (HY000): 無法連接到 'IP' 上的 MySQL 伺服器 ===
'''錯誤 2003 (HY000): 無法連接到 'IP' 上的 MySQL 伺服器'''

解決方案：
* 檢查 IP 是否有效

'''錯誤 2003 (HY000): 無法連接到 'IP' 上的 MySQL 伺服器 (111 "連接被拒絕")'''

解決方案：
* 檢查 MySQL 服務是否在運行<ref>[https://www.cyberciti.biz/faq/how-to-find-out-if-mysql-is-running-on-linux/ 如何找出 MySQL 是否在 Linux 上運行]</ref>。如果沒有，啟動 MySQL 服務。
* 檢查防火牆規則

'''錯誤 2003 (HY000): 無法連接到 'IP' 上的 MySQL 伺服器 (116 "連接超時")'''

解決方案：
* 檢查 MySQL 的配置
** 註釋掉 [https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_bind-address bind-address] = 127.0.0.1 或設置為 *
** 註釋掉 [https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_skip-networking skip-networking]
* 檢查 MySQL 資料庫用戶的權限。
* 在 (1) 個人電腦 (2) MySQL 服務所在的伺服器 (3) ISP/VM 供應商處檢查防火牆規則。更多資訊請參考 [https://devops.profitbricks.com/tutorials/install-mysql-on-centos-7/#firewall-rules 在 CentOS 7 上安裝 MySQL | ProfitBricks DevOps Central]
** 檢查您的 IP 地址是否包含在允許的 IP 地址列表中的防火牆規則。
** 透過使用 [https://whatismyipaddress.com/ 我的 IP 地址是什麼?] 服務檢查您的 IP 地址是否有更改。
* (可選) 監控防火牆活動。更多資訊請參考 [https://www.howtogeek.com/220204/how-to-track-firewall-activity-with-the-windows-firewall-log/ 如何使用 Windows 防火牆日誌追蹤防火牆活動] 在 {{Win}}

參考資料：
* [https://support.rackspace.com/how-to/mysql-connect-to-your-database-remotely/ 遠程連接到 MySQL 資料庫]
* [http://wiki.navicat.com/wiki/index.php/Error_2003 錯誤 2003 - Navicat Wiki]
* [http://faq.webyog.com/content/23/15/en/error-no-2003-can_t-connect.html SQLyog MySQL 管理員常見問答 - 錯誤號 2003: 無法連接...]

=== 錯誤 2013: 在查詢過程中與 MySQL 伺服器失去連接 ===
訊息：錯誤代碼：2013。在查詢過程中與 MySQL 伺服器失去連接

情況：在執行包含超過 1,000,000 行的下列查詢後，我遇到了 '錯誤代碼：2013。在查詢過程中與 MySQL 伺服器失去連接' 的錯誤訊息。
<pre>
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table`;
</pre>

解決方案：
* 在 [https://www.mysql.com/products/workbench/ MySQL Workbench] 上增加 (1) '''資料庫連接保持活躍間隔（秒）''' 和 (2) '''資料庫連接讀取超時（秒）''' 的設定 <ref>[http://stackoverflow.com/questions/16877574/how-can-i-execute-sql-queries-that-take-longer-99-999-seconds-on-mysql-workbench 如何在 MySQL Workbench 上執行超過 99,999 秒的 SQL 查詢 - Stack Overflow]</ref><ref>[http://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query 錯誤代碼：2013。在查詢過程中與 MySQL 伺服器失去連接 - Stack Overflow]</ref>。修改設定後請重新啟動 MySQL Workbench。例如，預設的 '''資料庫連接讀取超時（秒）''' 設定為 30 秒，你可以增加到 6000 秒（100 分鐘）。
* 減少行數以縮短執行時間 (1) 使用 {{kbd | key=LIMIT}} 子句 (2) 或分割查詢大小，例如 {{kbd | key=<nowiki>MOD(column, 2) = 0</nowiki>}} 和 {{kbd | key=<nowiki>MOD(column, 2) > 0</nowiki>}}，如果欄位是數字的話。
<pre>
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table` LIMIT 0, 10000;
</pre>

=== 從 MySQL 資料轉移至 MSSQL ===
情況: 需要將 MySQL 資料庫中的資料同步或遷移至 Microsoft SQL Server。

解決方案:
* (手動方式) 匯出 MySQL 資料為 MSSQL 相容格式: {{kbd | key=<nowiki>mysqldump --compatible=mssql [database_name]</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html#option_mysqldump_compatible MySQL :: MySQL 8.4 Reference Manual :: 6.5.4 mysqldump — A Database Backup Program]</ref>
* (自動方式) 使用 [https://learn.microsoft.com/en-us/sql/ssma/sql-server-migration-assistant?view=sql-server-ver17 Microsoft SQL Server Migration Assistant]、''$'' [https://www.webyog.com/product/sqlyog SQLyog] 工具 (適用於 {{Win}})。設定特定查詢來控制要從 MySQL 轉移至 Microsoft SQL Server 的資料。

常見挑戰:
* 大型資料集的連線逾時問題: 參考 2013 錯誤的故障排除方法，例如延長逾時參數或實施批次處理。
* 資料類型不相容: 在遷移前驗證兩系統間的類型對應，以防止資料損壞或遺失。

== 資源和環境錯誤 ==
=== 無法存取 MySQL 日誌 ===
版本：XAMPP 5.6.15-1，運行於{{Mac}}

原因：
* 錯誤日誌只能由名為 {{kbd | key=mysql}} 的用戶訪問<ref>打開位於以下位置的 MySQL 設定檔案：/Applications/XAMPP/xamppfiles/etc/my.cnf
<pre>
# MySQL 伺服器
[mysqld]
user = mysql
</pre></ref>

解決方案 <ref>[http://computerplumber.com/2009/01/using-the-chmod-command-effectively/ 有效使用 CHMOD 命令 @ Computer Plumber]</ref>：
<pre>
找到 MySQL 日誌的路徑
$ ls /Applications/XAMPP/xamppfiles/var/mysql/*.local.err

找出 MySQL 日誌的檔案名稱，例如：XXXMacBook-Pro.local.err

設置日誌的權限
$ sudo chmod 774 /Applications/XAMPP/xamppfiles/var/mysql/XXXMacBook-Pro.local.err
</pre>

=== 錯誤代碼: 11 資源暫時無法取得 ===
錯誤訊息：'''Can't create a new thread (errno 11 "Resource temporarily unavailable")'''

解決方案：[https://errerrors.blogspot.com/2026/06/xampp-mariadb-errno-11-open-files-limit.html 解決 XAMPP MariaDB 遇到 errno 11「Resource temporarily unavailable」錯誤]

=== 錯誤代碼: 13 拒絕存取 ===
當我嘗試啟動服務，執行 {{kbd | key=<nowiki>mysqld.exe --datadir=..\data --console</nowiki>}} 後出現此訊息。（MySQL版本：5.5.5-10.0.12-MariaDB 在 {{Win}}）
<pre>
> mysqld.exe --datadir=..\data --console
180430 10:33:38 [錯誤] mysqld.exe: 找不到檔案 'C:\MariaDB_10.0\bin\..\data\aria_log_control' (錯誤代碼: 13 "拒絕存取")
180430 10:33:38 [錯誤] mysqld.exe: 嘗試使用 aria 控制檔案 'C:\MariaDB_10.0\bin\..\data\aria_log_control' 時出錯 '無法打開檔案'
180430 10:33:38 [錯誤] 插件 'Aria' 初始化函數返回錯誤。
180430 10:33:38 [錯誤] 插件 'Aria' 註冊為儲存引擎失敗。
180430 10:33:38 [注意] InnoDB: 使用互斥鎖來計數緩衝池頁面
180430 10:33:38 [注意] InnoDB: InnoDB 記憶體堆疊已停用
180430 10:33:38 [注意] InnoDB: 互斥鎖和 rw_locks 使用 Windows 交錯函數
180430 10:33:38 [注意] InnoDB: 壓縮表使用 zlib 1.2.3
180430 10:33:38 [注意] InnoDB: 未使用 CPU crc32 指令
180430 10:33:38 [注意] InnoDB: 初始化緩衝池，大小 = 4.0G
180430 10:33:38 [注意] InnoDB: 完成緩衝池初始化
180430 10:33:38 [錯誤] InnoDB: 無法以讀寫模式打開 .\ibdata1
180430 10:33:38 [錯誤] InnoDB: 系統表空間必須可寫！
180430 10:33:38 [錯誤] 插件 'InnoDB' 初始化函數返回錯誤。
180430 10:33:38 [錯誤] 插件 'InnoDB' 註冊為儲存引擎失敗。
180430 10:33:38 [注意] 插件 'FEEDBACK' 已停用。
180430 10:33:38 [錯誤] 未知/不支持的儲存引擎: innodb
180430 10:33:38 [錯誤] 終止

180430 10:33:38 [注意] mysqld.exe: 關機完成

</pre>

解決方案：

以管理員特權開啟命令行。（如何操作: [https://www.howtogeek.com/194041/how-to-open-the-command-prompt-as-administrator-in-windows-8.1/ 如何在 Windows 8 或 10 中以管理員身份開啟命令提示符]）

=== 錯誤！：SQLSTATE[HY000]：一般錯誤：3 寫入檔案 'xxx\Temp\xxx.tmp' 時出錯（錯誤代碼：28 - 設備上沒有剩餘空間） ===

錯誤訊息範例：錯誤！：SQLSTATE[HY000]：一般錯誤：3 寫入檔案 'C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\MY2713.tmp' 時出錯（錯誤代碼：28 - 設備上沒有剩餘空間）

條件：檢查 mysql {{kbd | key=tmpdir}} 文件夾的磁碟可用空間

解決方案：增加 mysql {{kbd | key=tmpdir}} 文件夾的可用空間。或者使用另一個硬碟更改 mysql {{kbd | key=tmpdir}} 文件夾，該驅動器應具有更多的可用空間<ref>[https://stackoverflow.com/questions/11990887/changing-the-tmp-folder-of-mysql 更改 mysql 的 tmp 文件夾 - Stack Overflow]</ref>。
* 檢查當前的 mysql {{kbd | key=tmpdir}} 文件夾。查詢語法 {{kbd | key=<nowiki>SHOW VARIABLES LIKE 'tmpdir';</nowiki>}}。
** 在 {{Win}} 上，預設的臨時文件夾<ref>[https://answers.microsoft.com/en-us/windows/forum/windows_7-windows_programs/where-is-the-temporary-folder 臨時文件夾在哪裡？- Microsoft Community]</ref> 是 {{kbd | key=<nowiki>%TMP%</nowiki>}} = {{kbd | key=<nowiki>%USERPROFILE%\AppData\Local\Temp</nowiki>}}。
** 在 {{Linux}} 上，預設的臨時文件夾可能是 {{kbd | key=<nowiki>/tmp</nowiki>}}
* 編輯 [https://dev.mysql.com/doc/refman/5.7/en/option-files.html mysql 配置文件]
<pre>
[mysqld]
tmpdir=X:/temp
</pre>
* 重啟 MySQL 服務

驗證
* 查詢語法 {{kbd | key=<nowiki>SHOW VARIABLES LIKE 'tmpdir';</nowiki>}} 來驗證 mysql 配置文件的修改。

=== mysqldump: 寫入時遇到 errno 32 錯誤 ===
錯誤情況
<pre>
$ mysqldump -h localhost -u root -p --force --single-transaction --default-character-set=utf8 --quick mytable | pv | gzip -c > mytable.sql.gz
</pre>

遇到錯誤訊息：
<pre>
-bash: pv: 命令找不到
...
mysqldump: 寫入時遇到 errno 32 錯誤
</pre>

根本原因
* 未安裝 pv
* 輸入指令檢查 pv 是否安裝 {{kbd | key=which pv}}
* 輸入指令安裝 pv：{{kbd | key=sudo yum -y install pv}} <ref>[https://installati.one/centos/7/pv/ 如何在 CentOS 7 上安裝 pv | Installati.one]</ref>

參考資料
* [https://mysqldump.guru/mysqldump-got-errno-32-on-write.html mysqldump: 寫入時遇到 errno 32 錯誤 | mysqldump.guru]

=== 錯誤 1205: 鎖定等待超時，嘗試重新啟動事務 ===
解決方案：<ref>[https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im mysql - 獲得 "鎖定等待超時，嘗試重新啟動事務" 即使我沒有使用事務 - Stack Overflow]</ref>
# {{kbd | key=<nowiki>SHOW OPEN TABLES WHERE in_use > 0;</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/show-open-tables.html MySQL :: MySQL 5.7 參考手冊 :: 13.7.5.24 SHOW OPEN TABLES 語法]</ref>
# {{kbd | key=<nowiki>SHOW [FULL] PROCESSLIST;</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html MySQL :: MySQL 5.7 參考手冊 :: 13.7.5.29 SHOW PROCESSLIST 語法]</ref>
# {{kbd | key=<nowiki>KILL <process id>;</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/kill.html MySQL :: MySQL 5.7 參考手冊 :: 13.7.6.4 KILL 語法]</ref>

相關文章
* [https://errerrors.blogspot.com/2022/10/how-to-fix-lock-wait-timeout-exceeded-try-restarting-transaction-on-mysql.html 解決 MySQL 錯誤 1205: 鎖定等待超時，嘗試重新啟動事務]

=== 錯誤 1206: 鎖的總數量超過鎖表的大小 ===
訊息：錯誤代碼：1206。鎖的總數量超過鎖表的大小

當前狀態<ref>[https://dev.mysql.com/doc/refman/8.0/en/show-variables.html MySQL :: MySQL 8.0 參考手冊 :: 13.7.6.39 SHOW VARIABLES 語法]</ref>：
* Keywin {{kbd | key = <nowiki>SHOW VARIABLES LIKE 'innodb_buffer_pool_size';</nowiki>}}。如果返回 {{kbd | key=8388608}}，意味著 {{kbd | key=8388608}} 位元組 ≅ 8MB。

解決方案：
* 增加 {{kbd | key = innodb_buffer_pool_size}} 例如 {{kbd | key =<nowiki>SET GLOBAL innodb_buffer_pool_size=402653184;</nowiki>}}（402653184 位元組 ~ 400MB。預設值是 8MB。）
* 減少查詢數據的大小

進一步閱讀：
* [http://stackoverflow.com/questions/5696857/how-to-change-value-for-innodb-buffer-pool-size-in-mysql-on-mac-os innodb - 如何在 Mac OS 上改變 MySQL 的 innodb_buffer_pool_size 的值？ - Stack Overflow]。
* [https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html MySQL :: MySQL 5.7 參考手冊 :: 14.6.3.2 配置 InnoDB 緩衝池大小]

=== PHP 嚴重錯誤：在使用 adminer 導入 SQL.GZ 文件時，允許的記憶體大小耗盡 ===

解決方案：使用基本的 mysqldump 命令來生成備份文件。然後使用 mysql 命令導入備份。

=== 資料庫所在硬碟空間不足的狀況 ===

[http://errerrors.blogspot.com/2016/07/mysql.html Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況]

== 其他工具特定錯誤 ==
=== Navicat 錯誤：[Exp] OLE 錯誤 800A03EC ===
錯誤訊息：
<pre>
[Msg] [Exp] 導出到 - test.xlsx
[Err] [Row1048576] [Exp] OLE 錯誤 800A03EC
[Err] [Row1048577] [Exp] OLE 錯誤 800A03EC
[Err] [Row1048578] [Exp] OLE 錯誤 800A03EC
[Err] [Row1048579] [Exp] OLE 錯誤 800A03EC
</pre>

原因：
* 結果的行數超過了 [[Microsoft Excel]] 工作表大小的限制：1,048,576 行

解決方案：
* 限制 MySQL 查詢的行數

=== Navicat error: Field doesn't have a default value ===
錯誤：
<pre>
[ERR] 1364 - Field 'source' doesn't have a default value
</pre>

解決方案：
* 從 Excel 匯入到資料庫時，source 欄位是空值或者沒有指定，需要指定 source 欄位值。或者修改欄位定義，允許欄位值可以 NULL。

=== Navicat 錯誤: 導入數據筆數不一致 ===
錯誤：

使用 Navicat 將 Excel 文件導入 MySQL 資料庫時，出現總記錄筆數不一致的問題。解決方案：刪除空白行並檢查任何重複的主鍵數據。

解決方案：
* [https://errerrors.blogspot.com/2023/11/blog-post.html 解決 NaviCat 匯入 Excel 檔案到資料庫，出現資料總筆數不一致的問題] (written in Mandarin)

=== 如何解決 “zsh: operation not permitted: xxx.sql” ===
錯誤情況
<pre>
/applications/MAMP/library/bin/mysql -u [USERNAME] -p [DATABASE_NAME] < [PATH_TO_SQL_FILE]

zsh: operation not permitted: PATH_TO_SQL_FILE.sql
</pre>

解決方案：檢查安全與隱私設定：macOS 可能因安全設定而阻止終端機執行某些操作。你可以嘗試允許終端機完整磁碟存取：

* 開啟系統偏好設定。
* 轉到安全與隱私。
* 選擇隱私權標籤。
* 捲動至完整磁碟存取。
* 點擊鎖定圖示以進行更改（可能需要你的管理員密碼）。
* 尋找終端機應用程式或 iTerm2 或你正在使用的任何終端機，勾選允許完整磁碟存取的方框。
* 重啟你的終端機。

== 延伸閱讀 ==
* 如果找不到想要的問題答案，可以前往stackoverflow 尋找 [https://stackoverflow.com/questions/tagged/mysql Unanswered 'mysql' Questions - Stack Overflow]

== References ==
<references/>

{{Template: Data factory flow in Mandarin}}

[[Category: MySQL]]
[[Category: Database]]
[[Category: Data Science]]
[[Category: Revised with LLMs]]

Troubleshooting of MySQL errors

2026-06-19T15:26:39Z

Planetoid: /* errno 41 - Error dropping database */

Troubleshooting of MySQL errors

{{LanguageSwitcher | content = [[Troubleshooting of MySQL errors | English]], [[排除 MySQL 技術疑難問題|漢字]]}}

In this document, we explore various common issues encountered while working with MySQL, categorizing them into seven distinct groups based on their nature and impact. These categories help in identifying the underlying problems and facilitate efficient troubleshooting. The issues are classified as follows:

# '''Installation and Configuration Issues''': Problems related to setting up and configuring MySQL.
# '''Database and Table Operation Errors''': Issues during operations such as creating, dropping, or accessing databases and tables.
# '''Access and Authentication Errors''': Errors dealing with user permissions and authentication.
# '''SQL and Query Errors''': Issues related to the syntax and execution of SQL queries.
# '''Connection and Network Errors''': Problems involving connectivity between clients and the MySQL server.
# '''Resource and Environmental Errors''': Errors caused by system resources like disk space, memory limits, or file system permissions.
# '''Miscellaneous Tool-Specific Errors''': Errors specific to database tools like Navicat or issues from scripting environments like PHP.

Each category not only delineates common errors but also aids in the direct application of specific solutions tailored to each type of issue.

== Installation and Configuration Issues ==
=== command not found: mysqldump ===
# locate the mysqldump command
#* Key-in the command {{kbd | key=<nowiki>sudo find / -iname mysqldump</nowiki>}} for {{Mac}} or {{Linux}}
#* If you installed
#** [https://www.apachefriends.org/index.html XAMPP] for {{Mac}}: Entire path is {{kbd | key=<nowiki>/Applications/XAMPP/xamppfiles/bin/mysqldump</nowiki>}}
#** [https://www.apachefriends.org/index.html XAMPP] on X disk for {{Win}}: Entire path is {{kbd | key=<nowiki>X:\xampp\mysql\bin\mysqldump.exe</nowiki>}}
#** [https://www.mamp.info/en/ MAMP & ''$'' MAMP PRO] for {{Mac}}: Entire path is {{kbd | key=<nowiki>/Applications/MAMP/Library/bin/mysqldump</nowiki>}}
# input the complete path of mysqldump command
#* old command which caused error {{kbd | key=<nowiki>mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz</nowiki>}}
#* new command {{kbd | key=<nowiki>/Applications/XAMPP/xamppfiles/bin/mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz</nowiki>}}

=== Could not start MySQL service on Windows ===
checklist
# [https://dev.mysql.com/doc/refman/5.7/en/data-directory.html MySQL Data Directory] was allowed to written by {{kbd | key=NETWORK SERVICE}} on Windows Server 2008<ref>[https://errerrors.blogspot.com/2016/12/mysql-windows-server-2008-r2.html 如何修改 MySQL 伺服器的資料目錄 (Windows Server 2008 R2)]</ref>
# Directory {{kbd | key=secure-file-priv}} was allowed to written by {{kbd | key=NETWORK SERVICE}} on Windows Server 2008

=== mysqldump: Got errno 32 on write ===
Error condition
<pre>
$ mysqldump -h localhost -u root -p --force --single-transaction --default-character-set=utf8 --quick mytable | pv | gzip -c > mytable.sql.gz
</pre>

met the error message:
<pre>
-bash: pv: command not found
...
mysqldump: Got errno 32 on write
</pre>

Root cause
* The pv was not installed
* Input the command to check if pv was installed {{kbd | key=which pv}}
* install pv by input the command: {{kbd | key=sudo yum -y install pv}} <ref>[https://installati.one/centos/7/pv/ How To Install pv on CentOS 7 | Installati.one]</ref>

References
* [https://mysqldump.guru/mysqldump-got-errno-32-on-write.html mysqldump: Got errno 32 on write | mysqldump.guru]

== Database and Table Operation Errors ==

=== ERROR 1005 (HY000) at line xx: Can't create table 'TABLE_NAME' (errno: 28) ===

Solutions
* Check if the disk space where mysql data folder located is enough. e.g. Input {{kbd | key=df -h}} on {{Linux}}
* More on [http://stackoverflow.com/questions/11045279/error-1005-hy000-cant-create-table-errno-150 mysql - ERROR 1005 (HY000): Can't create table (errno: 150) - Stack Overflow].

=== ERROR 1006 (HY000): Can't create database 'DATABASE_NAME' (errno: 28) ===
Solutions
* Check if the disk space where mysql data folder located is enough. e.g. Input {{kbd | key=df -h}} on {{Linux}}
* More on [http://stackoverflow.com/questions/18719748/error-1006-hy000-cant-create-database-errno-13-mysql-5-6-12 ERROR 1006 (HY000) Can't create database (errno: 13) MySQL 5.6.12 - Stack Overflow].

=== ERROR 1010: Error dropping database (can't rmdir) ===
Message: <pre>Error dropping database (can't rmdir '.\<db_name>\', errno: 17)</pre>

Solution
* Manually delete the leftover non-MySQL files (e.g. .sql files) inside the database directory, then retry DROP DATABASE — or simply delete the entire folder manually.

Root Cause
* MySQL's DROP DATABASE only removes files it manages. Foreign files left in the data directory (such as .sql dumps) prevent rmdir from removing the folder, causing errno 17 (ENOTEMPTY).

=== ERROR 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument) ===
Message: [Err] 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument)

Solutions
* Check the existence of file DATABASE\TABLE.frm. If not, you may need to create the TABLE before executed the MySQL query.
* Check the permission of file DATABASE\TABLE.frm or folder which the file located<ref>[http://stackoverflow.com/questions/12106727/mysql-copying-tables-files-gives-rise-to-error-1017-hy000-cant-find-file MySQL, copying tables files gives rise to "ERROR 1017 (HY000): Can't find file:" even though its there there - Stack Overflow]</ref><ref>[https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/2762-error-1017-hy000-can-t-find-file ERROR 1017 (HY000): Can't find file - Percona Community]</ref>. ''unverified''

=== Navicat error: Field doesn't have a default value ===
<pre>
[ERR] 1364 - Field 'source' doesn't have a default value
</pre>

Solution:
* When importing from Excel to a database, if the 'source' field is empty or not specified, it's necessary to assign a value to the 'source' field. Alternatively, modify the field definition to allow the field value to be NULL.

=== ERROR 1813: Tablespace for table xxx exists ===
Message: ERROR 1813 Tablespace for table xxx exists.

Solution
* [https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp mysql - Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT - Stack Overflow]

=== ERROR 1690 - BIGINT UNSIGNED value is out of range ===
Message: MySQL error #1690 (BIGINT UNSIGNED value is out of range)

Solution <ref>[https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html MySQL :: MySQL 8.0 Reference Manual :: 11.2.6 Out-of-Range and Overflow Handling]</ref><ref>[https://stackoverflow.com/questions/34115917/mysql-error-1690-bigint-unsigned-value-is-out-of-range-for-unix-timestamp MySQL error #1690 (BIGINT UNSIGNED value is out of range) for UNIX_TIMESTAMP() - Stack Overflow]</ref>:
<pre>
SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
</pre>

=== ERROR 1305 - FUNCTION MY_TABLE.MY_FUNCTION does not exist ===
Message: MySQL error 1305 - FUNCTION MY_TABLE.MY_FUNCTION does not exist

Solution: Fix the typo in the function name

=== ERROR 1170: BLOB/TEXT column 'url' used in key specification without a key length ===
Condition
* SQL syntax when tried to create the new table

<pre>
DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`url` text COLLATE utf8mb4_unicode_ci NOT NULL,
`status` tinyint(2) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY (`job_id`),
UNIQUE KEY `url` (`url`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
</pre>

Solution: BLOB/TEXT column 'url' used in key specification with a key length e.g. {{kbd | key=`url`(500)}}
<pre>
DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`url` text COLLATE utf8mb4_unicode_ci NOT NULL,
`status` tinyint(2) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`(500))
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
</pre>

== Access and Authentication Errors ==

=== [Warning] Using a password on the command line interface can be insecure ===
Problem Description:
When exporting or importing MySQL databases via bash commands, you may encounter the following warning:

<pre>
mysql: [Warning] Using a password on the command line interface can be insecure.

or

mysqldump: [Warning] Using a password on the command line interface can be insecure.
</pre>

This typically occurs when using commands like:

<pre>
mysqldump -u USERNAME -p -h 127.0.0.1 --default-character-set=utf8 DATABASE > DATABASE.sql
</pre>

Solution: To resolve this warning and improve security, follow these steps:
# Set up a login path using [https://dev.mysql.com/doc/refman/8.4/en/mysql-config-editor.html mysql_config_editor]: {{kbd | key=<nowiki>mysql_config_editor set --login-path=local --host=localhost --user=root --password</nowiki>}}
# Use the login path when exporting or importing MySQL databases (1) For exporting: {{kbd | key=<nowiki>mysqldump --login-path=local --default-character-set=utf8 DATABASE > DATABASE.sql</nowiki>}} (2) For importing: {{kbd | key=<nowiki>pv DATABASE.sql.gz | gunzip | mysql --login-path=local --default_character_set utf8 --force DATABASE</nowiki>}}
# To view all configured login paths {{kbd | key=<nowiki>mysql_config_editor print --all</nowiki>}}

By using a login path, you avoid exposing your password in the command line, thus resolving the security warning.

=== ERROR 1044 (42000): Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME' ===
Message: {{kbd | key=<nowiki>ERROR 1044 (42000): Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME'</nowiki>}}
<pre>
pv file.sql.gz | gunzip | mysql -u USER -p --host=127.0.0.1 --default_character_set utf8 DATABASE_NAME
</pre>

Solution:
* Check the permission of specified user name & database name
<pre>
$ mysql -u USER -p --host=127.0.0.1
Enter password:

mysql> use DATABASE_NAME
</pre>

Message: {{kbd | key=<nowiki>mysqldump: Got error: 1044: Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME' when doing LOCK TABLES</nowiki>}}
<pre>
$ mysqldump -h 127.0.0.1 -u USER -p DATABASE_NAME TABLE_NAME > TABLE_NAME.sql
</pre>

Solution:
* Add the mysqldump option {{kbd | key=<nowiki>--skip-lock-tables</nowiki>}} if you cannot grant the user permissions <ref>[https://stackoverflow.com/questions/7415698/skip-lock-tables-and-mysqldump mysql - skip-lock-tables and mysqldump - Stack Overflow]</ref>.
<pre>
$ mysqldump -h 127.0.0.1 -u USER -p --skip-lock-tables DATABASE_NAME TABLE_NAME > TABLE_NAME.sql
</pre>

=== Error!: SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied ===
Message: Error!: {{kbd | key=<nowiki>SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'user'@'localhost' (using password: YES)</nowiki>}}

Solution:
* If you executed the query{{kbd | key=INTO OUTFILE}}, you need to grant the file permission e.g. {{kbd | key=GRANT FILE ON *.* TO 'user'@'localhost';}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_file MySQL :: MySQL 5.7 Reference Manual :: 6.2.1 Privileges Provided by MySQL]</ref><ref>[https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold MYSQL into outfile "access denied" - but my user has "ALL" access.. and the folder is CHMOD 777 - Stack Overflow]</ref>.

Related issue: "Error!: SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"<ref>[https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql database - How should I tackle --secure-file-priv in MySQL? - Stack Overflow]</ref>

=== ERROR 1049 (42000): Unknown database ===
Message: ERROR 1049 (42000): Unknown database 'MY_DATABASE_p'

Solution:
* Check the database 'MY_DATABASE_p' is exists
* Check there are no {{kbd | key=TAB}} character after database name if you want to connect the database 'MY_DATABASE' when you are using console.

=== ERROR 1045 (28000): Access denied for user ===
Message: ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Solution:
* Check the typo of user name.
* Check the typo of password.
* If you are using the console command, escape the password if it contains special characters e.g. {{kbd | key=<nowiki>mysql -u root -p'PASSWORD'</nowiki>}}<ref>[http://superuser.com/questions/123928/escaping-a-password-using-mysqldump-console escape characters - Escaping a password using mysqldump console - Super User]</ref>
* You may need to delete the existing account setting and re-config again.

=== ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function. ===
Wrong sql query as follows:
<pre>
CREATE USER 'test'@'localhost' IDENTIFIED BY PASSWORD 'my_password';
</pre>

Solution:
(1) Check if the account was created or not
<pre>
SELECT User,Host FROM mysql.user;
</pre>

(2a) If the account was created, set the password for the account.
<pre>
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('my_password');
</pre>

(2b) If the account was NOT created, re-create the account.
<pre>
CREATE USER 'test'@'localhost' IDENTIFIED BY 'my_password';
</pre>

references
* [http://dev.mysql.com/doc/refman/5.7/en/create-user.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.2 CREATE USER Syntax]
* [http://dev.mysql.com/doc/refman/5.7/en/set-password.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.7 SET PASSWORD Syntax]
* [http://dev.mysql.com/doc/refman/5.7/en/drop-user.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.3 DROP USER Syntax]

== SQL and Query Errors ==

=== Caught exception: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined ===
Solutions
* Number of question marks is not matched with the number of query values <ref>[https://stackoverflow.com/questions/10966251/sqlstatehy093-invalid-parameter-number-parameter-was-not-defined php Invalid parameter number: parameter was not defined - Stack Overflow]</ref>
* The array of query values should not be the associative array. Use sequential array!

=== ERROR 1054 - Unknown column in 'where clause' ===
Message: [Err] 1054 - Unknown column 'xxx' in 'where clause'

Solution:
# check the column name 'xxx' if exists
# if the column name 'xxx' was computed by the [http://dev.mysql.com/doc/refman/5.7/en/example-user-variables.html User-Defined Variables]. Enclosed the whole query into another parent derived query.
<pre>
-- The query which met the error message: [Err] 1054 - Unknown column 'rank' in 'where clause'
SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`
FROM
(
...
) semi
WHERE semi.rank <= 10
</pre>

Enclosed the whole query into another parent derived query.
<pre>
SELECT final.*
FROM
(
SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`
FROM
(
...
) semi
) final
WHERE final.rank <= 10
</pre>

=== ERROR 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ===
Message
<pre>
Query Error (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.posts.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
</pre>

Query with Error
<pre>
-- Create sample table
CREATE TABLE posts (
id INT,
post_id VARCHAR(10),
author VARCHAR(50),
content TEXT,
likes INT,
post_time DATETIME
);

-- Insert sample data
INSERT INTO posts VALUES
(1, 'A123', 'Alice', 'First post', 10, '2024-01-01'),
(2, 'A123', 'Bob', 'Great!', 5, '2024-01-02'),
(3, 'A123', 'Bob', 'Nice!', 3, '2024-01-03');

-- This will cause an error
SELECT
id, -- Problem: Not in GROUP BY
post_id,
author,
GROUP_CONCAT(content SEPARATOR "\n") AS content, -- This is OK as it uses aggregate function
likes -- Problem: Not in GROUP BY
FROM posts
GROUP BY post_id, author;
</pre>

Solution: Fix the query by either (1) including all columns in GROUP BY or (2) using aggregate functions (like MAX, MIN, SUM, COUNT, GROUP_CONCAT)
<pre>
-- Method 1: disable `sql_mode=only_full_group_by`
```
There are two ways to disable `sql_mode=only_full_group_by`:

1. Temporary Change (Only affects current session)
```sql
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
```

2. Permanent Change (Modify MySQL configuration file)
- Locate MySQL configuration file (usually `my.cnf` or `my.ini`)
- Add or modify in the `[mysqld]` section:
```ini
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
```

Important Notes:
1. After modifying the configuration file, MySQL service needs to be restarted to take effect
2. Disabling this setting is not recommended because:
- May lead to unpredictable query results
- Violates SQL standards
- May cause compatibility issues in future versions

The recommended approach is to fix the SQL query rather than changing the SQL mode.
```

-- Method 2: Add all columns to GROUP BY
SELECT
id,
post_id,
author,
content,
likes
FROM posts
GROUP BY post_id, author, id, content, likes;

-- Method 3: Use aggregate functions for non-GROUP BY columns
SELECT
MAX(id) as id,
post_id,
author,
GROUP_CONCAT(content) as contents,
SUM(likes) as total_likes
FROM posts
GROUP BY post_id, author;
</pre>

=== ERROR 1052 - Column 'column_name' in field list is ambiguous ===
Message: Error Code: 1052. Column 'column_name' in field list is ambiguous

Cause: Since 'column_name' is present in 2 or more tables ...<ref>[https://stackoverflow.com/questions/19351633/error-code-1052-column-admin-id-in-field-list-is-ambiguous mysql - Error Code: 1052 Column 'admin_id' in field list is ambiguous - Stack Overflow]</ref>

Solution: Remain only one table name 'column_name' OR adding the table name alias.

=== Error!: SQLSTATE[42000]: Syntax error or access violation ===
Message: {{kbd | key=<nowiki>Error!: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...</nowiki>}}

Solution:
* [[SQL syntax debug]]
* [https://www.eversql.com/sql-syntax-check-validator/ SQL Syntax Check Online, SQL Validator, Instant SQL Compiler Online – EverSQL] {{exclaim}} Not support the [https://www.php.net/manual/en/pdo.prepare.php PHP: PDO::prepare] which the query syntax contains question marks.

=== ERROR 1366: Incorrect string value ===
Message: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x87\xAF\xF0\x9F...' for column 'XXX'

Solution:
* Check the charset of PHP PDO. AND execute {{kbd | key=set names utf8mb4}}<ref>[https://stackoverflow.com/questions/54947392/incorrect-string-value-when-trying-to-pass-emoji-to-the-db-encoded-with-utf8mb4 php - Incorrect string value when trying to pass emoji to the db encoded with utf8mb4 - Stack Overflow]</ref>
* Check the configuration of table
** CHARACTER SETS (aka 字元集、字符集): {{kbd | key=utf8mb4}}
** COLLATION (aka 定序、字元序): {{kbd | key=utf8mb4_unicode_ci}}

=== ERROR 1070 (42000): Specified key was too long; max key length is 767 bytes ===
'''Envoronment''': MySQL 5.6

'''Root cause''': "By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Statement”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format." <ref>[https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html MySQL :: MySQL 5.6 Reference Manual :: 14.22 InnoDB Limits]</ref>

'''Solution'''<ref>[https://www.opencli.com/mysql/mysql-%E5%95%8F%E9%A1%8C-1071-42000-specified-key-was-too-long MySQL 問題: 1071 (42000): Specified key was too long]</ref><ref>[https://terrytongcc.com/%E5%A6%82%E4%BD%95%E8%A7%A3%E6%B1%BAmysql%E5%87%BA%E7%8F%BEerror-1118-row-size-too-large-8126%E5%95%8F%E9%A1%8C/ 如何解決MySQL出現Error 1118: row size too large (> 8126)問題 | Terry Tong – Full Stack Web Developer]</ref>:
* execute SQL query as the following:

<pre>
SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_format_max = Barracuda;
</pre>

* modify the MySQL configuration file
<pre>
innodb_file_per_table = 1
innodb-file-format = BARRACUDA
innodb-large-prefix = ON
innodb_file_format_max = BARRACUDA
</pre>

* restart the MySQL server

* execute SQL query as the following:
<pre>
ALTER TABLE `table_name` ROW_FORMAT=COMPRESSED;

</pre>

=== ERROR 1267: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat' ===

Error condition: Tried to concat different type of data e.g. CONCAT(string, int)

Solution: {{kbd | key =<nowiki>SELECT CONCAT(`string_column`, CONVERT(`int_column`, CHAR))</nowiki>}} or {{kbd | key =<nowiki>SELECT CONCAT(`string_column`, CAST(`int_column` AS CHAR))</nowiki>}}<ref>[https://stackoverflow.com/questions/15368753/cast-int-to-varchar mysql - Cast int to varchar - Stack Overflow]</ref><ref>[http://blog.51cto.com/bian5399/1092772 mysql字符集问题：Illegal mix of collations-每天让自己进步一点-51CTO博客]</ref>

=== ERROR 1235: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' ===
Message: ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'.

Solution: Change the syntax of subquery to {{kbd | key =column_name BETWEEN start_number to end_number}}

Further reading:
* [https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.6/en/subquery-restrictions.html MySQL :: MySQL Restrictions and Limitations :: 4 Restrictions on Subqueries]

=== Error: MySQL Incorrect datetime value: '0000-00-00 00:00:00' ===

Workaround solution<ref>[https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000 MySQL Incorrect datetime value: '0000-00-00 00:00:00' - Stack Overflow]</ref>:
<pre>
SET SQL_MODE='ALLOW_INVALID_DATES';
</pre>

=== Optimization Solutions for Query Performance ===

Originally, product_id (VARCHAR, 200) was used as the primary key. When the data volume reached approximately 600,000, data processing became very slow. Adding a field id (int) as the primary key can solve the problem of slow query speed.

== Connection and Network Errors ==

=== Error: SQLSTATE[HY000] [2002] No such file or directory ===
Error message: SQLSTATE[HY000] [2002] No such file or directory

Solutions:
* Modify the host of database from {{kbd | key=localhost}} to {{kbd | key=127.0.0.1}}

=== ERROR 2013: Lost connection to MySQL server during query ===
Message: Error Code: 2013. Lost connection to MySQL server during query

Condition: After executed the following query contains number of rows which exceed 1,000,000 rows, I met the error message 'Error Code: 2013. Lost connection to MySQL server during query'.
<pre>
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table`;
</pre>

Solution:
* Increase the settings of (1) '''DBMS connection keep-alive interval (in seconds)''' & (2) '''DBMS connection read time out (in seconds)''' on [https://www.mysql.com/products/workbench/ MySQL Workbench] <ref>[http://stackoverflow.com/questions/16877574/how-can-i-execute-sql-queries-that-take-longer-99-999-seconds-on-mysql-workbench How can I execute SQL queries that take longer 99,999 seconds on MySQL Workbench? - Stack Overflow]</ref><ref>[http://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query Error Code: 2013. Lost connection to MySQL server during query - Stack Overflow]</ref>. And remember to restart the MySQL Workbench after the settings were modified. e.g. The default setting of '''DBMS connection read time out (in seconds)''' is 30 seconds, you may increase to 6000 seconds (100 minutes).
* Reduce the number of rows to reduce the execution time (1) by using {{kbd | key=LIMIT}} clause (2) or by splitting the query size e.g. {{kbd | key=<nowiki>MOD(column, 2) = 0</nowiki>}} & {{kbd | key=<nowiki>MOD(column, 2) > 0</nowiki>}} if the column is numeric.
<pre>
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table` LIMIT 0, 10000;
</pre>

=== ERROR 2003 (HY000): Can't connect to MySQL server on 'ip' ===
'''ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' '''

Solution:
* Check if the IP is alive

'''ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (111 "Connection refused")'''

Solution:
* Check if the MySQL service is running or not<ref>[https://www.cyberciti.biz/faq/how-to-find-out-if-mysql-is-running-on-linux/ HowTo: Find out If MySQL Is Running On Linux Or Not]</ref>. If not, start the MySQL service.
* Check the firewall rules

'''ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (116 "Connection timed out")'''

Solution:
* Check the configuration of MySQL
** comment out [https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_bind-address bind-address] = 127.0.0.1 or set to *
** comment out [https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_skip-networking skip-networking]
* Check the permission of MySQL database user.
* Check the firewall rules at (1) your personal computer (2) the server where MySQL service located (3) ISP/VM providers. More on [https://devops.profitbricks.com/tutorials/install-mysql-on-centos-7/#firewall-rules Install MySQL on CentOS 7 | ProfitBricks DevOps Central]
** Check if your IP address was included in the allowed IP address list of firewall rules.
** Check if your IP address was changed by using [https://whatismyipaddress.com/ What Is My IP Address?] services.
* (optional) Monitor the firewall activity. More on [https://www.howtogeek.com/220204/how-to-track-firewall-activity-with-the-windows-firewall-log/ How to Track Firewall Activity with the Windows Firewall Log] on {{Win}}

References:
* [https://support.rackspace.com/how-to/mysql-connect-to-your-database-remotely/ Connect to a MySQL database remotely]
* [http://wiki.navicat.com/wiki/index.php/Error_2003 Error 2003 - Navicat Wiki]
* [http://faq.webyog.com/content/23/15/en/error-no-2003-can_t-connect.html SQLyog MySQL Admin FAQ - Error no. 2003: Can't connect...]

=== ERROR 2002: SQLSTATE[HY000] [2002] Only one usage of each socket address (protocol/network address/port) is normally permitted ===

Message: (1) [2002] Only one usage of each socket address (protocol/network address/port) is normally permitted (2) "SQLSTATE[HY000] [2002] 一次只能用一個通訊端位址 (通訊協定/網路位址/連接埠)。" in Chinese

Solution: [https://stackoverflow.com/questions/10317974/mysql-php-error2002-only-one-usage-of-each-socket-address-protocol-network-a MySQL/PHP Error:(2002) Only one usage of each socket address (protocol/network address/port) is normally permitted - Stack Overflow]

=== ERROR 2002: Cannot connect: SQLSTATE[HY000] [2002] ===
Condition on Cygwin terminal of Windows:
<pre>
$ mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock' (2)

$ mysql -h localhost -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock' (2)
</pre>

Solution:
* Change {{kbd | key=<nowiki>-h localhost</nowiki>}} to {{kbd | key=<nowiki>-h 127.0.0.1</nowiki>}}
<pre>
$ mysql -h 127.0.0.1 -u root -p
</pre>
* If still not work, reboot the server and restart the MySQL service.

=== SQLSTATE[HY000] [2002] Can't assign requested address ===
Error message
<pre>
Caught PDOException: /path/to/script.php ERROR: Cannot connect: SQLSTATE[HY000] [2002] Can't assign requested address
utf8: SQLSTATE[HY000] [2002] Can't assign requested address

Caught PDOException: /path/to/script.php ERROR: Cannot connect: SQLSTATE[HY000] [2002] Operation timed out
utf8: SQLSTATE[HY000] [2002] Operation timed out
</pre>

Walkaround Solution:
* You can extend the interval between each MySQL save operation.

Explanation:
It typically occurs when your application is unable to establish a connection to the specified database server.

=== Transfer Data from MySQL to MSSQL ===
Condition: Need to synchronize or transfer data from MySQL database to Microsoft SQL Server.

Solution:
* (Manually method) Export MySQL data in a format compatible with Microsoft SQL Server: {{kbd | key=<nowiki>mysqldump --compatible=mssql [database_name]</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html#option_mysqldump_compatible MySQL :: MySQL 8.4 Reference Manual :: 6.5.4 mysqldump — A Database Backup Program]</ref>
* (Automated method) Use [https://learn.microsoft.com/en-us/sql/ssma/sql-server-migration-assistant?view=sql-server-ver17 Microsoft SQL Server Migration Assistant] or ''$'' [https://www.webyog.com/product/sqlyog SQLyog] tool (for {{Win}}). Use query commands to specify the data to be transferred from MS SQL server to MySQL server.

Example mysqldump command
<pre>
mysqldump -h 127.0.0.1 -u root -p \
--skip-lock-tables \
--single-transaction \
--default-character-set=utf8 \
--compatible=mssql \
--skip-extended-insert \
--no-auto-rehash \
--skip-comments \
database_name > database_name.sql
</pre>

Related issues:
* For large data volumes, connection timeout issues may occur. In such cases, refer to Error 2013 handling methods by increasing connection timeout settings or processing data in batches.
* The two database systems have differences in data types, so data compatibility should be verified before transfer.

== Resource and Environmental Errors ==
=== could not access the mysql log ===
Version: XAMPP 5.6.15-1 on {{Mac}}

Cause:
* the error log only be accessed by the user named {{kbd | key=mysql}}<ref>Open the MySQL configuration file located: /Applications/XAMPP/xamppfiles/etc/my.cnf
<pre>
# The MySQL server
[mysqld]
user = mysql
</pre></ref>

Solution <ref>[http://computerplumber.com/2009/01/using-the-chmod-command-effectively/ Using the CHMOD command effectively @ Computer Plumber]</ref>:
<pre>
find the path to the mysql log
$ ls /Applications/XAMPP/xamppfiles/var/mysql/*.local.err

find the file name of mysql log ex: XXXMacBook-Pro.local.err

set the permission of log
$ sudo chmod 774 /Applications/XAMPP/xamppfiles/var/mysql/XXXMacBook-Pro.local.err
</pre>

=== Could not open single-table tablespace file filename.ibd ===
Version: XAMPP 5.6.15-1 on {{Mac}}

Condition: The Mac was shutdown accidentally and the database was not shutdown normally. After reboot the Mac, unable to start the MySQL service<ref>[http://stackoverflow.com/questions/35184367/error-could-not-open-single-table-tablespace-file-scrapers-records-ibd mysql - Error: could not open single-table tablespace file .\scrapers\records.ibd - Stack Overflow]</ref><ref>[http://chepri.com/our-blog/mysql-innodb-corruption-and-recovery/ MySQL Won't Start - InnoDB Corruption and Recovery. - Chepri]</ref>.

Possible solution:
# Edit the MySQL configuration file located: /Applications/XAMPP/xamppfiles/etc/my.cnf
# Add this line: {{kbd | key=<nowiki>innodb_force_recovery = 1</nowiki>}}
# Try to start the MySQL service
# If the MySQL service started successfully, edit the MySQL configuration file and mark this line : {{kbd | key=<nowiki>#innodb_force_recovery = 1</nowiki>}}
# Restart the MySQL service

=== MySQL server has gone away ===
Error conditions:
* Enable the option {{kbd | key = log_error}} in MySQL config file e.g. {{kbd | key = <nowiki>log_error="file_name_of_error_log"</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/error-log.html MySQL :: MySQL 8.0 Reference Manual :: 5.4.2 The Error Log]</ref>.
* Example error log located at {{kbd | key = <nowiki>file_name_of_error_log</nowiki>}} are as following:

<pre>
2019-05-23T08:52:19.989876Z 99980 [Note] Aborted connection 99980 to db: 'DB_NAME' user: 'DB_USER' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)
</pre>

Solution:

* Increase the value of {{kbd | key = <nowiki>max_allowed_packet</nowiki>}}<ref>[https://stackoverflow.com/questions/5688403/how-to-check-and-set-max-allowed-packet-mysql-variable php - how to check and set max_allowed_packet mysql variable - Stack Overflow]</ref> if the MySQL user has the {{kbd | key = <nowiki>SUPER</nowiki>}} privilege<ref>[https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL]</ref>.

=== Error!: SQLSTATE[HY000]: General error ===
Message: Error!: SQLSTATE[HY000]: General error

Condition: When I used the [http://php.net/manual/en/book.pdo.php PHP: PDO]

Solutions:
* "You do not use {{kbd | key=<nowiki>$result = $stmt->fetchAll();</nowiki>}} with update or insert queries" <ref>[https://stackoverflow.com/questions/12979510/pdo-error-sqlstatehy000-general-error-when-updating-database php - PDO error: " SQLSTATE[HY000]: General error " When updating database - Stack Overflow]</ref>.
* You do not use {{kbd | key=<nowiki>$result = $stmt->fetchAll();</nowiki>}} in the query {{kbd | key=<nowiki>INTO OUTFILE ...</nowiki>}} <ref>[http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/ Save MySQL query results into a text or CSV file]</ref><ref>[https://stackoverflow.com/questions/13369164/cant-create-write-to-file-errcode-22 mysql - Can't create/write to file (Errcode: 22) - Stack Overflow]</ref>.

=== Error!: SQLSTATE[HY000]: General error: 3 Error writing file xxx.tmp (Errcode: 28 - No space left on device) ===

Example error message: Error!: SQLSTATE[HY000]: General error: 3 Error writing file 'C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\MY2713.tmp' (Errcode: 28 - No space left on device)

Condition: Check the disk free space of mysql {{kbd | key=tmpdir}} folder

Solutions: Increase the free space of mysql {{kbd | key=tmpdir}} folder. Or change the mysql {{kbd | key=tmpdir}} folder with another hard disk drive contains more free space<ref>[https://stackoverflow.com/questions/11990887/changing-the-tmp-folder-of-mysql Changing the tmp folder of mysql - Stack Overflow]</ref>.
* Check the current mysql {{kbd | key=tmpdir}} folder. Query the syntax {{kbd | key=<nowiki>SHOW VARIABLES LIKE 'tmpdir';</nowiki>}}.
** On {{Win}} the default temporary folder<ref>[https://answers.microsoft.com/en-us/windows/forum/windows_7-windows_programs/where-is-the-temporary-folder/44a039a5-45ba-48dd-84db-fd700e54fd56 Where is the Temporary folder? - Microsoft Community]</ref> is {{kbd | key=<nowiki>%TMP%</nowiki>}} = {{kbd | key=<nowiki>%USERPROFILE%\AppData\Local\Temp</nowiki>}}.
** On {{Linux}} the default temporary folder maybe is {{kbd | key=<nowiki>/tmp</nowiki>}}
* Edit the [https://dev.mysql.com/doc/refman/5.7/en/option-files.html mysql configuration file]
<pre>
[mysqld]
tmpdir=X:/temp
</pre>
* Restart the MySQL service

Validation
* Query the syntax {{kbd | key=<nowiki>SHOW VARIABLES LIKE 'tmpdir';</nowiki>}} to validate the modification of mysql configuration file.

=== errno 11 - Resource temporarily unavailable ===
Message: '''Can't create a new thread (errno 11 "Resource temporarily unavailable")'''

Solution: [https://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit How to permanently raise ulimit 'open files' and MySQL 'open_files_limit']

=== errno 41 - Error dropping database ===
Message: '''Error dropping database (can't rmdir '.\TABLE_NAME', errno: 41)''' occurred when I executed '''DROP DATABASE `TABLE_NAME`''';

Solution: [https://stackoverflow.com/questions/17947255/error-in-dropping-a-database-in-mysql-cant-rmdir-oro-errno-41/19888293 phpmyadmin - Error in dropping a database in MySQL (can't rmdir '.\oro', errno: 41) - Stack Overflow]

=== How to resolve "zsh: operation not permitted: xxx.sql" ===
Error condition
<pre>
/applications/MAMP/library/bin/mysql -u [USERNAME] -p [DATABASE_NAME] < [PATH_TO_SQL_FILE]

zsh: operation not permitted: PATH_TO_SQL_FILE.sql
</pre>

Solution: Check Security & Privacy Settings: macOS may block certain actions from the terminal due to security settings. You can try allowing full disk access to the terminal:

* Open System Preferences.
* Go to Security & Privacy.
* Select the Privacy tab.
* Scroll down to Full Disk Access.
* Click the lock icon to make changes (you might need your administrator password).
* Find the Terminal app or iTerm2 or whatever terminal you are using and check the box to allow full disk access.
* Restart your terminal.

=== Resolve insufficient hard disk space where mysql data located ===
[http://errerrors.blogspot.com/2016/07/mysql.html Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況]

=== PHP Fatal Error: Allowed Memory Size Exhausted when import SQL.GZ file using adminer ===

solution: Using the naive mysqldump command to generate the backup file. And import the backup by using mysql command.

=== ERROR 1206: The total number of locks exceeds the lock table size ===
Message: Error Code: 1206. The total number of locks exceeds the lock table size

Current condition<ref>[https://dev.mysql.com/doc/refman/8.0/en/show-variables.html MySQL :: MySQL 8.0 Reference Manual :: 13.7.6.39 SHOW VARIABLES Syntax]</ref>:
* Keywin {{kbd | key = <nowiki>SHOW VARIABLES LIKE 'innodb_buffer_pool_size';</nowiki>}}. If it returns {{kbd | key=8388608}}, it means {{kbd | key=8388608}} bytes ≅ 8MB.

Solution:
* Increase {{kbd | key = innodb_buffer_pool_size}} e.g. {{kbd | key =<nowiki>SET GLOBAL innodb_buffer_pool_size=402653184;</nowiki>}} (402653184 bytes ~ 400MB. Default value is 8MB.)
* Reduce the size of query data

Further reading:
* [http://stackoverflow.com/questions/5696857/how-to-change-value-for-innodb-buffer-pool-size-in-mysql-on-mac-os innodb - How to change value for innodb_buffer_pool_size in MySQL on Mac OS? - Stack Overflow].
* [https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html MySQL :: MySQL 5.7 Reference Manual :: 14.6.3.2 Configuring InnoDB Buffer Pool Size]

=== ERROR 1205: Lock wait timeout exceeded; try restarting transaction ===
Solution: <ref>[https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im mysql - Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction - Stack Overflow]</ref>
# {{kbd | key=<nowiki>SHOW OPEN TABLES WHERE in_use > 0;</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/show-open-tables.html MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.24 SHOW OPEN TABLES Syntax]</ref>
# {{kbd | key=<nowiki>SHOW [FULL] PROCESSLIST;</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.29 SHOW PROCESSLIST Syntax]</ref>
# {{kbd | key=<nowiki>KILL <process id>;</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/kill.html MySQL :: MySQL 5.7 Reference Manual :: 13.7.6.4 KILL Syntax]</ref>

Related article
* [https://errerrors.blogspot.com/2022/10/how-to-fix-lock-wait-timeout-exceeded-try-restarting-transaction-on-mysql.html 解決 MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction]

=== ERROR 1114 (HY000): The table `TABLE_NAME` is full ===
Possible solution
* Because the hard disk of partition where [https://dev.mysql.com/doc/refman/5.7/en/data-directory.html MySQL Data Directory] located is full or almost full, free some hard disk space<ref>[http://stackoverflow.com/questions/730579/error-1114-hy000-the-table-is-full mysql - ERROR 1114 (HY000): The table is full - Stack Overflow]</ref>.

== Miscellaneous Tool-Specific Errors ==

=== ERROR: ASCII \0 appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode ===
Message: {{kbd | key=<nowiki>ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: ''.</nowiki>}}

Condition: When I import the sql file and I met the above error message.

Solution: Unzip the file and then import the file again <ref>[https://stackoverflow.com/questions/17158367/enable-binary-mode-while-restoring-a-database-from-an-sql-dump mysql - Enable binary mode while restoring a Database from an SQL dump - Stack Overflow]</ref>. The sql file is a compressed file. You may use [https://en.wikipedia.org/wiki/File_(command) file (command)] for recognizing the type of file.
<pre>
$ file compressed.sql
compressed.sql: gzip compressed data

$ file plain_text.sql
plain_text.sql: UTF-8 Unicode text, with very long lines
</pre>

=== Errcode: 13 Permission denied ===
The message occurred after executed {{kbd | key=<nowiki>mysqld.exe --datadir=..\data --console</nowiki>}} when I tried to start the service. (Version of MySQL：5.5.5-10.0.12-MariaDB on {{Win}})
<pre>
> mysqld.exe --datadir=..\data --console
180430 10:33:38 [ERROR] mysqld.exe: File 'C:\MariaDB_10.0\bin\..\data\aria_log_control' not found (Errcode: 13 "Permission denied")
180430 10:33:38 [ERROR] mysqld.exe: Got error 'Can't open file' when trying to use aria control file 'C:\MariaDB_10.0\bin\..\data\aria_log_control'
180430 10:33:38 [ERROR] Plugin 'Aria' init function returned error.
180430 10:33:38 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
180430 10:33:38 [Note] InnoDB: Using mutexes to ref count buffer pool pages
180430 10:33:38 [Note] InnoDB: The InnoDB memory heap is disabled
180430 10:33:38 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
180430 10:33:38 [Note] InnoDB: Compressed tables use zlib 1.2.3
180430 10:33:38 [Note] InnoDB: Not using CPU crc32 instructions
180430 10:33:38 [Note] InnoDB: Initializing buffer pool, size = 4.0G
180430 10:33:38 [Note] InnoDB: Completed initialization of buffer pool
180430 10:33:38 [ERROR] InnoDB: .\ibdata1 can't be opened in read-write mode
180430 10:33:38 [ERROR] InnoDB: The system tablespace must be writable!
180430 10:33:38 [ERROR] Plugin 'InnoDB' init function returned error.
180430 10:33:38 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180430 10:33:38 [Note] Plugin 'FEEDBACK' is disabled.
180430 10:33:38 [ERROR] Unknown/unsupported storage engine: innodb
180430 10:33:38 [ERROR] Aborting

180430 10:33:38 [Note] mysqld.exe: Shutdown complete

</pre>

Solutions:

Open the command line with administrative privileges. (How to: [https://www.howtogeek.com/194041/how-to-open-the-command-prompt-as-administrator-in-windows-8.1/ How to Open the Command Prompt as Administrator in Windows 8 or 10])

=== Navicat error: Import data mismatch ===
Error:

When importing an Excel file into a MySQL database using Navicat, there is an issue with the total number of records not matching. Solution: Delete blank rows and check for any duplicate primary key data.

Solution:
* [https://errerrors.blogspot.com/2023/11/blog-post.html 解決 NaviCat 匯入 Excel 檔案到資料庫，出現資料總筆數不一致的問題] (written in Mandarin)

=== Navicat error: [Exp] OLE error 800A03EC ===
error message:
<pre>
[Msg] [Exp] Export to - test.xlsx
[Err] [Row1048576] [Exp] OLE error 800A03EC
[Err] [Row1048577] [Exp] OLE error 800A03EC
[Err] [Row1048578] [Exp] OLE error 800A03EC
[Err] [Row1048579] [Exp] OLE error 800A03EC
</pre>

cause:
* Rows count of results exceed the limit of [[Microsoft Excel]] Worksheet size: 1,048,576 rows

solution:
* LIMIT the rows of MySQL query

== Further Reading ==
* If you cannot find the answer you're looking for, you can visit [https://stackoverflow.com/questions/tagged/mysql Unanswered 'mysql' Questions - Stack Overflow]

== References ==
<references/>

{{Template:Data factory flow}}

[[Category: MySQL]]
[[Category: Database]]
[[Category: Data Science]]
[[Category: Revised with LLMs]]

User:Planetoid

2026-06-18T14:38:46Z

Planetoid: /* social */

{{Semi-protected}}

Hello, Bonjour, Ciao, Hallo, Hola, Merhaba & 大家好!

Shortcut: [http://blog.planetoid.info/ Blog], [[PlanetoidNotes]]

: https://planetoid.info/images/portrait.gif
: 我也要大頭貼 --> [http://www.abi-station.com/tchinese/ 似顏繪]

== 講座分享 talk ==
* [https://hackmd.io/@LWS9lwHlSHK5Qb8Aia1L2g/rJLSh2sIxg#/ 氛圍式程式開發：從 0 到 1 打造直覺式資料專案] (2025-08-10) 分享於 [https://coscup.org/2025/ COSCUP x RubyConf Taiwan 2025]，台灣科技大學研揚大樓
* 製作自己的 GPT 助理：熱門 GPT 語文助理應用 (2024-08-08) 「2024 QT國文教學工作坊」，國文課的「教」與「學」— 國文教學的想像、設計與實踐，分享於國立師範大學教育學院大樓202國際會議廳。
* [https://hackmd.io/@LWS9lwHlSHK5Qb8Aia1L2g/Bk96m5QYC#/ 生成式 AI 於 WIKIDATA 中的寫作應用 – 從資料完整性到資料協作品質] (2024-08-03) 分享於 [https://coscup.org/2024/zh-TW/ COSCUP 2024]，台灣科技大學研揚大樓
* 製作自己的 GPTs 機器人 (2024-06-29) 「AI 工具手冊:學會AI魔法讓你變身超人不再當麻瓜」簽書見面會，分享於天瓏書局。
* AI 體驗-從生成式對話機器人到自動生圖，談AI特色與限制 (2024-04-10) 師大【技能提升工作坊】，分享於國立師範大學教育學院大樓。
* [https://hackmd.io/@LWS9lwHlSHK5Qb8Aia1L2g/By2KIujG6 Translation issue of short vocabulary - 短詞彙的翻譯問題] (2023-10-29) 分享於 [https://wikidatacon.tw/program WikidataCon 2023] 閃電講，國立臺北大學三峽校區人文學院一樓。
* [https://hackmd.io/@LWS9lwHlSHK5Qb8Aia1L2g/rJhRRcs72#/ 可以召喚 ChatGPT 使用 WikiData 資料嗎？ – ChatGPT 整合公開資料的可能] (2023-07-30) 分享於 [https://coscup.org/2023/ COSCUP 2023]，台灣科技大學綜合教學大樓。專案 [https://github.com/planetoid/chatgpt_x_wikidata source code]
* [https://hackmd.io/@LWS9lwHlSHK5Qb8Aia1L2g/Bya2bbb3q#/ WikiData 與政府公開資料整合經驗] (2022-07-30) 分享於 [https://coscup.org/2022/ COSCUP 2022]，台灣科技大學研揚大樓
* [https://hackmd.io/@LWS9lwHlSHK5Qb8Aia1L2g/SknTVe4u_ Wikidata 著錄問題，開源工具可以幫忙什麼] (2021-07-31) 分享於 [https://coscup.org/2021/ COSCUP 2021]，採線上預錄方式進行
* [https://hackmd.io/@LWS9lwHlSHK5Qb8Aia1L2g/Syg-a35xw Lua 模版快速取用 Wikidata] (2020-08-02) 分享於 [https://coscup.org/2020/ COSCUP 2020]，台灣科技大學研揚大樓
* [https://www.slideshare.net/planetoid/wikidata-164489690 Wikidata 新手編輯工具箱] (2019-08-17) 分享於 [https://coscup.org/2019/ COSCUP 2019]，台灣科技大學研揚大樓
* [https://blog.planetoid.info/2017/09/wikimedia-taiwan-10th-anniversary-lighting-talk.html 維基人職場的小彆扭 2017 年版] (2017-09-17) 分享於 [https://meta.wikimedia.org/wiki/Wikimedia_Taiwan/10th_anniversary/Anniversary Wikimedia Taiwan十週年研討會]，國立清華大學台達館
* [https://www.slideshare.net/planetoid/ss-12043149 維基人職場的小彆扭] (2012-03-17) 分享於 [https://zh.wikipedia.org/wiki/Wikipedia:%E8%81%9A%E6%9C%83/2012%E5%B9%B4%E5%8F%B0%E7%81%A3%E6%98%A5%E8%81%9A 中文維基百科 2012 年台灣春聚]，東海大學東海書房
* [https://blog.planetoid.info/2008/07/firefox.html Firefox 與個人知識管理] (2008-07-19) [https://wiki.moztw.org/Firefox_3_Go_Photos Firefox Party 3.0]，華碩電腦企業營運總部三樓咖啡廳
* [https://www.slideshare.net/planetoid/20070725-84188 校園共筆應用] (2007-07-25) OSSACC 教育部校園自由軟體數位資源推廣服務中心。([https://web.archive.org/web/20070629172822/http://news.ossacc.org/wiki/index.html 講座網頁備份])

== social ==
My contribution to wikimedia projects, such as
* [http://en.wikipedia.org/wiki/Special:Contributions/Planetoid English wikipedia]
* [https://simple.wikipedia.org/wiki/Special:Contributions/Planetoid Simple English Wikipedia]
* [http://zh.wikipedia.org/wiki/Special:Contributions/Planetoid Chinese wikipedia]
* [http://zh.wikibooks.org/wiki/Special:%E7%94%A8%E6%88%B7%E8%B4%A1%E7%8C%AE/Planetoid Chinese wikibooks]
* [http://meta.wikimedia.org/wiki/Special:Contributions/Planetoid Meta-Wiki]
* [http://www.mediawiki.org/wiki/Special:Contributions/Planetoid MediaWiki]
* [http://commons.wikimedia.org/wiki/Special:Contributions/Planetoid wikimedia commons]
* [https://www.wikidata.org/wiki/Special:Contributions/Planetoid Wikidata]

Other projects

* [http://zh.scratchpad.wikia.com/wiki/Special:Contributions/Planetoid my contribution][http://scratchpad.wikia.com/wiki/Special:Contributions/Planetoid] to [http://zh.scratchpad.wikia.com/wiki/%E5%9C%8D%E7%B4%80%E5%AF%A6%E9%A9%97%E5%AE%A4 圍紀實驗室]/[http://scratchpad.wikia.com/wiki/Scratchpad_Wiki_Labs Scratchpad]
* [http://userstyles.org/users/3650 planetoid - userstyles.org]
* user scripts hosted by [https://greasyfork.org/zh-TW/users/160335-planetoid greasyfork]
** [https://greasyfork.org/zh-TW/scripts/506037-hackmd-copy-code-snippet HackMD Copy Code Snippet] MIT License
** [https://greasyfork.org/zh-TW/scripts/570350-hide-referrer Hide Referrer] MIT License
* [https://github.com/planetoid/remove-tracking-tokens-from-URL Remove tracking tokens from URL]
* Taiwan Administrative Data Integration with Wikidata ([https://www.wikidata.org/wiki/Wikidata_talk:Impact_stories#Taiwan_Administrative_Data_Integration_with_Wikidata introduction])

文章 articles
* [https://www.facebook.com/notes/uigathering-%E5%8F%B0%E7%81%A3%E4%BD%BF%E7%94%A8%E8%80%85%E7%B6%93%E9%A9%97%E8%A8%AD%E8%A8%88%E5%8D%94%E6%9C%83/%E6%96%87%E6%9C%AC%E6%83%85%E7%B7%92%E5%88%86%E6%9E%90/10156244217783382/ 文本情緒分析] 2018-07-18

個人網誌 blog
# 初代機 @ <del>[http://www.upsaid.com/planetoid/ upsaid.com]</del> ([http://web.archive.org/web/*/http://www.upsaid.com/planetoid/ 網頁備份])
# 實驗機 @ <del>[http://openmusic-album.netfirms.com/mt/ netfirms.com]</del> 詳見 [[Blog at netfirms]]
# 二代機 @ <del>[http://voice.onlyfun.net/mt/spam/ voice.onlyfun.net]</del>
# 詩議會 <del>[http://www.voice.onlyfun.net/poemcongress/index.php voice.onlyfun.net]</del> 搬移至[http://planetoid.info/archived/poemcongress/ planetoid.info]
# [http://planetoid.info/weblog/ 三代機] @ planetoid.info

== 各種語言的問候語(Greeting Languages) ==

'Allo
Ahoy, xxx (English)
Aloha (Hawaiian language)
Annyeonghaseyo (Korean)
Bonjour (French)
Buna ziua (Romanian)
[https://en.wiktionary.org/wiki/buorre_beaivi Buorre beaivi] (Good day in Northern Sami 北方薩米語)
Fáilte! ([http://en.wikipedia.org/wiki/Irish_language Irish language] 愛爾蘭語)
Chonî: Say hello in [https://en.wikipedia.org/wiki/Kurdish_languages Kurdish languages] (庫德語)
ciao ([https://www.wikihow.com/Say-Hello-in-Italian Say Hello in Italian])
Good Day!
Hallo! (German. [https://www.wikihow.com/Say-Hello-in-German 4 Ways to Say Hello in German - wikiHow])
Hi
[https://zh.m.wiktionary.org/zh-hant/hej Hej] 瑞典語/[https://zh.wikipedia.org/wiki/%E4%B8%B9%E9%BA%A6%E8%AF%AD 丹麥語]
Hola (Spanish 西班牙語)
Mingalaba (Burmese language 緬甸語)
Moyo! (西魯巴語)
こんにちわ ''/ Kon'nichiwa /'' (Japanese[http://translate.google.com/translate_t?js=n&prev=_t&hl=en&ie=UTF-8&layout=2&eotf=1&sl=en&tl=th&text=hello&file=#ja|en|%E3%81%93%E3%82%93%E3%81%AB%E3%81%A1%E3%82%8F])
Live long and prosper ([https://en.wikipedia.org/wiki/Vulcan_salute Vulcan salute])
Mabuhay (Filipino means Hello, Welcome and Long Life[http://translate.google.com/translate_t?js=n&prev=_t&hl=en&ie=UTF-8&layout=2&eotf=1&sl=en&tl=th&text=hello&file=#auto|en|Mabuhay])
Merhaba (Turkish)
Mingalar pa (Indonesian)
Pryvit (Say hello in Ukrainian)
[https://en.wiktionary.org/wiki/salut#French Salut] (French, 法語)
[https://en.wikipedia.org/wiki/Servus Servus] in German
[https://en.wikipedia.org/wiki/Shalom Shalom] is a Hebrew word to mean both hello and goodbye.
S̄wạs̄dī ''/ Sawatdee ka! /''(Hello in Thai[http://www.blurtit.com/q4200680.html])
[https://en.wiktionary.org/wiki/tere#Estonian Tere] (Hello in Estonian 愛沙尼亞語)
Whassuuuup
[https://en.wiktionary.org/wiki/witaj Witaj] 用波蘭語打招呼, Welcome in Polish language
Xin chào! (Vietnamese)
नमस्ते (Hello in Hindī 北印度語)
Приве́т. (Namaste, [https://learnrussianstepbystep.com/en/how-to-say-hello-in-russian/ Hello in Russian])
你呷飽未？ Say hello in Taiwanese
欢迎大家好: Say hello in Chinese
歡迎大家好: Say hello in Mandarin

more on
* [http://www.flickr.com/forums/ideas/1881 Flickr: Forums: FlickrIdeas]: [http://www.flickr.com/search/forum/?q=Greeting%20Languages Greeting Languages](last visited: July 16, 2006)
* [http://www.omniglot.com/language/phrases/hello.htm Translations of Hello and general greetings in many languages] {{access | date = 2015-03-28}}
* [http://voyager.jpl.nasa.gov/spacecraft/greetings.html Voyager - The Interstellar Mission] {{access | date = 2015-09-05}}
* [http://www.douban.com/note/26601423/ 打招呼]

== special characters ==
[[ASCII art]]

== other notes ==
* [[:Category: Draft]] 草稿狀態

Troubleshooting of GIT

2026-06-17T08:04:55Z

Planetoid: /* SSH Authentication Failed: Permission denied (publickey) */

== Troubleshooting of GIT ==

=== Restoring deleted files in Git ===
[[Restoring deleted files in Git]]

=== SSH Authentication Failed: Permission denied (publickey) ===

'''Error Message'''

After keyin the command <pre>git clone git@github.com:your-org/your-repo.git /opt/your-app</pre>, met the error message

<pre>
Cloning into '/opt/your-app'...
git@github.com: Permission denied (publickey).
fatal: Could not read from remote repository.

Please make sure you have the correct access rights and the repository exists.
</pre>

'''Cause'''

This error occurs when Git cannot authenticate with GitHub over SSH. This typically means the SSH key on your machine is either missing, not registered with your GitHub account, or not being picked up by the SSH agent.

'''Solutions'''

'''Option 1 — Configure your SSH config file'''

Add an entry to `~/.ssh/config` so Git automatically uses the correct key:

<pre>
Host github.com
HostName github.com
User git
IdentityFile /path/to/.ssh/your_key_name
</pre>

'''Option 2 — Pass the SSH key inline'''

1. Before running `git clone`, test whether the specified SSH key can successfully authenticate with GitHub:

<pre>
ssh -T -i /path/to/.ssh/your_key_name git@github.com
</pre>

If the key is configured correctly, you should see:

<pre>
Hi your-org/your-app! You've successfully authenticated, but GitHub does not provide shell access.
</pre>

`successfully authenticated` confirms the key is correctly linked to your GitHub account and you're good to proceed with `git clone`. The message `GitHub does not provide shell access` is expected — SSH is used for Git operations only, not for interactive shell login.

2. Override the SSH command directly when cloning:

<pre>
sudo GIT_SSH_COMMAND='ssh -i /path/to/.ssh/your_key_name -o IdentitiesOnly=yes' \
git clone git@github.com:your-org/your-repo.git /opt/your-app
</pre>

{{exclaim}} Notice: `-o IdentitiesOnly=yes` prevents SSH from trying other keys loaded in the agent, which can cause unexpected auth failures.

=== Git Submodule Pull Failed: unable to create file — File exists ===

'''Error Message'''

After pulling in GitHub Desktop, met the error message

<pre>
Fetching submodule lib
error: unable to create file feeds/example.php: File exists
Updating xxxxxxxx..xxxxxxxx
</pre>

'''Cause'''

When Git tries to update the submodule to a newer commit, it finds a local untracked file with the same name, blocking the checkout. A mid-pull failure leaves the submodule in an inconsistent state and causes a large number of unexpected unstaged changes to appear in GitHub Desktop.

'''Solution'''

Run the following command in the project root directory to force re-sync the submodule:

<pre>
git submodule update --init --recursive --force
</pre>

If it still fails, manually remove the conflicting file and retry:

<pre>
rm lib/feeds/example.php
git submodule update --init --recursive
</pre>

Enter the submodule directory and verify the status is clean:

<pre>
cd lib
git status
</pre>

The expected output should be:

<pre>
HEAD detached from xxxxxxxx
nothing to commit, working tree clean
</pre>

`nothing to commit, working tree clean` confirms the submodule has been successfully synced — return to GitHub Desktop and continue as normal. `HEAD detached` is the expected state for a submodule and does not require any action.

{{exclaim}} Notice: The large number of unstaged changes appearing in GitHub Desktop after a failed Pull will typically return to normal once the submodule is fixed. If unexpected changes still remain, run <code>git submodule foreach git checkout .</code> to revert all modifications inside every submodule.

== Further Reading ==

* [https://docs.github.com/en/authentication/connecting-to-github-with-ssh/generating-a-new-ssh-key-and-adding-it-to-the-ssh-agent Generating a new SSH key and adding it to the ssh-agent - GitHub Docs]
* [https://docs.github.com/en/authentication/connecting-to-github-with-ssh/adding-a-new-ssh-key-to-your-github-account Adding a new SSH key to your GitHub account - GitHub Docs]
* [https://docs.github.com/en/authentication/connecting-to-github-with-ssh/testing-your-ssh-connection Testing your SSH connection - GitHub Docs]
* [https://linux.die.net/man/5/ssh_config ssh_config(5): OpenSSH SSH client config files - Linux man page]
* [https://docs.github.com/en/authentication/troubleshooting-ssh Troubleshooting SSH - GitHub Docs]

[[Category: Git]]
[[Category: Revised with LLMs]]

Troubleshooting of PostgreSQL

2026-06-16T07:17:54Z

Planetoid: /* pgAdmin 4 on macOS Has No Autocomplete for Field or Table Names */

Troubleshooting of [https://www.postgresql.org/ PostgreSQL]

== Troubleshooting of PostgreSQL ==
=== How to fix Syntax error: 7 ERROR: LIMIT #,# syntax is not supported ===
Query syntax mer error
<pre>
SELECT *
FROM my_table
LIMIT 0,10
</pre>

Error message
<pre>
Uncaught PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: LIMIT #,# syntax is not supported
HINT: Use separate LIMIT and OFFSET clauses.
</pre>

Correct query syntax<ref>[http://www.sqlines.com/postgresql/limit_offset PostgreSQL Queries - OFFSET and LIMIT - Guide, Examples and Alternatives - SQLines Tools]</ref>
<pre>
SELECT *
FROM my_table
LIMIT 10 OFFSET 0
</pre>

=== How to fix ERROR: operator does not exist: ` character varying ===
The following SQL query has a syntax error:
<pre>
SELECT `my_columm`
from my_table
LIMIT 10
</pre>

Error message
<pre>
ERROR: operator does not exist: ` character varying
LINE 1: SELECT `content_hash`
^
HINT: No operator matches the given name and argument type. You might need to add an explicit type cast.
</pre>

To fix the error, update the query with the correct syntax:
<pre>
SELECT my_columm
from my_table
LIMIT 10
</pre>

=== How to fix ERROR: argument of WHERE must be type boolean, not type integer ===
Query syntax mer error
<pre>
SELECT *
FROM my_table
WHERE 1
</pre>

Correct query syntax
<pre>
SELECT *
FROM my_table
WHERE 1 = 1
</pre>

or
<pre>
SELECT *
FROM my_table
WHERE TRUE
</pre>

=== How to fix the ERROR: column "count" does not exist ===
The following SQL query has a syntax error:
<pre>
SELECT count(*) count, content_hash
from my_table
GROUP BY content_hash
having count >= 2
</pre>

To fix the error, update the query with the correct syntax:
<pre>
SELECT count(*) count, content_hash
from my_table
GROUP BY content_hash
having count(*) >= 2
</pre>

=== How to fix: The numeric column data all became null after imported ===
[[Troubleshooting of Navicat for PostgreSQL]]

=== pgAdmin 4 on macOS Has No Autocomplete for Field or Table Names ===

[https://www.pgadmin.org/download/ pgAdmin] 4 has autocomplete built in by default, but it sometimes needs to be triggered manually or configured first.

'''How to trigger it'''
In the Query Tool, press `Ctrl / Cmd + Space`. However, on macOS, `Ctrl + Space` is reserved for switching input methods, and `Cmd + Space` opens Spotlight, so the shortcut conflict likely prevents the autocomplete menu from appearing. This menu is what normally shows suggestions for tables, columns, schemas, and keywords.

'''How to make autocomplete appear as you type'''

# Windows / Linux: Go to File → Preferences → Query Tool → Auto Completion, and make sure "Keywords autocomplete?" is checked.
# Mac: Go to pgAdmin 4 → Preferences → Query Tool → Auto Completion, and make sure "Keywords autocomplete?" is checked.

== Further reading ==
* [https://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause dynamic sql - Why would someone use WHERE 1=1 AND <conditions> in a SQL clause? - Stack Overflow]
* [https://koalatea.io/postgres-insert-ignore/ Working with Insert Into Ignore in Postgres]

== References ==
<references />

{{Template:Troubleshooting}}

[[Category:PostgreSQL]]
[[Category:Database]]
[[Category:Data Science]]

Troubleshooting of PostgreSQL

2026-06-16T06:37:01Z

Planetoid: /* pgAdmin 4 on macOS Has No Autocomplete for Field or Table Names */

Troubleshooting of [https://www.postgresql.org/ PostgreSQL]

== Troubleshooting of PostgreSQL ==
=== How to fix Syntax error: 7 ERROR: LIMIT #,# syntax is not supported ===
Query syntax mer error
<pre>
SELECT *
FROM my_table
LIMIT 0,10
</pre>

Error message
<pre>
Uncaught PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: LIMIT #,# syntax is not supported
HINT: Use separate LIMIT and OFFSET clauses.
</pre>

Correct query syntax<ref>[http://www.sqlines.com/postgresql/limit_offset PostgreSQL Queries - OFFSET and LIMIT - Guide, Examples and Alternatives - SQLines Tools]</ref>
<pre>
SELECT *
FROM my_table
LIMIT 10 OFFSET 0
</pre>

=== How to fix ERROR: operator does not exist: ` character varying ===
The following SQL query has a syntax error:
<pre>
SELECT `my_columm`
from my_table
LIMIT 10
</pre>

Error message
<pre>
ERROR: operator does not exist: ` character varying
LINE 1: SELECT `content_hash`
^
HINT: No operator matches the given name and argument type. You might need to add an explicit type cast.
</pre>

To fix the error, update the query with the correct syntax:
<pre>
SELECT my_columm
from my_table
LIMIT 10
</pre>

=== How to fix ERROR: argument of WHERE must be type boolean, not type integer ===
Query syntax mer error
<pre>
SELECT *
FROM my_table
WHERE 1
</pre>

Correct query syntax
<pre>
SELECT *
FROM my_table
WHERE 1 = 1
</pre>

or
<pre>
SELECT *
FROM my_table
WHERE TRUE
</pre>

=== How to fix the ERROR: column "count" does not exist ===
The following SQL query has a syntax error:
<pre>
SELECT count(*) count, content_hash
from my_table
GROUP BY content_hash
having count >= 2
</pre>

To fix the error, update the query with the correct syntax:
<pre>
SELECT count(*) count, content_hash
from my_table
GROUP BY content_hash
having count(*) >= 2
</pre>

=== How to fix: The numeric column data all became null after imported ===
[[Troubleshooting of Navicat for PostgreSQL]]

=== pgAdmin 4 on macOS Has No Autocomplete for Field or Table Names ===

[https://www.pgadmin.org/download/ pgAdmin] 4 has autocomplete built in by default, but it sometimes needs to be triggered manually or configured first.

'''How to trigger it'''
In the Query Tool, press `Ctrl / Cmd + Space`. However, on macOS, `Ctrl + Space` is reserved for switching input methods, and `Cmd + Space` opens Spotlight — so the shortcut conflict likely prevents the autocomplete menu from appearing. This menu is what normally shows suggestions for tables, columns, schemas, and keywords.

'''How to make autocomplete appear as you type'''

# Windows / Linux: Go to File → Preferences → Query Tool → Auto Completion, and make sure "Keywords autocomplete?" is checked.
# Mac: Go to pgAdmin 4 → Preferences → Query Tool → Auto Completion, and make sure "Keywords autocomplete?" is checked.

== Further reading ==
* [https://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause dynamic sql - Why would someone use WHERE 1=1 AND <conditions> in a SQL clause? - Stack Overflow]
* [https://koalatea.io/postgres-insert-ignore/ Working with Insert Into Ignore in Postgres]

== References ==
<references />

{{Template:Troubleshooting}}

[[Category:PostgreSQL]]
[[Category:Database]]
[[Category:Data Science]]

Troubleshooting of PostgreSQL

2026-06-16T06:36:09Z

Planetoid: /* How to fix Syntax error of PostgreSQL */

Troubleshooting of [https://www.postgresql.org/ PostgreSQL]

== Troubleshooting of PostgreSQL ==
=== How to fix Syntax error: 7 ERROR: LIMIT #,# syntax is not supported ===
Query syntax mer error
<pre>
SELECT *
FROM my_table
LIMIT 0,10
</pre>

Error message
<pre>
Uncaught PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: LIMIT #,# syntax is not supported
HINT: Use separate LIMIT and OFFSET clauses.
</pre>

Correct query syntax<ref>[http://www.sqlines.com/postgresql/limit_offset PostgreSQL Queries - OFFSET and LIMIT - Guide, Examples and Alternatives - SQLines Tools]</ref>
<pre>
SELECT *
FROM my_table
LIMIT 10 OFFSET 0
</pre>

=== How to fix ERROR: operator does not exist: ` character varying ===
The following SQL query has a syntax error:
<pre>
SELECT `my_columm`
from my_table
LIMIT 10
</pre>

Error message
<pre>
ERROR: operator does not exist: ` character varying
LINE 1: SELECT `content_hash`
^
HINT: No operator matches the given name and argument type. You might need to add an explicit type cast.
</pre>

To fix the error, update the query with the correct syntax:
<pre>
SELECT my_columm
from my_table
LIMIT 10
</pre>

=== How to fix ERROR: argument of WHERE must be type boolean, not type integer ===
Query syntax mer error
<pre>
SELECT *
FROM my_table
WHERE 1
</pre>

Correct query syntax
<pre>
SELECT *
FROM my_table
WHERE 1 = 1
</pre>

or
<pre>
SELECT *
FROM my_table
WHERE TRUE
</pre>

=== How to fix the ERROR: column "count" does not exist ===
The following SQL query has a syntax error:
<pre>
SELECT count(*) count, content_hash
from my_table
GROUP BY content_hash
having count >= 2
</pre>

To fix the error, update the query with the correct syntax:
<pre>
SELECT count(*) count, content_hash
from my_table
GROUP BY content_hash
having count(*) >= 2
</pre>

=== How to fix: The numeric column data all became null after imported ===
[[Troubleshooting of Navicat for PostgreSQL]]

=== pgAdmin 4 on macOS Has No Autocomplete for Field or Table Names ===

pgAdmin 4 has autocomplete built in by default, but it sometimes needs to be triggered manually or configured first.

'''How to trigger it'''
In the Query Tool, press `Ctrl / Cmd + Space`. However, on macOS, `Ctrl + Space` is reserved for switching input methods, and `Cmd + Space` opens Spotlight — so the shortcut conflict likely prevents the autocomplete menu from appearing. This menu is what normally shows suggestions for tables, columns, schemas, and keywords.

'''How to make autocomplete appear as you type'''

# Windows / Linux: Go to File → Preferences → Query Tool → Auto Completion, and make sure "Keywords autocomplete?" is checked.
# Mac: Go to pgAdmin 4 → Preferences → Query Tool → Auto Completion, and make sure "Keywords autocomplete?" is checked.

== Further reading ==
* [https://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause dynamic sql - Why would someone use WHERE 1=1 AND <conditions> in a SQL clause? - Stack Overflow]
* [https://koalatea.io/postgres-insert-ignore/ Working with Insert Into Ignore in Postgres]

== References ==
<references />

{{Template:Troubleshooting}}

[[Category:PostgreSQL]]
[[Category:Database]]
[[Category:Data Science]]

PDF Table Parsing

2026-06-15T12:48:04Z

Planetoid: /* 1. Terminology */

Technical Notes on PDF Table Parsing

PDF table parsing usually cannot rely solely on the raw output produced by tools such as <code>pdfplumber.extract_tables()</code>, Camelot, Tabula, or similar libraries. Since PDF is primarily a layout-oriented format rather than a structured data format, practical implementations often require additional rules, state management, and post-processing steps in order to produce stable and usable datasets.

=== Comparison of Different Parsers ===

# [https://github.com/jsvine/pdfplumber pdfplumber] requires writing code to process the data.
# [https://www.xpdfreader.com/pdftotext-man.html pdftotext] with the `-layout` option: The drawback is that columns are aligned using spaces, rather than being extracted as a truly structured table.
# [https://jina.ai/ jina.ai parser]: The drawback is that columns are aligned using spaces, rather than being extracted as a truly structured table.

=== 1. Terminology ===

'''Parser'''

A <code>parser</code> is a program responsible for parsing PDF content and converting it into structured data. It usually does more than simply read text or tables; it also needs to identify sections, table types, column positions, data rows, continuations, and source metadata.

'''Record'''

A <code>record</code> is a standardized data entry produced by the parser.

A single row in a PDF does not necessarily correspond to one record. The same record may be split across multiple rows, continue across pages, or require inherited information from a parent row before it becomes complete.

'''Section / Subsection'''

<code>section</code> and <code>subsection</code> refer to the section and subsection in a document.

These fields are typically used to describe the source context of the data, such as which section or table block a particular record comes from.

'''Table Type'''

<code>table_type</code> is a table-type label defined by the parser to indicate what kind of data structure the current table represents.

Examples include:

* <code>summary_table</code>
* <code>detail_table</code>
* <code>parent_child_table</code>
* <code>cross_reference_table</code>
* <code>unknown_table</code>

The purpose of <code>table_type</code> is to help the parser determine which set of parsing rules should be applied.

It is not a built-in PDF field, nor is it information automatically provided by <code>pdfplumber</code>, Camelot, or Tabula.

'''Table Schema'''

A <code>table schema</code> refers to the column structure and parsing rules for a specific type of table.

It usually includes the number of columns, column order, standardized column names, column aliases, required fields, nullable fields, and whether special continuation handling or parent-child row handling is needed.

In PDF table parsing, a <code>table schema</code> is not necessarily a database schema. Instead, it is a set of rules used by the parser to align and standardize table data.

'''Column Mapping'''

<code>column mapping</code> refers to the process of mapping original PDF columns to standardized fields.

For example, different documents may use headers such as “Item Name,” “Object Name,” or “Target Name,” but all of them can be normalized into <code>item_name</code> in the output data.

'''Alias Mapping'''

<code>alias mapping</code> refers to a lookup table for header aliases.

Because PDF table headers often vary across versions, formats, or reporting units, the parser needs to map multiple header names to the same standardized field.

'''Forward-Fill'''

<code>forward-fill</code> refers to the process of allowing later rows to inherit field values from previous rows.

In a parent-child row structure, a parent row may appear only once, while subsequent child rows omit repeated information. In this case, the parser needs to fill the parent-row information into the child rows.

'''Parent Row / Child Row'''

A <code>parent row</code> is a row that provides main category, group, or summary information.

A <code>child row</code> is a detail row that belongs to a parent row.

In PDF tables, it is common for a parent row to list primary information while child rows only list detailed items. Without handling parent-child row relationships, the output data may lose necessary context.

'''Cross-Page Continuation'''

<code>cross-page continuation</code> refers to a situation where the same record is split and continues onto the next page.

This may occur in names, descriptive text, category fields, remarks, or other long fields.

Cross-page continuation handling requires determining whether text on the next page belongs to an unfinished record from the previous page, instead of treating it directly as a new data row.

'''Metadata'''

<code>metadata</code> refers to auxiliary information that describes the data source and parsing state.

Common fields include <code>page</code>, <code>table_index</code>, <code>table_type</code>, <code>section</code>, <code>subsection</code>, <code>source_file</code>, <code>raw_row</code>, and <code>parse_warning</code>.

Metadata helps with debugging, auditing, data traceability, and data quality checks.

=== 2. Section Detection Requires Cross-Page State ===

Section, subsection, and table category information in PDF documents may not be repeated on every page. Therefore, the parser needs to preserve the current <code>section</code>, <code>subsection</code>, or other document-level context and reuse it on subsequent pages.

Common scenarios include:

* Continuation pages may contain only table content without repeating the section title.
* Table of contents, summary, or explanatory pages may contain multiple section names at once. If the parser only checks whether a page contains certain keywords, it may misclassify the page.
* Section titles may use different punctuation or spacing due to layout variations, such as full-width or half-width parentheses, enumeration marks, colons, full-width periods, or extra spaces.
* When switching sections, subsections, or table types, inherited state from the previous table must be cleared to prevent data from being incorrectly carried over into the next block.

A recommended approach is to determine sections using a combination of text position, title patterns, and contextual state, rather than relying only on keyword matching across the entire page.

=== 3. A Single Section May Contain Multiple Table Schemas ===

A single section may contain multiple types of tables. If the parser determines column mapping only from the section name, columns may be misaligned.

When parsing each table, the parser should first inspect the header or the first few rows of the table to determine:

* <code>table_type</code>
* Number of columns
* Column order
* Standardized column names
* Whether parent-child row relationships exist
* Whether special continuation handling is required

Header names may also contain aliases, abbreviations, or terms that vary across document versions. For example, “Item Name,” “Object Name,” and “Target Name” may refer to similar fields in different documents. The implementation should therefore maintain an alias mapping that normalizes different header names into consistent fields.

=== 4. Parent-Child Row Relationships Require Forward-Fill Support ===

Some PDF tables use parent rows to list primary information, while subsequent child rows only list detail fields. For example, a parent row may contain a main category, group name, case number, or summary information, while child rows only list sub-item numbers, detail names, or detailed content.

These tables require forward-fill support, where child rows inherit field values from the parent row.

Design considerations include:

* Parent row state should be isolated by context such as <code>section</code>, <code>subsection</code>, and <code>table_type</code>.
* Parent row state may need to persist across pages.
* A single text field should not be used as the parent row identifier, because different parent rows may have identical or similar names.
* Backfilling should consider at least the section, subsection, table type, and parent-row identifier.

When the section or table changes, parent row state should be explicitly reset to avoid carrying data from the previous table into the next one.

=== 5. Cross-Page Continuation Is More Than Repeated Headers ===

When PDF tables span multiple pages, data may be split in various ways. A parser that only handles repeated headers on the next page is usually insufficient for real-world documents.

Common cross-page continuation cases include:

* The first row on the next page contains only the latter part of a field from the previous page.
* Names, categories, descriptive text, or remarks are split across pages.
* The next page starts with multiple repeated header rows before continuing an unfinished record from the previous page.
* Continuation text and a new data-row identifier appear in the same row.
* Empty columns are compressed, causing column positions to shift.
* Table borders, line breaks, or OCR results split what was originally one row into multiple rows.

Therefore, cross-page processing should preserve original column positions and should not remove empty columns too early. When necessary, the parser should use column coordinates, column indexes, identifier patterns, and the previous record state to determine which field a continuation fragment belongs to.

Continuation merging should also depend on the type of text:

* Chinese text fragments can usually be concatenated directly.
* English text fragments usually require an inserted space.
* Multi-value fields, category fields, or remarks may need to preserve line breaks, enumeration marks, or delimiters.
* Numeric fields should avoid accidentally concatenating text fragments.

=== 6. Headers, Notes, and Data Rows Should Be Detected in Stages ===

PDF tables often contain headers, unit descriptions, notes, subtotals, totals, footers, or blank rows. If a parser treats any row containing text as a data row, it can easily produce incorrect records.

A recommended parsing flow is to separate the process into stages:

# Wait for header keywords to appear.
# Detect the header area and column names.
# Determine where data rows begin.
# Process regular data rows.
# Process cross-page continuations.
# Exclude notes, units, subtotals, totals, and footers.
# Output standardized records.

Missing fields may be filled with <code>null</code>. However, if columns have shifted, the parser must not blindly apply the schema based on the order of a compressed array, as this can misalign the entire row.

=== 7. Metadata Should Be Stored at the Record Level ===

When parsing PDF tables, source context should be preserved in addition to the extracted data itself. This metadata is important for debugging, auditing, data traceability, and downstream flattened outputs.

Each record should preferably include at least:

* <code>page</code>
* <code>table_index</code>
* <code>table_type</code>
* <code>section</code>
* <code>subsection</code>
* <code>source_file</code>
* <code>raw_row</code> or original row data
* <code>parse_warning</code> or parsing warning

Writing metadata at the record level helps prevent context loss when JSON is flattened into CSV, imported into a database, or passed around as individual records.

If the document itself contains a year, quarter, period, version, batch, or category information, that information should also be explicitly preserved to avoid mixing data from different batches.

=== 8. Implementation Recommendations ===

A PDF table parser can use a layered design that separates extraction, classification, cleaning, state management, normalization, and validation.

A recommended workflow is:

# Extract text, tables, and coordinate information from the PDF.
# Detect page sections and table blocks.
# Determine <code>table_type</code> based on header keywords.
# Apply the corresponding schema and header alias mapping.
# Identify data rows, parent rows, child rows, and continuation rows.
# Manage cross-page state, parent row state, and other document-level state.
# Convert the data into standardized records.
# Add source metadata.
# Run regression checks and data quality checks.
# Output JSON, CSV, or database-ready records.

This design reduces the risk of coupling individual rules too tightly to a specific PDF layout and makes it easier to adjust only the affected parts when the document format changes.

[[Category: Tool]]
[[Category: Revised with LLMs]]

PDF Table Parsing

2026-06-15T10:51:32Z

Planetoid:

Technical Notes on PDF Table Parsing

PDF table parsing usually cannot rely solely on the raw output produced by tools such as <code>pdfplumber.extract_tables()</code>, Camelot, Tabula, or similar libraries. Since PDF is primarily a layout-oriented format rather than a structured data format, practical implementations often require additional rules, state management, and post-processing steps in order to produce stable and usable datasets.

=== Comparison of Different Parsers ===

# [https://github.com/jsvine/pdfplumber pdfplumber] requires writing code to process the data.
# [https://www.xpdfreader.com/pdftotext-man.html pdftotext] with the `-layout` option: The drawback is that columns are aligned using spaces, rather than being extracted as a truly structured table.
# [https://jina.ai/ jina.ai parser]: The drawback is that columns are aligned using spaces, rather than being extracted as a truly structured table.

=== 1. Terminology ===

==== Parser ====

A <code>parser</code> is a program responsible for parsing PDF content and converting it into structured data. It usually does more than simply read text or tables; it also needs to identify sections, table types, column positions, data rows, continuations, and source metadata.

==== Record ====

A <code>record</code> is a standardized data entry produced by the parser.

A single row in a PDF does not necessarily correspond to one record. The same record may be split across multiple rows, continue across pages, or require inherited information from a parent row before it becomes complete.

==== Section / Subsection ====

<code>section</code> and <code>subsection</code> refer to the section and subsection in a document.

These fields are typically used to describe the source context of the data, such as which section or table block a particular record comes from.

==== Table Type ====

<code>table_type</code> is a table-type label defined by the parser to indicate what kind of data structure the current table represents.

Examples include:

* <code>summary_table</code>
* <code>detail_table</code>
* <code>parent_child_table</code>
* <code>cross_reference_table</code>
* <code>unknown_table</code>

The purpose of <code>table_type</code> is to help the parser determine which set of parsing rules should be applied.

It is not a built-in PDF field, nor is it information automatically provided by <code>pdfplumber</code>, Camelot, or Tabula.

==== Table Schema ====

A <code>table schema</code> refers to the column structure and parsing rules for a specific type of table.

It usually includes the number of columns, column order, standardized column names, column aliases, required fields, nullable fields, and whether special continuation handling or parent-child row handling is needed.

In PDF table parsing, a <code>table schema</code> is not necessarily a database schema. Instead, it is a set of rules used by the parser to align and standardize table data.

==== Column Mapping ====

<code>column mapping</code> refers to the process of mapping original PDF columns to standardized fields.

For example, different documents may use headers such as “Item Name,” “Object Name,” or “Target Name,” but all of them can be normalized into <code>item_name</code> in the output data.

==== Alias Mapping ====

<code>alias mapping</code> refers to a lookup table for header aliases.

Because PDF table headers often vary across versions, formats, or reporting units, the parser needs to map multiple header names to the same standardized field.

==== Forward-Fill ====

<code>forward-fill</code> refers to the process of allowing later rows to inherit field values from previous rows.

In a parent-child row structure, a parent row may appear only once, while subsequent child rows omit repeated information. In this case, the parser needs to fill the parent-row information into the child rows.

==== Parent Row / Child Row ====

A <code>parent row</code> is a row that provides main category, group, or summary information.

A <code>child row</code> is a detail row that belongs to a parent row.

In PDF tables, it is common for a parent row to list primary information while child rows only list detailed items. Without handling parent-child row relationships, the output data may lose necessary context.

==== Cross-Page Continuation ====

<code>cross-page continuation</code> refers to a situation where the same record is split and continues onto the next page.

This may occur in names, descriptive text, category fields, remarks, or other long fields.

Cross-page continuation handling requires determining whether text on the next page belongs to an unfinished record from the previous page, instead of treating it directly as a new data row.

==== Metadata ====

<code>metadata</code> refers to auxiliary information that describes the data source and parsing state.

Common fields include <code>page</code>, <code>table_index</code>, <code>table_type</code>, <code>section</code>, <code>subsection</code>, <code>source_file</code>, <code>raw_row</code>, and <code>parse_warning</code>.

Metadata helps with debugging, auditing, data traceability, and data quality checks.

=== 2. Section Detection Requires Cross-Page State ===

Section, subsection, and table category information in PDF documents may not be repeated on every page. Therefore, the parser needs to preserve the current <code>section</code>, <code>subsection</code>, or other document-level context and reuse it on subsequent pages.

Common scenarios include:

* Continuation pages may contain only table content without repeating the section title.
* Table of contents, summary, or explanatory pages may contain multiple section names at once. If the parser only checks whether a page contains certain keywords, it may misclassify the page.
* Section titles may use different punctuation or spacing due to layout variations, such as full-width or half-width parentheses, enumeration marks, colons, full-width periods, or extra spaces.
* When switching sections, subsections, or table types, inherited state from the previous table must be cleared to prevent data from being incorrectly carried over into the next block.

A recommended approach is to determine sections using a combination of text position, title patterns, and contextual state, rather than relying only on keyword matching across the entire page.

=== 3. A Single Section May Contain Multiple Table Schemas ===

A single section may contain multiple types of tables. If the parser determines column mapping only from the section name, columns may be misaligned.

When parsing each table, the parser should first inspect the header or the first few rows of the table to determine:

* <code>table_type</code>
* Number of columns
* Column order
* Standardized column names
* Whether parent-child row relationships exist
* Whether special continuation handling is required

Header names may also contain aliases, abbreviations, or terms that vary across document versions. For example, “Item Name,” “Object Name,” and “Target Name” may refer to similar fields in different documents. The implementation should therefore maintain an alias mapping that normalizes different header names into consistent fields.

=== 4. Parent-Child Row Relationships Require Forward-Fill Support ===

Some PDF tables use parent rows to list primary information, while subsequent child rows only list detail fields. For example, a parent row may contain a main category, group name, case number, or summary information, while child rows only list sub-item numbers, detail names, or detailed content.

These tables require forward-fill support, where child rows inherit field values from the parent row.

Design considerations include:

* Parent row state should be isolated by context such as <code>section</code>, <code>subsection</code>, and <code>table_type</code>.
* Parent row state may need to persist across pages.
* A single text field should not be used as the parent row identifier, because different parent rows may have identical or similar names.
* Backfilling should consider at least the section, subsection, table type, and parent-row identifier.

When the section or table changes, parent row state should be explicitly reset to avoid carrying data from the previous table into the next one.

=== 5. Cross-Page Continuation Is More Than Repeated Headers ===

When PDF tables span multiple pages, data may be split in various ways. A parser that only handles repeated headers on the next page is usually insufficient for real-world documents.

Common cross-page continuation cases include:

* The first row on the next page contains only the latter part of a field from the previous page.
* Names, categories, descriptive text, or remarks are split across pages.
* The next page starts with multiple repeated header rows before continuing an unfinished record from the previous page.
* Continuation text and a new data-row identifier appear in the same row.
* Empty columns are compressed, causing column positions to shift.
* Table borders, line breaks, or OCR results split what was originally one row into multiple rows.

Therefore, cross-page processing should preserve original column positions and should not remove empty columns too early. When necessary, the parser should use column coordinates, column indexes, identifier patterns, and the previous record state to determine which field a continuation fragment belongs to.

Continuation merging should also depend on the type of text:

* Chinese text fragments can usually be concatenated directly.
* English text fragments usually require an inserted space.
* Multi-value fields, category fields, or remarks may need to preserve line breaks, enumeration marks, or delimiters.
* Numeric fields should avoid accidentally concatenating text fragments.

=== 6. Headers, Notes, and Data Rows Should Be Detected in Stages ===

PDF tables often contain headers, unit descriptions, notes, subtotals, totals, footers, or blank rows. If a parser treats any row containing text as a data row, it can easily produce incorrect records.

A recommended parsing flow is to separate the process into stages:

# Wait for header keywords to appear.
# Detect the header area and column names.
# Determine where data rows begin.
# Process regular data rows.
# Process cross-page continuations.
# Exclude notes, units, subtotals, totals, and footers.
# Output standardized records.

Missing fields may be filled with <code>null</code>. However, if columns have shifted, the parser must not blindly apply the schema based on the order of a compressed array, as this can misalign the entire row.

=== 7. Metadata Should Be Stored at the Record Level ===

When parsing PDF tables, source context should be preserved in addition to the extracted data itself. This metadata is important for debugging, auditing, data traceability, and downstream flattened outputs.

Each record should preferably include at least:

* <code>page</code>
* <code>table_index</code>
* <code>table_type</code>
* <code>section</code>
* <code>subsection</code>
* <code>source_file</code>
* <code>raw_row</code> or original row data
* <code>parse_warning</code> or parsing warning

Writing metadata at the record level helps prevent context loss when JSON is flattened into CSV, imported into a database, or passed around as individual records.

If the document itself contains a year, quarter, period, version, batch, or category information, that information should also be explicitly preserved to avoid mixing data from different batches.

=== 8. Implementation Recommendations ===

A PDF table parser can use a layered design that separates extraction, classification, cleaning, state management, normalization, and validation.

A recommended workflow is:

# Extract text, tables, and coordinate information from the PDF.
# Detect page sections and table blocks.
# Determine <code>table_type</code> based on header keywords.
# Apply the corresponding schema and header alias mapping.
# Identify data rows, parent rows, child rows, and continuation rows.
# Manage cross-page state, parent row state, and other document-level state.
# Convert the data into standardized records.
# Add source metadata.
# Run regression checks and data quality checks.
# Output JSON, CSV, or database-ready records.

This design reduces the risk of coupling individual rules too tightly to a specific PDF layout and makes it easier to adjust only the affected parts when the document format changes.

[[Category: Tool]]
[[Category: Revised with LLMs]]

PDF Table Parsing

2026-06-15T10:32:01Z

Planetoid: /* 8. Implementation Recommendations */

== Technical Notes on PDF Table Parsing ==

PDF table parsing usually cannot rely solely on the raw output produced by tools such as <code>pdfplumber.extract_tables()</code>, Camelot, Tabula, or similar libraries. Since PDF is primarily a layout-oriented format rather than a structured data format, practical implementations often require additional rules, state management, and post-processing steps in order to produce stable and usable datasets.

The following notes summarize common technical issues that can serve as a reference when developing a PDF table parser.

# Terminology
# Section Detection Requires Cross-Page State
# A Single Section May Contain Multiple Table Schemas
# Parent-Child Row Relationships Require Forward-Fill Support
# Cross-Page Continuation Is More Than Repeated Headers
# Headers, Notes, and Data Rows Should Be Detected in Stages
# Metadata Should Be Stored at the Record Level
# Implementation Recommendations

=== 1. Terminology ===

==== Parser ====

A <code>parser</code> is a program responsible for parsing PDF content and converting it into structured data. It usually does more than simply read text or tables; it also needs to identify sections, table types, column positions, data rows, continuations, and source metadata.

==== Record ====

A <code>record</code> is a standardized data entry produced by the parser.

A single row in a PDF does not necessarily correspond to one record. The same record may be split across multiple rows, continue across pages, or require inherited information from a parent row before it becomes complete.

==== Section / Subsection ====

<code>section</code> and <code>subsection</code> refer to the section and subsection in a document.

These fields are typically used to describe the source context of the data, such as which section or table block a particular record comes from.

==== Table Type ====

<code>table_type</code> is a table-type label defined by the parser to indicate what kind of data structure the current table represents.

Examples include:

* <code>summary_table</code>
* <code>detail_table</code>
* <code>parent_child_table</code>
* <code>cross_reference_table</code>
* <code>unknown_table</code>

The purpose of <code>table_type</code> is to help the parser determine which set of parsing rules should be applied.

It is not a built-in PDF field, nor is it information automatically provided by <code>pdfplumber</code>, Camelot, or Tabula.

==== Table Schema ====

A <code>table schema</code> refers to the column structure and parsing rules for a specific type of table.

It usually includes the number of columns, column order, standardized column names, column aliases, required fields, nullable fields, and whether special continuation handling or parent-child row handling is needed.

In PDF table parsing, a <code>table schema</code> is not necessarily a database schema. Instead, it is a set of rules used by the parser to align and standardize table data.

==== Column Mapping ====

<code>column mapping</code> refers to the process of mapping original PDF columns to standardized fields.

For example, different documents may use headers such as “Item Name,” “Object Name,” or “Target Name,” but all of them can be normalized into <code>item_name</code> in the output data.

==== Alias Mapping ====

<code>alias mapping</code> refers to a lookup table for header aliases.

Because PDF table headers often vary across versions, formats, or reporting units, the parser needs to map multiple header names to the same standardized field.

==== Forward-Fill ====

<code>forward-fill</code> refers to the process of allowing later rows to inherit field values from previous rows.

In a parent-child row structure, a parent row may appear only once, while subsequent child rows omit repeated information. In this case, the parser needs to fill the parent-row information into the child rows.

==== Parent Row / Child Row ====

A <code>parent row</code> is a row that provides main category, group, or summary information.

A <code>child row</code> is a detail row that belongs to a parent row.

In PDF tables, it is common for a parent row to list primary information while child rows only list detailed items. Without handling parent-child row relationships, the output data may lose necessary context.

==== Cross-Page Continuation ====

<code>cross-page continuation</code> refers to a situation where the same record is split and continues onto the next page.

This may occur in names, descriptive text, category fields, remarks, or other long fields.

Cross-page continuation handling requires determining whether text on the next page belongs to an unfinished record from the previous page, instead of treating it directly as a new data row.

==== Metadata ====

<code>metadata</code> refers to auxiliary information that describes the data source and parsing state.

Common fields include <code>page</code>, <code>table_index</code>, <code>table_type</code>, <code>section</code>, <code>subsection</code>, <code>source_file</code>, <code>raw_row</code>, and <code>parse_warning</code>.

Metadata helps with debugging, auditing, data traceability, and data quality checks.

=== 2. Section Detection Requires Cross-Page State ===

Section, subsection, and table category information in PDF documents may not be repeated on every page. Therefore, the parser needs to preserve the current <code>section</code>, <code>subsection</code>, or other document-level context and reuse it on subsequent pages.

Common scenarios include:

* Continuation pages may contain only table content without repeating the section title.
* Table of contents, summary, or explanatory pages may contain multiple section names at once. If the parser only checks whether a page contains certain keywords, it may misclassify the page.
* Section titles may use different punctuation or spacing due to layout variations, such as full-width or half-width parentheses, enumeration marks, colons, full-width periods, or extra spaces.
* When switching sections, subsections, or table types, inherited state from the previous table must be cleared to prevent data from being incorrectly carried over into the next block.

A recommended approach is to determine sections using a combination of text position, title patterns, and contextual state, rather than relying only on keyword matching across the entire page.

=== 3. A Single Section May Contain Multiple Table Schemas ===

A single section may contain multiple types of tables. If the parser determines column mapping only from the section name, columns may be misaligned.

When parsing each table, the parser should first inspect the header or the first few rows of the table to determine:

* <code>table_type</code>
* Number of columns
* Column order
* Standardized column names
* Whether parent-child row relationships exist
* Whether special continuation handling is required

Header names may also contain aliases, abbreviations, or terms that vary across document versions. For example, “Item Name,” “Object Name,” and “Target Name” may refer to similar fields in different documents. The implementation should therefore maintain an alias mapping that normalizes different header names into consistent fields.

=== 4. Parent-Child Row Relationships Require Forward-Fill Support ===

Some PDF tables use parent rows to list primary information, while subsequent child rows only list detail fields. For example, a parent row may contain a main category, group name, case number, or summary information, while child rows only list sub-item numbers, detail names, or detailed content.

These tables require forward-fill support, where child rows inherit field values from the parent row.

Design considerations include:

* Parent row state should be isolated by context such as <code>section</code>, <code>subsection</code>, and <code>table_type</code>.
* Parent row state may need to persist across pages.
* A single text field should not be used as the parent row identifier, because different parent rows may have identical or similar names.
* Backfilling should consider at least the section, subsection, table type, and parent-row identifier.

When the section or table changes, parent row state should be explicitly reset to avoid carrying data from the previous table into the next one.

=== 5. Cross-Page Continuation Is More Than Repeated Headers ===

When PDF tables span multiple pages, data may be split in various ways. A parser that only handles repeated headers on the next page is usually insufficient for real-world documents.

Common cross-page continuation cases include:

* The first row on the next page contains only the latter part of a field from the previous page.
* Names, categories, descriptive text, or remarks are split across pages.
* The next page starts with multiple repeated header rows before continuing an unfinished record from the previous page.
* Continuation text and a new data-row identifier appear in the same row.
* Empty columns are compressed, causing column positions to shift.
* Table borders, line breaks, or OCR results split what was originally one row into multiple rows.

Therefore, cross-page processing should preserve original column positions and should not remove empty columns too early. When necessary, the parser should use column coordinates, column indexes, identifier patterns, and the previous record state to determine which field a continuation fragment belongs to.

Continuation merging should also depend on the type of text:

* Chinese text fragments can usually be concatenated directly.
* English text fragments usually require an inserted space.
* Multi-value fields, category fields, or remarks may need to preserve line breaks, enumeration marks, or delimiters.
* Numeric fields should avoid accidentally concatenating text fragments.

=== 6. Headers, Notes, and Data Rows Should Be Detected in Stages ===

PDF tables often contain headers, unit descriptions, notes, subtotals, totals, footers, or blank rows. If a parser treats any row containing text as a data row, it can easily produce incorrect records.

A recommended parsing flow is to separate the process into stages:

# Wait for header keywords to appear.
# Detect the header area and column names.
# Determine where data rows begin.
# Process regular data rows.
# Process cross-page continuations.
# Exclude notes, units, subtotals, totals, and footers.
# Output standardized records.

Missing fields may be filled with <code>null</code>. However, if columns have shifted, the parser must not blindly apply the schema based on the order of a compressed array, as this can misalign the entire row.

=== 7. Metadata Should Be Stored at the Record Level ===

When parsing PDF tables, source context should be preserved in addition to the extracted data itself. This metadata is important for debugging, auditing, data traceability, and downstream flattened outputs.

Each record should preferably include at least:

* <code>page</code>
* <code>table_index</code>
* <code>table_type</code>
* <code>section</code>
* <code>subsection</code>
* <code>source_file</code>
* <code>raw_row</code> or original row data
* <code>parse_warning</code> or parsing warning

Writing metadata at the record level helps prevent context loss when JSON is flattened into CSV, imported into a database, or passed around as individual records.

If the document itself contains a year, quarter, period, version, batch, or category information, that information should also be explicitly preserved to avoid mixing data from different batches.

=== 8. Implementation Recommendations ===

A PDF table parser can use a layered design that separates extraction, classification, cleaning, state management, normalization, and validation.

A recommended workflow is:

# Extract text, tables, and coordinate information from the PDF.
# Detect page sections and table blocks.
# Determine <code>table_type</code> based on header keywords.
# Apply the corresponding schema and header alias mapping.
# Identify data rows, parent rows, child rows, and continuation rows.
# Manage cross-page state, parent row state, and other document-level state.
# Convert the data into standardized records.
# Add source metadata.
# Run regression checks and data quality checks.
# Output JSON, CSV, or database-ready records.

This design reduces the risk of coupling individual rules too tightly to a specific PDF layout and makes it easier to adjust only the affected parts when the document format changes.

[[Category: Tool]]
[[Category: Revised with LLMs]]

Planetoid:

訂閱指定的關鍵字的相關網頁資料的動態更新 « [[Personal Knowledge Management | 個人知識管理工具]]

== 關鍵字訂閱服務 ==
[http://www.google.com/alerts Google Alerts] (中文介面:[http://www.google.com/alerts?hl=zh-TW Google 快訊])
* 資料類型: 網頁, 新聞, 網誌, 即時(twitter), 影片, 討論(論壇){{Gd}}
* 傳送頻率: 即時傳送, 每天一次, 每週一次
* 傳送方式: e-mail
* 教學: [http://planetoid.info/weblog/2006/01/03/000530.html 使用Google快訊(Google alert)訂閱國外訊息]

== 其他替代新聞搜尋服務 ==
''$'' [https://tavily.com/ Tavily]
* 資料類型: 一般網路資源、新聞、財經類新聞。詢問客服 (2025/4) 不支援台灣的新聞來源，替代方案是直接列出要搜尋的網域來源清單。
* 傳送頻率:
* 傳送方式: API
* 教學:

''$'' 支援網頁搜尋工具 (Web Search Tool) 的 LLMs API

供應商包含 [https://docs.anthropic.com/en/docs/agents-and-tools/tool-use/web-search-tool Anthropic]、[https://platform.openai.com/docs/guides/tools-web-search OpenAI]、[https://docs.x.ai/docs/guides/live-search xAI Grok]。
* 資料類型: 一般網路資源、即時資訊、新聞。
* 傳送方式: API（在請求的 `tools` 陣列中啟用搜尋工具）
* 共同特色:
** 模型自行判斷是否需要執行搜尋，無法由開發者強制觸發。
** 回應自動附帶來源引用。
** 均支援網域白名單（`allowed_domains`）與黑名單（`blocked_domains`）來限制搜尋範圍。
** 費用以搜尋次數計費（約 $5–$10 / 1,000 次），另加 token 費用。
* xAI Grok 額外提供 `x_search` 工具，可搜尋 X/Twitter 平台上的即時貼文與討論。

[https://www.mediacloud.org/ Media Cloud]
* Data sources: Media<ref>[https://www.mediacloud.org/documentation/source-guide Source Guide]</ref>
* Frequency:
* Delivery Method: The API provides access to (1) Media Cloud API news data from 2008 onward<ref>[https://ojs.aaai.org/index.php/ICWSM/article/view/18127 Media Cloud: Massive Open Source Collection of Global News on the Open Web | Proceedings of the International AAAI Conference on Web and Social Media] "About Media Cloud Since 2008, Media Cloud has collected a total of over 1.7 billion stories."</ref> and (2) Wayback Machine news archive data from August 1, 2022 onward tested on 2025/11/1

''out of service'' [https://www.alerts.com/index.php Alerts.com] '''需要輸入美國zip code'''
* 資料類型: 美國國家氣象局([http://www.weather.gov/ National Weather Service])提供的龍捲風、颱風、洪水等惡劣氣候(Severe Weather)警告；新聞、交通等
* 傳送頻率:
* 傳送方式: e-mail, SMS(手機簡訊), 手機語音
* 教學:

''out of service'' [http://alerts.yahoo.com/ Yahoo! Alerts - Create an Alert][http://search.yahoo.com/alert/manage]
* 資料類型: 網頁, 新聞, 股票, 天氣, [http://alerts.yahoo.com/knews_editalert.php Keyword News Alert](取代網誌RSS通知)
* 傳送頻率: 即時傳送, 每天一次
* 傳送方式: e-mail, Yahoo! Messenger
* 教學:

''out of service'' [http://favebot.com/ FaveBot - Stop Searching, Start Discovering!]
* 資料類型: Blogs, Books, DVDs, Events (required to specify your location), Music, News, Photos, Videos
* 傳送頻率: 最少每天一次 "FaveBot will search for new content at least once per day. Blogs and news are searched multiple times per day..." Quoted from [http://favebot.wordpress.com/faq/ FAQ]
* 傳送方式: e-mail, RSS
* 教學:

----
服務名稱與網址 (故意留白)
* 資料類型:
* 傳送頻率:
* 傳送方式:
* 教學:

[[Category: PKM]]
[[Category: Search]]
[[Category: News search]]
[[Category: Revised with LLMs]]