Editing Data type

Jump to navigation Jump to search

Warning: You are not logged in. Your IP address will be publicly visible if you make any edits. If you log in or create an account, your edits will be attributed to your username, along with other benefits.

The edit can be undone. Please check the comparison below to verify that this is what you want to do, and then save the changes below to finish undoing the edit.

Latest revision Your text
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 ===
 
=== birth year ===
 
* data type: int
 
* data type: int
* example: Range from 1905 ~ 2013 (108 yeas old) from the Sign-up form from outlook.com {{access| date=2013-02-04}}
+
* ex: Range from 1905 ~ 2013 (108 yeas old) from the Sing-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>
 
* 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 33: Line 35:
  
 
=== Chinese name ===
 
=== 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>
+
** 最長姓名有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>
* 參考別人: LINE姓名欄位長度限制: 15字(15個中文字或15英文字母)
+
** 參考別人: LINE姓名欄位長度限制: 15字(15個中文字或15英文字母)
  
 
=== unix timestamp ===
 
=== unix timestamp ===
Line 42: Line 44:
  
 
=== duration 時長 ===
 
=== 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>
+
* data type: {{kbd | key= TIME}} 「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
 
* format: 小時:分鐘:秒 hh:mm:ss
 
* 參考別人: Google form 問卷題目選項的小時 0~72 ; 分鐘: 00~59: 秒: 00~59
 
* 參考別人: 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>
+
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>
 
* integer: 4(university) ~ 6
 
* integer: 4(university) ~ 6
 
* ex: 0001(國立政治大學)、373607(臺北市立華江國小)。前面可能有零。
 
* ex: 0001(國立政治大學)、373607(臺北市立華江國小)。前面可能有零。
* range/limit:
+
* 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 )
+
* 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>
 
* ex: 37.401724,-122.114646
 
* 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>」
+
* range/limit:
  
 
=== 價錢/金額 ===
 
=== 價錢/金額 ===
* 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>
+
* 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>
 
* ex:  
 
* ex:  
 
* range/limit: 0.00 ~ 99999999.99
 
* range/limit: 0.00 ~ 99999999.99
Line 76: Line 74:
 
=== 密碼 ===
 
=== 密碼 ===
 
* 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>
 
* 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 ===
 
