Data type: Difference between revisions
Jump to navigation
Jump to search
m (→台灣身分證號/統一證號) |
|||
(53 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
資料庫欄位設計時,針對不同資料類型,建議的資料型態 (data type) 與長度 | |||
== 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}} | ||
* 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 15: | Line 13: | ||
=== IP === | === IP === | ||
IP(v4) | 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> | * 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: | * 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}} | ||
** MS SQL: {{kbd | key=nvarchar(2083)}} | |||
</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> | |||
=== | === 姓名欄位 === | ||
* | * 資料類型:{{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 49: | Line 49: | ||
=== 學校代碼 === | === 學校代碼 === | ||
School ID defined by MOE at Taiwan / 學校代碼<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 | * 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> or VARCHAR( 30 ) | ||
* ex: 37.401724,-122.114646 | * 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 78: | ||
=== 密碼 === | === 密碼 === | ||
* 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]: | |||
* {{Gd}} 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]<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>}} | |||
* (3) CHAR(60) if using the MySQL [https://www.w3resource.com/mysql/encryption-and-compression-functions/sha1().php SHA1() function] | |||
==== 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> | |||
=== GUID 全域唯一識別碼字串 === | |||
[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 === | ||
* [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= 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 位數 | * 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 [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) 由十個字元(字母或數字)組成 | * 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 位數 | * 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 = TEXT}}<ref>[http://blog.longwin.com.tw/2009/10/mysql-text-field-type-length-limit-2009/ MySQL TEXT 格式 的 長度限制 - Tsung's Blog | * 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)}}<ref>[http://technet.microsoft.com/en-us/library/ms186939.aspx nchar and nvarchar (Transact-SQL)]</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) | <pre> (left blank intentionally) | ||
Line 102: | Line 177: | ||
== 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}} | * MySQL: {{kbd | key=varchar}} / MS SQL 2008: {{kbd | key=nvarchar}} | ||
* MS SQL 2008: {{kbd | key=nvarchar}} | * 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> | ||
=== 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 are equivalent | ||
Line 115: | Line 196: | ||
* MySQL ex: 2013-06-13 03:33:33 | * MySQL ex: 2013-06-13 03:33:33 | ||
further reading | |||
* [https://www. | * [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> | ||
* {{kbd | key = <nowiki>DESCRIBE table_name;</nowiki>}} | * {{kbd | key = <nowiki>DESCRIBE table_name;</nowiki>}} | ||
== | == 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 134: | Line 217: | ||
* [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 /> | |||
{{Template:Build a website}} | |||
[[Category:Web_Dev]] [[Category:Database]] [[Category:MySQL]] [[Category:Data Science]] | [[Category:Web_Dev]] [[Category:Database]] [[Category:MySQL]] [[Category:Data Science]] |
Latest revision as of 11:06, 14 December 2023
資料庫欄位設計時,針對不同資料類型,建議的資料型態 (data type) 與長度
Examples of data and suggested datatype[edit]
birth year[edit]
- data type: int
- example: Range from 1905 ~ 2013 (108 yeas old) from the Sign-up form from outlook.com [Last visited: 2013-02-04]
- range/limit: 122 yeas old[1]
file name[edit]
file name for Win FTFS ; Linux ex3 and ex4 file system types[2].
- varchar(255) [3][4] 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[edit]
IP(v4)
- data type: CHAT(15) or VARCHAR(15)
- ex: 255.255.255.255 [5]
range/limit
- IP range - Classless inter-domain routing (CIDR)[6]
- 12 or 24 bytes (IPv4 and IPv6 networks)[7]
- ex: 69.208.0.0/32
IPv6: 128位元長度,以16位元為一組,每組以冒號":"隔開,可以分為8組,每組以4位元十六進制方式表示
- data type:
- ex: 2001:0db8:85a3:08d3:1319:8a2e:0370:7344
網址[edit]
- data type:
- MySQL 5.0.3+ use VARCHAR(2083)[8] 或 TEXT
- MS SQL: nvarchar(2083)
參考資料
姓名欄位[edit]
- 資料類型: CHAR(50)、VARCHAR(50)
- 參考資料:「惟戶政系統中,欄位最多填寫50字,成為名字長度的天花板。」[11][12][13]
- 其他人作法: LINE 姓名欄位長度限制: 15字(15個中文字或15英文字母)
unix timestamp[edit]
- bigint(10) ;
- ex: 1328664539
- range/limit:
duration 時長[edit]
- data type: TIME 「TIME values may range from '-838:59:59' to '838:59:59'」[14]
- format: 小時:分鐘:秒 hh:mm:ss
- 參考別人: Google form 問卷題目選項的小時 0~72 ; 分鐘: 00~59: 秒: 00~59
學校代碼[edit]
School ID defined by MOE at Taiwan / 學校代碼[15]
- integer: 4(university) ~ 6
- ex: 0001(國立政治大學)、373607(臺北市立華江國小)。前面可能有零。
- range/limit:
地址[edit]
- 資料類型: TEXT or VARCHAR(255)[16]
- 參考別人: 觀察 Google payment 付款地址的文字輸入框並沒有限制文字長度
經緯度 (經度,緯度)[edit]
- DECIMAL(18,12)[17] or FLOAT( 10, 6 )[18] or VARCHAR( 30 )
- ex: 37.401724,-122.114646
- range/limit: 「緯度座標的整數須介於 -90 和 90 之間。經度座標的整數須介於 -180 和 180 之間。[19]」
價錢/金額[edit]
台灣縣市欄位值[edit]
台灣縣市欄位值 下拉式選單[22]
$city = array("基隆市", "台北市", "新北市", "桃園縣", "新竹市", "新竹縣", "苗栗縣", "台中市", "彰化縣", "南投縣", "雲林縣", "嘉義市", "嘉義縣", "台南市", "高雄市", "屏東縣", "台東縣", "花蓮縣", "宜蘭縣", "澎湖縣", "金門縣", "連江縣");
密碼[edit]
- varchar at least eight (8) characters[23]
雜湊碼 (hash value) e.g. MD5, SHA[edit]
MD5:
- CHAR(32) or
- VARCHAR(32)[24]
- (1) HEX: CHAR(64) Using PHP: hash_file() or PHP: hash(), MySQL SHA2(str, hash_length)
- (2) Binary: BINARY(32) In PHP using hex2bin() e.g. echo hex2bin(hash_file('sha256', $file_path));, In MySQL using UNHEX() function e.g. SELECT UNHEX(SHA2('The quick brown fox jumped over the lazy dog.', 256))
- (3) CHAR(60) if using the MySQL SHA1() function
Retrieve the hash value from string or file content[edit]
PHP:
<?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.'); ?>
MySQL:
SELECT SHA2('The quick brown fox jumped over the lazy dog.', 256)
Retrieve the hash value from binary value[edit]
- PHP: bin2hex() e.g. echo bin2hex(hex2bin(hash('sha256', 'The quick brown fox jumped over the lazy dog.')));
- MySQL: Hexadecimal Literals e.g. SELECT HEX(UNHEX(SHA2('The quick brown fox jumped over the lazy dog.', 256)));[27]
GUID 全域唯一識別碼字串[edit]
- BINARY(16)[28]
- VARCHAR(36) or CHAR(36)
Store the GUID value[edit]
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", "-","")) );
Need to replace the - symbol with whitespace from GUID value. Or it will not able to retrieve the GUID value
Retrieve the GUID value[edit]
SELECT `guid_char`, HEX(`guid_binary`) FROM `sometable`;
membership[edit]
台灣公司統一編號[edit]
台灣身分證號/統一證號[edit]
- 最長 10 位文字,所以建議使用 CHAR(10):(1)身分證號:1碼英文字母加上9碼數字組成、(2) 統一證號:2碼英文字母加上8碼數字,一共10個字元組成 [31]。
商品序號或商品條碼[edit]
MySQL: VARCHAR(14)
- GTIN 全球交易品項號碼(Global Trade Item Number) 最長 14 位數
- EAN 國際商品條碼(International Article Number),即前歐洲商品條碼 (European Article Number) and Japanese Article Number (JAN) 最長 13 位數
- UPC 通用產品代碼 (Universal Product Code) 最長 12 位數,只允許數字 (UPC-A)
- ASIN 亞馬遜標準識別號碼 (Amazon Standard Identification Number) 由十個字元(字母或數字)組成
書籍
- ISBN 國際標準書號(International Standard Book Number) 最長 13 位數
國際電話號碼[edit]
使用 Google libphonenumber 套件驗證國際電話號碼格式 – Frochu – Medium
很長的文字[edit]
(left blank intentionally) * data ** data type ** ex: ** range/limit:
comparision of datatypes in different database server[edit]
numeric[edit]
bigint
text[edit]
Storing Unicode text[37]
- MySQL: varchar / MS SQL 2008: nvarchar
- MySQL: LONGTEXT (4GB)[38] / MS SQL 2008: nvarchar(max) (2GB)[39]
date and time[edit]
DATETIME: ex: "2013-06-13 03:33:33"
- MS SQL 2008 & MySQL are equivalent
TIMESTAMP: MS SQL 2008 and MySQL are NOT equivalent[40]
- MS SQL 2008[41] ex: 0x00000000000007D3
- MySQL ex: 2013-06-13 03:33:33
further reading
- MySQL to SQL Server Data Type Comparisons
- Microsoft SQL Server - Data Types (Migration by Ispirer SQLWays) [Last visited: 2015-12-22]
- SQL Data Types for MS Access, MySQL, and SQL Server [Last visited: 2015-12-22]
tools[edit]
ER圖(entity-relationship diagram)
- MySQL :: MySQL Workbench 6.0: Notice the installation prerequisiteshttps://www.planetoid.info/images/Icon_exclaim.gif
產生資料庫結構表格文件
參考其他資料表的結構設計[42]
- DESCRIBE table_name;
Further reading[edit]
- MySQL :: MySQL 5.0 Reference Manual :: 10 Data Types
- SQL Server 2005 Data Types (Database Engine) / 資料類型 (Database Engine)
- Oracle Data Types
- Industry Data Models
- Newest 'database-design' Questions - Database Administrators Stack Exchange
References[edit]
- ↑ 最年長者 - 維基百科,自由的百科全書: 「根據金氏世界紀錄大全紀錄的最長壽者是活了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 各國人口預期壽命列表
- ↑ Type df -aT to list the file system types in Linux
- ↑ Comparison of file systems - Wikipedia, the free encyclopedia
- ↑ Naming Files, Paths, and Namespaces (Windows)
- ↑ Datatype for storing ip address in SQL Server - Stack Overflow
- ↑ Help:Range blocks - MediaWiki
- ↑ PostgreSQL: Documentation: Manuals: Network Address Types
- ↑ sql - Best database field type for a URL - Stack Overflow
- ↑ http - What is the maximum length of a URL in different browsers? - Stack Overflow
- ↑ Maximum URL lengths « The Wiert Corner – irregular stream of stuff
- ↑ 改名字數無上限 戶政司:會念很久
- ↑ 中國新聞網 (2010). 台灣“獨一姓氏”者達149人 最長姓名有13個字_台灣頻道_新浪網-北美
- ↑ 姓名8個字 外交部不給護照 - 生活 - 自由時報電子報
- ↑ MySQL :: MySQL 5.0 Reference Manual :: 11.3.2 The TIME Type
- ↑ 各級學校名錄--教育部統計處 Department of Statistics[Last visited: 2019-10-13]
- ↑ address
- ↑ mysql - What is the ideal data type for latitude / longitude? - Stack Overflow
- ↑ Creating a Store Locator with PHP, MySQL & Google Maps - Google Maps API Family - Google Code
- ↑ 找出或輸入經緯度 - 電腦 - Google 地圖說明
- ↑ sql - Best Data Type for Currency - Stack Overflow[Last visited: 2012-03-07]
- ↑ mysql 用什么数据类型表示价格?_百度知道[Last visited: 2015-05-25]
- ↑ 取自中華郵政全球資訊網
- ↑ Password length & complexity - OWASP "Minimum length. Passwords should be at least eight (8) characters long."
- ↑ php - Can I use VARCHAR(32) for md5() values? - Stack Overflow
- ↑ mysql - How long is the SHA256 hash? - Stack Overflow
- ↑ 魚乾的筆記本: MD5被破解了,要改用SHA
- ↑ insert - Storing a binary SHA1 hash into a mySQL BINARY(20) column - Stack Overflow
- ↑ How should I store GUID in MySQL tables? - Stack Overflow
- ↑ 公示資料查詢服務-財政部稅務入口網
- ↑ » 營利事業統一編號驗證完全手冊(Javascript,Java,C#,PHP) - Hero Think~用手摀住我的嘴
- ↑ 新式外來人口統一證號(宣導手冊)
- ↑ MySQL :: MySQL 5.7 Reference Manual :: 11.1.3 String Type Overview
- ↑ MySQL TEXT 格式 的 長度限制 - Tsung's Blog
- ↑ nchar and nvarchar (Transact-SQL)
- ↑ int、bigint、smallint 和 tinyint (Transact-SQL)
- ↑ MySQL :: MySQL 5.5 Reference Manual :: 11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
- ↑
- ↑ Whats SQL Server NVARCHAR(max) equivalent in MySQL? - Database Administrators Stack Exchange
- ↑ nchar 和 nvarchar (Transact-SQL)
- ↑ SQL Server 2008 timestamp data type - Stack Overflow
- ↑ timestamp (Transact-SQL)
- ↑ MySQL :: MySQL 5.0 Reference Manual :: 3.4 Getting Information About Databases and Tables
Web site design and development process
- Information gathering: Research surveys
- Planning: Before you start to build a website, Content development strategy | Register domain name, Choose web hosting | Information architecture | Data model: Data type, Data flow | Documentation: Request For Proposal | Licensing
- Design: CSS tools, Free fonts, Free photos, Emoji & icons
- Testing & delivery: Usability test, check browser compatibility | Web testing | Speed up websites: Web Ping, Software acceptance test plan | Promote your web
- Maintenance: Site backup & restore test, Software update (OS patch or CMS security update)
- Need help? Community, I need inspiration, Web design glossary