Data type: Difference between revisions

Jump to navigation Jump to search

Data type (edit)

Revision as of 15:49, 4 November 2025

6,348 bytes added ,  4 November 2025
m
→‎台灣公司統一編號
mNo edit summary
 
(32 intermediate revisions by the same user not shown)
Line 1: Line 1:
資料表欄位設計時，針對不同資料類型，建議的資料型態 (data type)
資料庫欄位設計時，針對不同資料類型，建議的資料型態 (data type) 與長度


== examples of data and suggested datatype ==
== Examples of data and suggested datatype ==
=== birth year ===
=== birth year ===
* data type: int
* data type: int
Line 13: Line 13:
=== IP ===
=== IP ===
IP(v4)
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>
* 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 26: 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>


=== Chinese name ===
參考資料
* 最長姓名有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>
* [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>
* 參考別人: LINE姓名欄位長度限制: 15字(15個中文字或15英文字母)
 
=== 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 ===
=== unix timestamp ===
Line 78: Line 85:


=== 雜湊碼 (hash value) e.g. MD5, SHA ===
=== 雜湊碼 (hash value) e.g. MD5, SHA ===
[https://zh.wikipedia.org/wiki/MD5 MD5]:
 
* 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>
'''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>:  
[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()], MySQL [https://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_sha2 SHA2(str, hash_length)]
* (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>}}
* (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>}}
* (3) CHAR(60) if using the MySQL [https://www.w3resource.com/mysql/encryption-and-compression-functions/sha1().php SHA1() function]
 
[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 ====
==== Retrieve the hash value from string or file content ====
Line 111: Line 192:
* 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>}}
* 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>
* 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 ===
=== membership ===
Line 116: Line 224:


=== 台灣公司統一編號 ===
=== 台灣公司統一編號 ===
* 八位數字 因為可能以 0 開頭，所以建議使用 {{kbd | key= VARCHAR(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>
統一編號 (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= VARCHAR(10)}}：(1)身分證號：1碼英文字母加上9碼數字組成、(2) 統一證號：2碼英文字母加上8碼數字，一共10個字元組成  <ref>[https://www.moi.gov.tw/News_Content.aspx?n=2&s=211965&sms=9009 新式外來人口統一證號(宣導手冊)]</ref>。
* 最長 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>。


=== 商品序號或商品條碼 ===
=== 商品序號或商品條碼 ===
Line 180: Line 291:
* {{kbd | key = <nowiki>DESCRIBE table_name;</nowiki>}}
* {{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]
* [http://dev.mysql.com/doc/refman/5.0/en/data-types.html MySQL :: MySQL 5.0 Reference Manual :: 10 Data Types]
Line 189: Line 297:
* [http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm Oracle Data Types]
* [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]
* [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 />




Line 194: Line 307:




[[Category:Web_Dev]] [[Category:Database]] [[Category:MySQL]] [[Category:Data Science]]
[[Category: Web_Dev]]  
[[Category: Database]]  
[[Category: MySQL]]  
[[Category: Data Science]]
[[Category: Revised with LLMs]]
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/22744...26064"

Navigation menu