* [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>
 
* [https://zh.wikipedia.org/wiki/SHA%E5%AE%B6%E6%97%8F SHA] 256<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)]
 
** (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>}}
 
 
==== 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>
 
  
 
=== membership ===
 
=== membership ===
 
* [http://msdn.microsoft.com/en-us/library/aa478949.aspx Membership Providers]
 
* [http://msdn.microsoft.com/en-us/library/aa478949.aspx Membership Providers]
 
=== 台灣公司統一編號 ===
 
* 八位數字 因為可能以 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>
 
 
=== 台灣身分證號/統一證號 ===
 
* 最長 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>。
 
  
 
=== 商品序號或商品條碼 ===
 
=== 商品序號或商品條碼 ===
MySQL: {{kbd | key = VARCHAR(14)}}
 
 
* GTIN 全球交易品項號碼([http://en.wikipedia.org/wiki/Global_Trade_Item_Number Global Trade Item Number])  最長 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 位數
 
* 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)
+
* 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 位數
 +
 
 +
網站專屬的商品序號
 
* 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) 由十個字元(字母或數字)組成
 
* 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 位數
 
* 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>
+
* MySQL: {{kbd | key = TEXT}}<ref>[http://blog.longwin.com.tw/2009/10/mysql-text-field-type-length-limit-2009/ MySQL TEXT 格式 的 長度限制 - Tsung's Blog]</ref><ref>[http://dev.mysql.com/doc/refman/5.0/en/blob.html MySQL :: MySQL 5.0 Reference Manual :: 11.1.6.3 The BLOB and TEXT Types]</ref>
* MsSQL: {{kbd | key = NVARCHAR(max)}}: 2GB <ref>[http://technet.microsoft.com/en-us/library/ms186939.aspx nchar and nvarchar (Transact-SQL)]</ref>
+
* MsSQL: {{kbd | key = NVARCHAR(max)}}<ref>[http://technet.microsoft.com/en-us/library/ms186939.aspx nchar and nvarchar (Transact-SQL)]</ref>
  
 
<pre> (left blank intentionally)
 
<pre> (left blank intentionally)
Line 143: Line 102:
  
 
== comparision of datatypes in different database server ==
 
== comparision of datatypes in different database server ==
=== numeric ===
 
{{kbd | key=bigint}}
 
* MS SQL<ref>[https://msdn.microsoft.com/zh-tw/library/ms187745(v=sql.120).aspx int、bigint、smallint 和 tinyint (Transact-SQL)]</ref> & MySQL<ref>[https://dev.mysql.com/doc/refman/5.5/en/integer-types.html MySQL :: MySQL 5.5 Reference Manual :: 11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT]</ref> are equivalent
 
 
=== text ===
 
 
Storing Unicode text<ref>
 
Storing Unicode text<ref>
 
* [http://trufflepenne.blogspot.tw/2012/10/mysql-sqlservernvarchar.html 松露筆管麵: Mysql 等同SqlServer的nvarchar]
 
* [http://trufflepenne.blogspot.tw/2012/10/mysql-sqlservernvarchar.html 松露筆管麵: Mysql 等同SqlServer的nvarchar]
 
* [http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar sql server - What is the difference between varchar and nvarchar? - Stack Overflow]</ref>
 
* [http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar sql server - What is the difference between varchar and nvarchar? - Stack Overflow]</ref>
* MySQL: {{kbd | key=varchar}} / MS SQL 2008: {{kbd | key=nvarchar}}
+
* MySQL: {{kbd | key=varchar}}
* MySQL: {{kbd | key= LONGTEXT}} (4GB)<ref>[http://dba.stackexchange.com/questions/98268/whats-sql-server-nvarcharmax-equivalent-in-mysql Whats SQL Server NVARCHAR(max) equivalent in MySQL? - Database Administrators Stack Exchange]</ref> / MS SQL 2008: {{kbd | key=nvarchar(max)}} (2GB)<ref>[https://msdn.microsoft.com/zh-tw/library/ms186939(v=sql.120).aspx nchar 和 nvarchar (Transact-SQL)]</ref>
+
* MS SQL 2008: {{kbd | key=nvarchar}}
  
=== date and time ===
 
 
{{kbd | key=DATETIME}}: ex: "2013-06-13 03:33:33"  
 
{{kbd | key=DATETIME}}: ex: "2013-06-13 03:33:33"  
* MS SQL 2008 & MySQL are equivalent
+
* MS SQL 2008, MySQL is equivalent
  
{{kbd | key=TIMESTAMP}}: MS SQL 2008 and MySQL are '''NOT''' equivalent<ref>[http://stackoverflow.com/questions/8665548/sql-server-2008-timestamp-data-type SQL Server 2008 timestamp data type - Stack Overflow]</ref> {{exclaim}}
+
{{kbd | key=TIMESTAMP}}: MS SQL 2008 and MySQL is '''NOT''' equivalent<ref>[http://stackoverflow.com/questions/8665548/sql-server-2008-timestamp-data-type SQL Server 2008 timestamp data type - Stack Overflow]</ref> {{exclaim}}
 
* MS SQL 2008<ref>[http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx timestamp (Transact-SQL)]</ref> ex: 0x00000000000007D3  
 
* MS SQL 2008<ref>[http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx timestamp (Transact-SQL)]</ref> ex: 0x00000000000007D3  
 
* MySQL ex: 2013-06-13 03:33:33
 
* MySQL ex: 2013-06-13 03:33:33
 
further reading
 
* [https://www.mssqltips.com/sqlservertutorial/2203/mysql-to-sql-server-data-type-comparisons/ MySQL to SQL Server Data Type Comparisons]
 
* [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}}
 
  
 
== tools ==
 
== tools ==
 
ER圖(entity-relationship diagram)
 
ER圖(entity-relationship diagram)
 
* [http://www.mysql.com/products/workbench/ MySQL :: MySQL Workbench 6.0]: Notice the installation prerequisites{{exclaim}}
 
* [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>
 
參考其他資料表的結構設計<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>
Line 186: Line 131:
 
* [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]
 
 
{{Template:Build a website}}
 
 
  
 
[[Category:Web_Dev]] [[Category:Database]] [[Category:MySQL]] [[Category:Data Science]]
 
[[Category:Web_Dev]] [[Category:Database]] [[Category:MySQL]] [[Category:Data Science]]

Please note that all contributions to LemonWiki共筆 are considered to be released under the Creative Commons Attribution-NonCommercial-ShareAlike (see LemonWiki:Copyrights for details). If you do not want your writing to be edited mercilessly and redistributed at will, then do not submit it here.
You are also promising us that you wrote this yourself, or copied it from a public domain or similar free resource. Do not submit copyrighted work without permission!

Cancel Editing help (opens in new window)