Editing Data type (section)

== Examples of data and suggested datatype ==
=== birth year ===
* data type: int
* 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>

=== file name ===
file name for {{Win}} FTFS ; {{Linux}} ex3 and ex4 file system types<ref>Type {{kbd | key = <nowiki>df -aT</nowiki>}} to list the file system types in Linux</ref>.
* varchar(255) <ref>[https://en.wikipedia.org/wiki/Comparison_of_file_systems#Limits Comparison of file systems - Wikipedia, the free encyclopedia]</ref><ref>[http://msdn.microsoft.com/en-us/library/aa365247.aspx Naming Files, Paths, and Namespaces (Windows)]</ref>  {{exclaim}} If the files hosted by Windows server, you should also take the '''limit of PATH length''' into account besides the limit of filename length.

=== IP ===
IP(v4)
* 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>

range/limit
* IP range - Classless inter-domain routing (CIDR)<ref> [http://www.mediawiki.org/wiki/Help:Range_blocks Help:Range blocks - MediaWiki] </ref>
** 12 or 24 bytes (IPv4 and IPv6 networks)<ref> [http://www.postgresql.org/docs/8.2/static/datatype-net-types.html PostgreSQL: Documentation: Manuals: Network Address Types] </ref>
** ex: 69.208.0.0/32

IPv6: [http://zh.wikipedia.org/wiki/IPv6 128位元長度，以16位元為一組，每組以冒號":"隔開，可以分為8組，每組以4位元十六進制方式表示]
** data type:
** ex: 2001:0db8:85a3:08d3:1319:8a2e:0370:7344

=== 網址 ===
* 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=TEXT}}
** MS SQL: {{kbd | key=nvarchar(2083)}}

參考資料
* [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>

=== Email ===
* data type: 
** 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 ===
* bigint(10) ; 
* ex: 1328664539
* range/limit:

=== duration 時長 ===
* data type: {{kbd | key= TIME}} 「{{kbd | key= TIME}} values may range from '-838:59:59' to '838:59:59'」<ref>[http://dev.mysql.com/doc/refman/5.0/en/time.html MySQL :: MySQL 5.0 Reference Manual :: 11.3.2 The TIME Type]</ref>
* format: 小時:分鐘:秒 hh:mm:ss
* 參考別人: Google form 問卷題目選項的小時 0~72 ; 分鐘: 00~59: 秒: 00~59

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

=== 價錢/金額 ===
* DECIMAL(10,2)<ref> [http://stackoverflow.com/questions/628637/best-data-type-for-currency sql - Best Data Type for Currency - Stack Overflow]{{access | date=2012-03-07}} </ref><ref>[http://zhidao.baidu.com/question/682060470049185972.html mysql 用什么数据类型表示价格？_百度知道]{{access | date=2015-05-25}}  </ref>
* ex: 
* range/limit: 0.00 ~ 99999999.99

=== 台灣縣市欄位值 ===
台灣縣市欄位值 下拉式選單<ref>取自[http://www.post.gov.tw/post/internet/f_searchzone/index.jsp?ID=190103 中華郵政全球資訊網]</ref>
<pre>
$city = array("基隆市", "台北市", "新北市", "桃園縣", "新竹市", "新竹縣", "苗栗縣", "台中市", 
        "彰化縣", "南投縣", "雲林縣", "嘉義市", "嘉義縣", "台南市", "高雄市", "屏東縣", "台東縣", "花蓮縣", 
        "宜蘭縣", "澎湖縣", "金門縣", "連江縣");
</pre>

=== 密碼 ===
* 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-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) 由十個字元(字母或數字)組成

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

=== 很長的文字 ===
* MySQL: {{kbd | key= LONGTEXT}}: 4GB / {{kbd | key = TEXT}}: 65,535 bytes ~64kb <ref>[http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html MySQL :: MySQL 5.7 Reference Manual :: 11.1.3 String Type Overview]</ref><ref>[http://blog.longwin.com.tw/2009/10/mysql-text-field-type-length-limit-2009/ MySQL TEXT 格式 的 長度限制 - Tsung's Blog]</ref>
* MsSQL: {{kbd | key = NVARCHAR(max)}}: 2GB <ref>[http://technet.microsoft.com/en-us/library/ms186939.aspx nchar and nvarchar (Transact-SQL)]</ref>

<pre> (left blank intentionally)
* data
** data type
** ex:
** range/limit:
</pre>