Data type: Difference between revisions

(59 intermediate revisions by the same user not shown)

Line 1:

~~資料表欄位設計時，針對不同資料類型，建議的資料型態~~(data type)

資料庫欄位設計時，針對不同資料類型，建議的資料型態 (data type) 與長度

~~{{Template:Build a website}}~~

== Examples of data and suggested datatype ==

== ~~examples~~ of data and suggested datatype ==

=== birth year ===

* data type: int

* ex: Range from 1905 ~ 2013 (108 yeas old) from the ~~Sing~~-up form from outlook.com {{access| date=2013-02-04}}

* example: Range from 1905 ~ 2013 (108 yeas old) from the Sign-up form from outlook.com {{access| date=2013-02-04}}

* range/limit: 122 yeas old<ref>[http://zh.wikipedia.org/wiki/%E6%9C%80%E5%B9%B4%E9%95%B7%E8%80%85 最年長者 - 維基百科，自由的百科全書]: 「根據金氏世界紀錄大全紀錄的最長壽者是活了122年的雅娜·卡爾曼特」、[https://zh.wikipedia.org/zh-tw/%E5%90%84%E5%9B%BD%E4%BA%BA%E5%8F%A3%E9%A2%84%E6%9C%9F%E5%AF%BF%E5%91%BD%E5%88%97%E8%A1%A8 各國人口預期壽命列表</ref>

Line 15:

Line 13:

=== IP ===

IP(v4)

* ~~varchar~~(15)

* data type: CHAT(15) or VARCHAR(15)

* ex: 255.255.255.255 <ref>[http://stackoverflow.com/questions/1385552/datatype-for-storing-ip-address-in-sql-server Datatype for storing ip address in SQL Server - Stack Overflow]</ref>

Line 28:

Line 26:

=== 網址 ===

* data type: >= MySQL 5.0.3 use {{kbd | key=VARCHAR(2083)}}<ref>[http://stackoverflow.com/questions/219569/best-database-field-type-for-a-url sql - Best database field type for a URL - Stack Overflow]</ref>; {{kbd | key=nvarchar(2083)}} ~~for MS SQL~~

* data type:

* [http://support.microsoft.com/kb/208427 最大 URL 長度是在 Internet Explorer 中的 2,083 字元] for {{IE}}<ref>

** MySQL 5.0.3+ use {{kbd | key=VARCHAR(2083)}}<ref>[http://stackoverflow.com/questions/219569/best-database-field-type-for-a-url sql - Best database field type for a URL - Stack Overflow]</ref> 或 {{kbd | key=TEXT}}

* [http://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url-in-different-browsers http - What is the maximum length of a URL in different browsers? - Stack Overflow]

** MS SQL: {{kbd | key=nvarchar(2083)}}

* [http://wiert.me/2010/04/20/maximum-url-lengths/ Maximum URL lengths « The Wiert Corner – irregular stream of stuff]

</ref>

參考資料

* [http://support.microsoft.com/kb/208427 最大 URL 長度是在 Internet Explorer 中的 2,083 字元] for {{IE}}<ref>[http://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url-in-different-browsers http - What is the maximum length of a URL in different browsers? - Stack Overflow]</ref><ref>[http://wiert.me/2010/04/20/maximum-url-lengths/ Maximum URL lengths « The Wiert Corner – irregular stream of stuff]</ref>

=== ~~Chinese name~~ ===

=== Email ===

* ~~最長姓名有13個字~~ <ref> 中國新聞網 (2010). [http://dailynews.sina.com/bg/tw/twpolitics/chinanews/20101104/00481967448.html 台灣“獨一姓氏”者達149人最長姓名有13個字_台灣頻道_新浪網-北美]</ref><ref>[http://news.ltn.com.tw/news/life/paper/780935 姓名8個字外交部不給護照 - 生活 - 自由時報電子報]</ref>

* data type:

* ~~參考別人~~: ~~LINE姓名欄位長度限制~~: 15字(15個中文字或15英文字母)

** MySQL: {{kbd | key=VARCHAR(254)}}<ref>[https://www.rfc-editor.org/rfc/rfc5321 RFC 5321: Simple Mail Transfer Protocol]"4.5.3.1.3. Path The maximum total length of a reverse-path or forward-path is 256 octets (including the punctuation and element separators)." Complete address in SMTP envelope (including {{kbd | key=<nowiki><...></nowiki>}})</ref>

* 遵循 [https://www.rfc-editor.org/rfc/rfc5321 RFC 5321: Simple Mail Transfer Protocol] 規格

=== 姓名欄位 ===

* 資料類型：{{Gd}} {{kbd | key=<nowiki>CHAR(50)</nowiki>}}、{{kbd | key=<nowiki>VARCHAR(50)</nowiki>}}

* 參考資料：「惟戶政系統中，欄位最多填寫50字，成為名字長度的天花板。」<ref>[https://tw.news.yahoo.com/%E6%94%B9%E5%90%8D%E5%AD%97%E6%95%B8%E7%84%A1%E4%B8%8A%E9%99%90-%E6%88%B6%E6%94%BF%E5%8F%B8-%E6%9C%83%E5%BF%B5%E5%BE%88%E4%B9%85-063953310.html 改名字數無上限戶政司：會念很久]</ref><ref> 中國新聞網 (2010). [http://dailynews.sina.com/bg/tw/twpolitics/chinanews/20101104/00481967448.html 台灣“獨一姓氏”者達149人最長姓名有13個字_台灣頻道_新浪網-北美]</ref><ref>[http://news.ltn.com.tw/news/life/paper/780935 姓名8個字外交部不給護照 - 生活 - 自由時報電子報]</ref>

* 其他人作法: LINE 姓名欄位長度限制: 15字(15個中文字或15英文字母)

=== unix timestamp ===

Line 49:

Line 54:

=== 學校代碼 ===

School ID defined by MOE at Taiwan / 學校代碼<ref>[~~http~~://~~www~~.edu.tw/~~statistics~~/~~content~~.aspx?~~site_content_sn~~=~~25656~~ 各級學校名錄--教育部統計處 Department of Statistics]{{access | date=~~2012~~-02-16}}</ref>

School ID defined by MOE at Taiwan / 學校代碼<ref>[https://depart.moe.edu.tw/ED4500/News.aspx?n=63F5AB3D02A8BBAC&sms=1FF9979D10DBF9F3 各級學校名錄--教育部統計處 Department of Statistics]{{access | date=2019-10-13}}</ref>

* integer: 4(university) ~ 6

* ex: 0001(國立政治大學)、373607(臺北市立華江國小)。前面可能有零。

* range/limit:

=== 地址 ===

* 資料類型: {{kbd | key=TEXT}} or {{kbd | key=VARCHAR(255)}}<ref>[https://stackoverflow.com/questions/354763/common-mysql-fields-and-their-appropriate-data-types address]</ref>

* 參考別人: 觀察 Google payment 付款地址的文字輸入框並沒有限制文字長度

=== 經緯度 (經度,緯度) ===

* DECIMAL(18,12)<ref>[http://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-for-latitude-longitude mysql - What is the ideal data type for latitude / longitude? - Stack Overflow]</ref> or FLOAT( 10, 6 )<ref>[http://code.google.com/apis/maps/articles/phpsqlsearch.html Creating a Store Locator with PHP, MySQL & Google Maps - Google Maps API Family - Google Code]</ref> or VARCHAR( 30 )

* ex: 37.401724,-122.114646

* range/limit:

* range/limit: 「緯度座標的整數須介於 -90 和 90 之間。經度座標的整數須介於 -180 和 180 之間。<ref>[https://support.google.com/maps/answer/18539?co=GENIE.Platform%3DDesktop&hl=zh-Hant 找出或輸入經緯度 - 電腦 - Google 地圖說明]</ref>」

=== 價錢/金額 ===

Line 74:

Line 83:

=== 密碼 ===

* varchar at least eight (8) characters<ref>[https://www.owasp.org/index.php/Password_length_%26_complexity Password length & complexity - OWASP] "Minimum length. Passwords should be at least eight (8) characters long." </ref>

=== 雜湊碼 (hash value) e.g. MD5, SHA ===

'''Hash Algorithm Output and Schema Reference'''

<ref>[https://en.wikipedia.org/wiki/Secure_Hash_Algorithms Secure Hash Algorithms - Wikipedia]</ref>

<th style="text-align: left;">Algorithm</th>

<th style="text-align: left;">Output Size (bits)</th>

<th style="text-align: left;">Max Length (chars)</th>

<th style="text-align: left;">Schema Recommendation (Text)</th>

<th style="text-align: left;">Schema Recommendation (Binary)</th>

</tr>

<tr>

<td>binary(16)</td>

</tr>

<tr>

<td>binary(20)</td>

</tr>

<tr>

<td>binary(28)</td>

</tr>

<tr>

<td>binary(32)</td>

</tr>

<tr>

<td>binary(48)</td>

</tr>

<tr>

<td>binary(64)</td>

</tr>

</table>

Explain the conversion process from 224 bits to 56 characters for SHA-224's output. Conversion process:

* Original output: 224 bits

* '''Text Representation (Hexadecimal)''': Every 4 bits converts to 1 hexadecimal character (hex character): 224 ÷ 4 = 56 Therefore, it requires 56 hexadecimal characters to represent

* '''Binary Storage Representation''': Every 8 bits equals 1 byte in binary storage: 224 ÷ 8 = 28 Therefore, it requires 28 bytes (binary(28)) to store efficiently

[https://zh.wikipedia.org/wiki/MD5 MD5]: {{exclaim}} Not recommended to use this function to secure passwords

* Data type: CHAR(32) or VARCHAR(32)<ref>[https://stackoverflow.com/questions/14922208/can-i-use-varchar32-for-md5-values php - Can I use VARCHAR(32) for md5() values? - Stack Overflow]</ref>

* Framework: MySQL [https://www.w3resource.com/mysql/encryption-and-compression-functions/md5().php md5()], [https://www.php.net/manual/en/function.md5.php PHP: md5 - Manual]

[https://zh.wikipedia.org/wiki/SHA%E5%AE%B6%E6%97%8F SHA]<ref>[https://stackoverflow.com/questions/2240973/how-long-is-the-sha256-hash mysql - How long is the SHA256 hash? - Stack Overflow]</ref><ref>[http://fishjerky.blogspot.com/2013/06/md5sha512.html 魚乾的筆記本: MD5被破解了，要改用SHA]</ref>:

* (1) HEX: CHAR(64) Using [http://php.net/manual/en/function.hash-file.php PHP: hash_file()] or [https://www.php.net/manual/en/function.hash.php PHP: hash()], MySQL [https://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_sha2 SHA2(str, hash_length)]

* (2) Binary: BINARY(32) In PHP using [http://php.net/manual/en/function.hex2bin.php hex2bin()] e.g. {{kbd | key=<nowiki>echo hex2bin(hash_file('sha256', $file_path));</nowiki>}}, In MySQL using [https://www.w3resource.com/mysql/string-functions/mysql-unhex-function.php UNHEX() function] e.g. {{kbd | key=<nowiki>SELECT UNHEX(SHA2('The quick brown fox jumped over the lazy dog.', 256))</nowiki>}}

[https://zh.wikipedia.org/zh-tw/SHA-1 SHA-1] (Secure Hash Algorithm 1) {{exclaim}} Not recommended to use this function to secure passwords

* Data type:

** CHAR(40) - The hexadecimal representation of a SHA-1 hash consists of 40 characters. SHA-1 is a 160-bit hash function, resulting in 160 binary bits. Since each hexadecimal character corresponds to 4 binary bits, 40 hexadecimal characters are needed to represent a complete SHA-1 hash.

** BINARY(20) - If stored in binary format, a SHA-1 hash has a length of 20 bytes. Each byte contains 8 binary bits. Therefore, the BINARY(20) data type is used to store the binary representation of a SHA-1 hash.

* Framework: MySQL [https://www.w3resource.com/mysql/encryption-and-compression-functions/sha1().php SHA1() function], [https://www.php.net/manual/en/function.sha1.php PHP: sha1 - Manual]

SHA-256 ([https://zh.wikipedia.org/zh-tw/SHA-2 SHA-2])

* Data type:

** CHAR(64)　- The hexadecimal representation of a SHA-256 hash consists of 64 characters. This is because SHA-256 is a 256-bit hash function, resulting in 256 binary bits. Since each hexadecimal character corresponds to 4 binary bits, 64 hexadecimal characters are needed to represent a complete SHA-256 hash.

** BINARY(32) - If stored in binary format, a SHA-256 hash has a length of 32 bytes. Each byte contains 8 binary bits. Therefore, the BINARY(32) data type is used to store the binary representation of a SHA-256 hash.

* Framework: MySQL [https://dev.mysql.com/doc/refman/8.0/en/sha256-pluggable-authentication.html MySQL :: MySQL 8.0 Reference Manual :: 8.4.1.3 SHA-256 Pluggable Authentication], [https://www.php.net/manual/zh/function.hash.php PHP: hash - Manual]

==== Retrieve the hash value from string or file content ====

PHP:

<pre>

<?php

/* Create a file to calculate hash of */

file_put_contents('example.txt', 'The quick brown fox jumped over the lazy dog.');

echo hash_file('sha256', 'example.txt');

// expected result: 68b1282b91de2c054c36629cb8dd447f12f096d3e3c587978dc2248444633483

// 64 characters

// calculate the hash of string

echo hash('sha256', 'The quick brown fox jumped over the lazy dog.');

?>

</pre>

MySQL:

<pre>

SELECT SHA2('The quick brown fox jumped over the lazy dog.', 256)

</pre>

==== Retrieve the hash value from binary value ====

* PHP: [http://php.net/manual/en/function.bin2hex.php bin2hex()] e.g. {{kbd | key=<nowiki>echo bin2hex(hex2bin(hash('sha256', 'The quick brown fox jumped over the lazy dog.')));</nowiki>}}

* MySQL: [https://dev.mysql.com/doc/refman/8.0/en/hexadecimal-literals.html Hexadecimal Literals] e.g. {{kbd | key=<nowiki>SELECT HEX(UNHEX(SHA2('The quick brown fox jumped over the lazy dog.', 256)));</nowiki>}}<ref>[https://stackoverflow.com/questions/14608413/storing-a-binary-sha1-hash-into-a-mysql-binary20-column insert - Storing a binary SHA1 hash into a mySQL BINARY(20) column - Stack Overflow]</ref>

=== UUID 通用唯一辨識碼、GUID 全域唯一識別碼字串 ===

[https://zh.wikipedia.org/zh-tw/%E9%80%9A%E7%94%A8%E5%94%AF%E4%B8%80%E8%AF%86%E5%88%AB%E7%A0%81 通用唯一辨識碼 - 維基百科，自由的百科全書]、[https://zh.wikipedia.org/zh-tw/%E5%85%A8%E5%B1%80%E5%94%AF%E4%B8%80%E6%A0%87%E8%AF%86%E7%AC%A6 全域唯一識別碼 - 維基百科，自由的百科全書]

* BINARY(16)<ref>[https://stackoverflow.com/questions/412341/how-should-i-store-guid-in-mysql-tables How should I store GUID in MySQL tables? - Stack Overflow]</ref>

* VARCHAR(36) or CHAR(36)

==== Store the GUID value ====

<pre>

SELECT UUID();

-- 4470beb9-ab1e-11ec-bd92-00155de8c33a

INSERT INTO `sometable`

(`guid_char`, `guid_binary`)

VALUES (

'4470beb9-ab1e-11ec-bd92-00155de8c33a',

UNHEX(REPLACE("4470beb9-ab1e-11ec-bd92-00155de8c33a", "-",""))

);

</pre>

{{exclaim}} Need to replace the - symbol with whitespace from GUID value. Or it will not able to retrieve the GUID value

==== Retrieve the GUID value ====

<pre>

SELECT `guid_char`, HEX(`guid_binary`)

FROM `sometable`;

</pre>

=== membership ===

* [http://msdn.microsoft.com/en-us/library/aa478949.aspx Membership Providers]

=== 台灣公司統一編號 ===

統一編號 (Unified Business Number, 簡稱 UBN)

* 數值長度：八位數字

* 建議資料類型：因為可能以 0 開頭，所以建議使用 {{kbd | key= CHAR(8)}}，而不建議使用 {{kbd | key= INT(8)}} <ref>[https://www.etax.nat.gov.tw/etwmain/web/ETW113W1_1 公示資料查詢服務-財政部稅務入口網]</ref><ref>[http://herolin.webhop.me/entry/is-valid-TW-company-ID/ » 營利事業統一編號驗證完全手冊(Javascript,Java,C#,PHP) - Hero Think~用手摀住我的嘴]</ref> {{exclaim}} 早期公司統一編號不是八位數字

=== 台灣身分證號/統一證號 ===

* 最長 10 位文字，所以建議使用 {{kbd | key= CHAR(10)}}：(1)身分證號：1碼英文字母加上9碼數字組成、(2) 統一證號：2碼英文字母加上8碼數字，一共10個字元組成 <ref>[https://www.moi.gov.tw/News_Content.aspx?n=2&s=211965&sms=9009 新式外來人口統一證號(宣導手冊)]</ref>。

=== 商品序號或商品條碼 ===

MySQL: {{kbd | key = VARCHAR(14)}}

* GTIN 全球交易品項號碼([http://en.wikipedia.org/wiki/Global_Trade_Item_Number Global Trade Item Number]) 最長 14 位數

* EAN 國際商品條碼([http://en.wikipedia.org/wiki/International_Article_Number_(EAN) International Article Number])，即前[http://zh.wikipedia.org/wiki/%E6%AC%A7%E6%B4%B2%E5%95%86%E5%93%81%E7%BC%96%E7%A0%81 歐洲商品條碼] (European Article Number) and [http://en.wikipedia.org/wiki/International_Article_Number_(EAN)#jan Japanese Article Number (JAN)] 最長 13 位數

* UPC [http://zh.wikipedia.org/wiki/%E9%80%9A%E7%94%A8%E4%BA%A7%E5%93%81%E4%BB%A3%E7%A0%81 通用產品代碼] ([http://en.wikipedia.org/wiki/Universal_Product_Code Universal Product Code]) 最長 12 位數

* UPC [http://zh.wikipedia.org/wiki/%E9%80%9A%E7%94%A8%E4%BA%A7%E5%93%81%E4%BB%A3%E7%A0%81 通用產品代碼] ([http://en.wikipedia.org/wiki/Universal_Product_Code Universal Product Code]) 最長 12 位數，只允許數字 (UPC-A)

~~網站專屬的商品序號~~

* ASIN [http://zh.wikipedia.org/wiki/%E4%BA%9A%E9%A9%AC%E9%80%8A%E6%A0%87%E5%87%86%E8%AF%86%E5%88%AB%E5%8F%B7%E7%A0%81 亞馬遜標準識別號碼] (Amazon Standard Identification Number) 由十個字元(字母或數字)組成

** MySQL: {{kbd | key = VARCHAR(10)}}

書籍

* ISBN [http://zh.wikipedia.org/wiki/%E5%9B%BD%E9%99%85%E6%A0%87%E5%87%86%E4%B9%A6%E5%8F%B7 國際標準書號]([http://en.wikipedia.org/wiki/International_Standard_Book_Number International Standard Book Number]) 最長 13 位數

=== 國際電話號碼 ===

[https://medium.com/frochu/libphonenumber-example-app-f60680faa599 使用 Google libphonenumber 套件驗證國際電話號碼格式 – Frochu – Medium]

=== 很長的文字 ===

Line 125:

Line 280:

* [http://wiki.ispirer.com/sqlways/sql-server/data-types Microsoft SQL Server - Data Types (Migration by Ispirer SQLWays)] {{access | date = 2015-12-22}}

* [http://www.w3schools.com/sql/sql_datatypes.asp SQL Data Types for MS Access, MySQL, and SQL Server] {{access | date = 2015-12-22}}

~~conversion tools~~

* [https://www.webyog.com/product/sqlyog SQLyog] ''$''

== tools ==

ER圖(entity-relationship diagram)

* [http://www.mysql.com/products/workbench/ MySQL :: MySQL Workbench 6.0]: Notice the installation prerequisites{{exclaim}}

產生資料庫結構表格文件

* [[Create database schema document]]

參考其他資料表的結構設計<ref>[http://dev.mysql.com/doc/refman/5.0/en/getting-information.html MySQL :: MySQL 5.0 Reference Manual :: 3.4 Getting Information About Databases and Tables]</ref>

* {{kbd | key = <nowiki>DESCRIBE table_name;</nowiki>}}

== ~~references ==~~

== Further reading ==

~~<references />~~

~~=== further~~ reading ===

* [http://dev.mysql.com/doc/refman/5.0/en/data-types.html MySQL :: MySQL 5.0 Reference Manual :: 10 Data Types]

Line 145:

Line 297:

* [http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm Oracle Data Types]

* [http://www.databaseanswers.org/data_models/index.htm Industry Data Models]

* [https://dba.stackexchange.com/questions/tagged/database-design Newest 'database-design' Questions - Database Administrators Stack Exchange]

== References ==

[[Category:Web_Dev]] [[Category:Database]] [[Category:MySQL]] [[Category:Data Science]]

[[Category: Web_Dev]]

[[Category: Database]]

[[Category: MySQL]]

[[Category: Data Science]]

[[Category: Revised with LLMs]]

@@ Line 1: / Line 1: @@
-資料表欄位設計時，針對不同資料類型，建議的資料型態(data type)
+資料庫欄位設計時，針對不同資料類型，建議的資料型態 (data type) 與長度
-{{Template:Build a website}}
+== Examples of data and suggested datatype ==
-== examples of data and suggested datatype ==
 === birth year ===
 * data type: int
-* ex: Range from 1905 ~ 2013 (108 yeas old) from the Sing-up form from outlook.com {{access| date=2013-02-04}}
+* example: Range from 1905 ~ 2013 (108 yeas old) from the Sign-up form from outlook.com {{access| date=2013-02-04}}
 * range/limit: 122 yeas old<ref>[http://zh.wikipedia.org/wiki/%E6%9C%80%E5%B9%B4%E9%95%B7%E8%80%85 最年長者 - 維基百科，自由的百科全書]: 「根據金氏世界紀錄大全紀錄的最長壽者是活了122年的雅娜·卡爾曼特」、[https://zh.wikipedia.org/zh-tw/%E5%90%84%E5%9B%BD%E4%BA%BA%E5%8F%A3%E9%A2%84%E6%9C%9F%E5%AF%BF%E5%91%BD%E5%88%97%E8%A1%A8 各國人口預期壽命列表</ref>
@@ Line 15: / Line 13: @@
 === IP ===
 IP(v4)
-* varchar(15)
+* data type: CHAT(15) or VARCHAR(15)
 * ex: 255.255.255.255 <ref>[http://stackoverflow.com/questions/1385552/datatype-for-storing-ip-address-in-sql-server Datatype for storing ip address in SQL Server - Stack Overflow]</ref>
@@ Line 28: / Line 26: @@
 === 網址 ===
-* data type: >= MySQL 5.0.3 use {{kbd | key=VARCHAR(2083)}}<ref>[http://stackoverflow.com/questions/219569/best-database-field-type-for-a-url sql - Best database field type for a URL - Stack Overflow]</ref>; {{kbd | key=nvarchar(2083)}} for MS SQL
+* data type:
-* [http://support.microsoft.com/kb/208427 最大 URL 長度是在 Internet Explorer 中的 2,083 字元] for {{IE}}<ref>
+** MySQL 5.0.3+ use {{kbd | key=VARCHAR(2083)}}<ref>[http://stackoverflow.com/questions/219569/best-database-field-type-for-a-url sql - Best database field type for a URL - Stack Overflow]</ref> 或 {{kbd | key=TEXT}}
-* [http://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url-in-different-browsers http - What is the maximum length of a URL in different browsers? - Stack Overflow]
+** MS SQL: {{kbd | key=nvarchar(2083)}}
-* [http://wiert.me/2010/04/20/maximum-url-lengths/ Maximum URL lengths « The Wiert Corner – irregular stream of stuff]
-</ref>
+參考資料
+* [http://support.microsoft.com/kb/208427 最大 URL 長度是在 Internet Explorer 中的 2,083 字元] for {{IE}}<ref>[http://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url-in-different-browsers http - What is the maximum length of a URL in different browsers? - Stack Overflow]</ref><ref>[http://wiert.me/2010/04/20/maximum-url-lengths/ Maximum URL lengths « The Wiert Corner – irregular stream of stuff]</ref>
-=== Chinese name ===
+=== Email ===
-* 最長姓名有13個字 <ref> 中國新聞網 (2010). [http://dailynews.sina.com/bg/tw/twpolitics/chinanews/20101104/00481967448.html 台灣“獨一姓氏”者達149人 最長姓名有13個字_台灣頻道_新浪網-北美]</ref><ref>[http://news.ltn.com.tw/news/life/paper/780935 姓名8個字 外交部不給護照 - 生活 - 自由時報電子報]</ref>
+* data type:
-* 參考別人: LINE姓名欄位長度限制: 15字(15個中文字或15英文字母)
+** MySQL: {{kbd | key=VARCHAR(254)}}<ref>[https://www.rfc-editor.org/rfc/rfc5321 RFC 5321: Simple Mail Transfer Protocol]"4.5.3.1.3.  Path The maximum total length of a reverse-path or forward-path is 256 octets (including the punctuation and element separators)." Complete address in SMTP envelope (including {{kbd | key=<nowiki><...></nowiki>}})</ref>
+* 遵循 [https://www.rfc-editor.org/rfc/rfc5321 RFC 5321: Simple Mail Transfer Protocol] 規格
+=== 姓名欄位 ===
+* 資料類型：{{Gd}} {{kbd | key=<nowiki>CHAR(50)</nowiki>}}、{{kbd | key=<nowiki>VARCHAR(50)</nowiki>}}
+* 參考資料：「惟戶政系統中，欄位最多填寫50字，成為名字長度的天花板。」<ref>[https://tw.news.yahoo.com/%E6%94%B9%E5%90%8D%E5%AD%97%E6%95%B8%E7%84%A1%E4%B8%8A%E9%99%90-%E6%88%B6%E6%94%BF%E5%8F%B8-%E6%9C%83%E5%BF%B5%E5%BE%88%E4%B9%85-063953310.html 改名字數無上限 戶政司：會念很久]</ref><ref> 中國新聞網 (2010). [http://dailynews.sina.com/bg/tw/twpolitics/chinanews/20101104/00481967448.html 台灣“獨一姓氏”者達149人 最長姓名有13個字_台灣頻道_新浪網-北美]</ref><ref>[http://news.ltn.com.tw/news/life/paper/780935 姓名8個字 外交部不給護照 - 生活 - 自由時報電子報]</ref>
+* 其他人作法: LINE 姓名欄位長度限制: 15字(15個中文字或15英文字母)
 === unix timestamp ===
@@ Line 49: / Line 54: @@
 === 學校代碼 ===
-School ID defined by MOE at Taiwan / 學校代碼<ref>[http://www.edu.tw/statistics/content.aspx?site_content_sn=25656 各級學校名錄--教育部統計處 Department of Statistics]{{access | date=2012-02-16}}</ref>
+School ID defined by MOE at Taiwan / 學校代碼<ref>[https://depart.moe.edu.tw/ED4500/News.aspx?n=63F5AB3D02A8BBAC&sms=1FF9979D10DBF9F3 各級學校名錄--教育部統計處 Department of Statistics]{{access | date=2019-10-13}}</ref>
 * integer: 4(university) ~ 6
 * ex: 0001(國立政治大學)、373607(臺北市立華江國小)。前面可能有零。
 * range/limit:
+=== 地址 ===
+* 資料類型: {{kbd | key=TEXT}} or {{kbd | key=VARCHAR(255)}}<ref>[https://stackoverflow.com/questions/354763/common-mysql-fields-and-their-appropriate-data-types address]</ref>
+* 參考別人: 觀察 Google payment 付款地址的文字輸入框並沒有限制文字長度
 === 經緯度 (經度,緯度) ===
 * DECIMAL(18,12)<ref>[http://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-for-latitude-longitude mysql - What is the ideal data type for latitude / longitude? - Stack Overflow]</ref> or FLOAT( 10, 6 )<ref>[http://code.google.com/apis/maps/articles/phpsqlsearch.html Creating a Store Locator with PHP, MySQL & Google Maps - Google Maps API Family - Google Code]</ref> or VARCHAR( 30 )
 * ex: 37.401724,-122.114646
-* range/limit:
+* range/limit: 「緯度座標的整數須介於 -90 和 90 之間。經度座標的整數須介於 -180 和 180 之間。<ref>[https://support.google.com/maps/answer/18539?co=GENIE.Platform%3DDesktop&hl=zh-Hant 找出或輸入經緯度 - 電腦 - Google 地圖說明]</ref>」
 === 價錢/金額 ===
@@ Line 74: / Line 83: @@
 === 密碼 ===
 * varchar at least eight (8) characters<ref>[https://www.owasp.org/index.php/Password_length_%26_complexity Password length & complexity - OWASP] "Minimum length. Passwords should be at least eight (8) characters long." </ref>
+=== 雜湊碼 (hash value) e.g. MD5, SHA ===
+'''Hash Algorithm Output and Schema Reference'''
+<ref>[https://en.wikipedia.org/wiki/Secure_Hash_Algorithms Secure Hash Algorithms - Wikipedia]</ref>
+<table border="1" class="wikitable sortable" style="border-collapse: collapse; width: 100%;">
+       <tr style="background-color: #f2f2f2;">
+           <th style="text-align: left;">Algorithm</th>
+           <th style="text-align: left;">Output Size (bits)</th>
+           <th style="text-align: left;">Max Length (chars)</th>
+           <th style="text-align: left;">Schema Recommendation (Text)</th>
+           <th style="text-align: left;">Schema Recommendation (Binary)</th>
+       </tr>
+       <tr>
+           <td>MD5</td>
+           <td>128</td>
+           <td>32</td>
+           <td>char(32)</td>
+           <td>binary(16)</td>
+       </tr>
+       <tr>
+           <td>SHA-1</td>
+           <td>160</td>
+           <td>40</td>
+           <td>char(40)</td>
+           <td>binary(20)</td>
+       </tr>
+       <tr>
+           <td>SHA-224</td>
+           <td>224</td>
+           <td>56</td>
+           <td>char(56)</td>
+           <td>binary(28)</td>
+       </tr>
+       <tr>
+           <td>SHA-256</td>
+           <td>256</td>
+           <td>64</td>
+           <td>char(64)</td>
+           <td>binary(32)</td>
+       </tr>
+       <tr>
+           <td>SHA-384</td>
+           <td>384</td>
+           <td>96</td>
+           <td>char(96)</td>
+           <td>binary(48)</td>
+       </tr>
+       <tr>
+           <td>SHA-512</td>
+           <td>512</td>
+           <td>128</td>
+           <td>char(128)</td>
+           <td>binary(64)</td>
+       </tr>
+</table>
+Explain the conversion process from 224 bits to 56 characters for SHA-224's output. Conversion process:
+* Original output: 224 bits
+* '''Text Representation (Hexadecimal)''': Every 4 bits converts to 1 hexadecimal character (hex character): 224 ÷ 4 = 56 Therefore, it requires 56 hexadecimal characters to represent
+* '''Binary Storage Representation''': Every 8 bits equals 1 byte in binary storage: 224 ÷ 8 = 28 Therefore, it requires 28 bytes (binary(28)) to store efficiently
+[https://zh.wikipedia.org/wiki/MD5 MD5]: {{exclaim}} Not recommended to use this function to secure passwords
+* Data type: CHAR(32) or VARCHAR(32)<ref>[https://stackoverflow.com/questions/14922208/can-i-use-varchar32-for-md5-values php - Can I use VARCHAR(32) for md5() values? - Stack Overflow]</ref>
+* Framework: MySQL [https://www.w3resource.com/mysql/encryption-and-compression-functions/md5().php md5()], [https://www.php.net/manual/en/function.md5.php PHP: md5 - Manual]
+[https://zh.wikipedia.org/wiki/SHA%E5%AE%B6%E6%97%8F SHA]<ref>[https://stackoverflow.com/questions/2240973/how-long-is-the-sha256-hash mysql - How long is the SHA256 hash? - Stack Overflow]</ref><ref>[http://fishjerky.blogspot.com/2013/06/md5sha512.html 魚乾的筆記本: MD5被破解了，要改用SHA]</ref>:
+* (1) HEX: CHAR(64) Using [http://php.net/manual/en/function.hash-file.php PHP: hash_file()] or [https://www.php.net/manual/en/function.hash.php PHP: hash()], MySQL [https://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_sha2 SHA2(str, hash_length)]
+* (2) Binary: BINARY(32) In PHP using [http://php.net/manual/en/function.hex2bin.php hex2bin()] e.g. {{kbd | key=<nowiki>echo hex2bin(hash_file('sha256', $file_path));</nowiki>}}, In MySQL using [https://www.w3resource.com/mysql/string-functions/mysql-unhex-function.php UNHEX() function] e.g. {{kbd | key=<nowiki>SELECT UNHEX(SHA2('The quick brown fox jumped over the lazy dog.', 256))</nowiki>}}
+[https://zh.wikipedia.org/zh-tw/SHA-1 SHA-1] (Secure Hash Algorithm 1) {{exclaim}} Not recommended to use this function to secure passwords
+* Data type:
+** CHAR(40) - The hexadecimal representation of a SHA-1 hash consists of 40 characters. SHA-1 is a 160-bit hash function, resulting in 160 binary bits. Since each hexadecimal character corresponds to 4 binary bits, 40 hexadecimal characters are needed to represent a complete SHA-1 hash.
+** BINARY(20) - If stored in binary format, a SHA-1 hash has a length of 20 bytes. Each byte contains 8 binary bits. Therefore, the BINARY(20) data type is used to store the binary representation of a SHA-1 hash.
+* Framework: MySQL [https://www.w3resource.com/mysql/encryption-and-compression-functions/sha1().php SHA1() function], [https://www.php.net/manual/en/function.sha1.php PHP: sha1 - Manual]
+SHA-256 ([https://zh.wikipedia.org/zh-tw/SHA-2 SHA-2])
+* Data type:
+** CHAR(64)　- The hexadecimal representation of a SHA-256 hash consists of 64 characters. This is because SHA-256 is a 256-bit hash function, resulting in 256 binary bits. Since each hexadecimal character corresponds to 4 binary bits, 64 hexadecimal characters are needed to represent a complete SHA-256 hash.
+** BINARY(32) - If stored in binary format, a SHA-256 hash has a length of 32 bytes. Each byte contains 8 binary bits. Therefore, the BINARY(32) data type is used to store the binary representation of a SHA-256 hash.
+* Framework: MySQL [https://dev.mysql.com/doc/refman/8.0/en/sha256-pluggable-authentication.html MySQL :: MySQL 8.0 Reference Manual :: 8.4.1.3 SHA-256 Pluggable Authentication], [https://www.php.net/manual/zh/function.hash.php PHP: hash - Manual]
+==== Retrieve the hash value from string or file content ====
+PHP:
+<pre>
+<?php
+/* Create a file to calculate hash of */
+file_put_contents('example.txt', 'The quick brown fox jumped over the lazy dog.');
+echo hash_file('sha256', 'example.txt');
+// expected result: 68b1282b91de2c054c36629cb8dd447f12f096d3e3c587978dc2248444633483
+// 64 characters
+// calculate the hash of string
+echo hash('sha256', 'The quick brown fox jumped over the lazy dog.');
+?>
+</pre>
+MySQL:
+<pre>
+SELECT SHA2('The quick brown fox jumped over the lazy dog.', 256)
+</pre>
+==== Retrieve the hash value from binary value ====
+* PHP: [http://php.net/manual/en/function.bin2hex.php bin2hex()] e.g. {{kbd | key=<nowiki>echo bin2hex(hex2bin(hash('sha256', 'The quick brown fox jumped over the lazy dog.')));</nowiki>}}
+* MySQL: [https://dev.mysql.com/doc/refman/8.0/en/hexadecimal-literals.html Hexadecimal Literals] e.g. {{kbd | key=<nowiki>SELECT HEX(UNHEX(SHA2('The quick brown fox jumped over the lazy dog.', 256)));</nowiki>}}<ref>[https://stackoverflow.com/questions/14608413/storing-a-binary-sha1-hash-into-a-mysql-binary20-column insert - Storing a binary SHA1 hash into a mySQL BINARY(20) column - Stack Overflow]</ref>
+=== UUID 通用唯一辨識碼、GUID 全域唯一識別碼字串 ===
+[https://zh.wikipedia.org/zh-tw/%E9%80%9A%E7%94%A8%E5%94%AF%E4%B8%80%E8%AF%86%E5%88%AB%E7%A0%81 通用唯一辨識碼 - 維基百科，自由的百科全書]、[https://zh.wikipedia.org/zh-tw/%E5%85%A8%E5%B1%80%E5%94%AF%E4%B8%80%E6%A0%87%E8%AF%86%E7%AC%A6 全域唯一識別碼 - 維基百科，自由的百科全書]
+* BINARY(16)<ref>[https://stackoverflow.com/questions/412341/how-should-i-store-guid-in-mysql-tables How should I store GUID in MySQL tables? - Stack Overflow]</ref>
+* VARCHAR(36) or CHAR(36)
+==== Store the GUID value ====
+<pre>
+SELECT UUID();
+-- 4470beb9-ab1e-11ec-bd92-00155de8c33a
+INSERT INTO `sometable`
+(`guid_char`, `guid_binary`)
+VALUES (
+'4470beb9-ab1e-11ec-bd92-00155de8c33a',
+UNHEX(REPLACE("4470beb9-ab1e-11ec-bd92-00155de8c33a", "-",""))
+);
+</pre>
+{{exclaim}} Need to replace the - symbol with whitespace from GUID value. Or it will not able to retrieve the GUID value
+==== Retrieve the GUID value ====
+<pre>
+SELECT `guid_char`, HEX(`guid_binary`)
+FROM `sometable`;
+</pre>
 === membership ===
 * [http://msdn.microsoft.com/en-us/library/aa478949.aspx Membership Providers]
+=== 台灣公司統一編號 ===
+統一編號 (Unified Business Number, 簡稱 UBN)
+* 數值長度：八位數字
+* 建議資料類型：因為可能以 0 開頭，所以建議使用 {{kbd | key= CHAR(8)}}，而不建議使用 {{kbd | key= INT(8)}} <ref>[https://www.etax.nat.gov.tw/etwmain/web/ETW113W1_1 公示資料查詢服務-財政部稅務入口網]</ref><ref>[http://herolin.webhop.me/entry/is-valid-TW-company-ID/ » 營利事業統一編號驗證完全手冊(Javascript,Java,C#,PHP) - Hero Think~用手摀住我的嘴]</ref> {{exclaim}} 早期公司統一編號不是八位數字
+=== 台灣身分證號/統一證號 ===
+* 最長 10 位文字，所以建議使用 {{kbd | key= CHAR(10)}}：(1)身分證號：1碼英文字母加上9碼數字組成、(2) 統一證號：2碼英文字母加上8碼數字，一共10個字元組成  <ref>[https://www.moi.gov.tw/News_Content.aspx?n=2&s=211965&sms=9009 新式外來人口統一證號(宣導手冊)]</ref>。
 === 商品序號或商品條碼 ===
+MySQL: {{kbd | key = VARCHAR(14)}}
 * GTIN 全球交易品項號碼([http://en.wikipedia.org/wiki/Global_Trade_Item_Number Global Trade Item Number])  最長 14 位數
 * EAN 國際商品條碼([http://en.wikipedia.org/wiki/International_Article_Number_(EAN) International Article Number])，即前[http://zh.wikipedia.org/wiki/%E6%AC%A7%E6%B4%B2%E5%95%86%E5%93%81%E7%BC%96%E7%A0%81 歐洲商品條碼] (European Article Number) and [http://en.wikipedia.org/wiki/International_Article_Number_(EAN)#jan Japanese Article Number (JAN)] 最長 13 位數
-* UPC [http://zh.wikipedia.org/wiki/%E9%80%9A%E7%94%A8%E4%BA%A7%E5%93%81%E4%BB%A3%E7%A0%81 通用產品代碼] ([http://en.wikipedia.org/wiki/Universal_Product_Code Universal Product Code]) 最長 12 位數
+* UPC [http://zh.wikipedia.org/wiki/%E9%80%9A%E7%94%A8%E4%BA%A7%E5%93%81%E4%BB%A3%E7%A0%81 通用產品代碼] ([http://en.wikipedia.org/wiki/Universal_Product_Code Universal Product Code]) 最長 12 位數，只允許數字 (UPC-A)
-網站專屬的商品序號
 * ASIN [http://zh.wikipedia.org/wiki/%E4%BA%9A%E9%A9%AC%E9%80%8A%E6%A0%87%E5%87%86%E8%AF%86%E5%88%AB%E5%8F%B7%E7%A0%81 亞馬遜標準識別號碼] (Amazon Standard Identification Number) 由十個字元(字母或數字)組成
-** MySQL: {{kbd | key = VARCHAR(10)}}
 書籍
 * ISBN [http://zh.wikipedia.org/wiki/%E5%9B%BD%E9%99%85%E6%A0%87%E5%87%86%E4%B9%A6%E5%8F%B7 國際標準書號]([http://en.wikipedia.org/wiki/International_Standard_Book_Number International Standard Book Number])  最長 13 位數
+=== 國際電話號碼 ===
+[https://medium.com/frochu/libphonenumber-example-app-f60680faa599 使用 Google libphonenumber 套件驗證國際電話號碼格式 – Frochu – Medium]
 === 很長的文字 ===
@@ Line 125: / Line 280: @@
 * [http://wiki.ispirer.com/sqlways/sql-server/data-types Microsoft SQL Server - Data Types (Migration by Ispirer SQLWays)] {{access | date = 2015-12-22}}
 * [http://www.w3schools.com/sql/sql_datatypes.asp SQL Data Types for MS Access, MySQL, and SQL Server] {{access | date = 2015-12-22}}
-conversion tools
-* [https://www.webyog.com/product/sqlyog SQLyog] ''$''
 == tools ==
 ER圖(entity-relationship diagram)
 * [http://www.mysql.com/products/workbench/ MySQL :: MySQL Workbench 6.0]: Notice the installation prerequisites{{exclaim}}
+產生資料庫結構表格文件
+* [[Create database schema document]]
 參考其他資料表的結構設計<ref>[http://dev.mysql.com/doc/refman/5.0/en/getting-information.html MySQL :: MySQL 5.0 Reference Manual :: 3.4 Getting Information About Databases and Tables]</ref>
 * {{kbd | key = <nowiki>DESCRIBE table_name;</nowiki>}}
-== references ==
+== Further reading ==
-<references />
-=== further reading ===
 * [http://dev.mysql.com/doc/refman/5.0/en/data-types.html MySQL :: MySQL 5.0 Reference Manual :: 10 Data Types]
@@ Line 145: / Line 297: @@
 * [http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm Oracle Data Types]
 * [http://www.databaseanswers.org/data_models/index.htm Industry Data Models]
+* [https://dba.stackexchange.com/questions/tagged/database-design Newest 'database-design' Questions - Database Administrators Stack Exchange]
+== References ==
+<references />
+{{Template:Build a website}}
-[[Category:Web_Dev]] [[Category:Database]] [[Category:MySQL]] [[Category:Data Science]]
+[[Category: Web_Dev]]
+[[Category: Database]]
+[[Category: MySQL]]
+[[Category: Data Science]]
+[[Category: Revised with LLMs]]