Data type

資料庫欄位設計時，針對不同資料類型，建議的資料型態 (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)

參考資料

最大 URL 長度是在 Internet Explorer 中的 2,083 字元 for IE ^[9]^[10]

Email[edit]

data type:
- MySQL: VARCHAR(254)^[11]
遵循 RFC 5321: Simple Mail Transfer Protocol 規格

姓名欄位[edit]

資料類型： CHAR(50)、VARCHAR(50)
參考資料：「惟戶政系統中，欄位最多填寫50字，成為名字長度的天花板。」^[12]^[13]^[14]
其他人作法: 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'」^[15]
format: 小時:分鐘:秒 hh:mm:ss
參考別人: Google form 問卷題目選項的小時 0~72 ; 分鐘: 00~59: 秒: 00~59

學校代碼[edit]

School ID defined by MOE at Taiwan / 學校代碼^[16]

integer: 4(university) ~ 6
ex: 0001(國立政治大學)、373607(臺北市立華江國小)。前面可能有零。
range/limit:

地址[edit]

資料類型: TEXT or VARCHAR(255)^[17]
參考別人: 觀察 Google payment 付款地址的文字輸入框並沒有限制文字長度

經緯度 (經度,緯度)[edit]

DECIMAL(18,12)^[18] or FLOAT( 10, 6 )^[19] or VARCHAR( 30 )
ex: 37.401724,-122.114646
range/limit: 「緯度座標的整數須介於 -90 和 90 之間。經度座標的整數須介於 -180 和 180 之間。^[20]」

價錢/金額[edit]

DECIMAL(10,2)^[21]^[22]
ex:
range/limit: 0.00 ~ 99999999.99

台灣縣市欄位值[edit]

台灣縣市欄位值下拉式選單^[23]

$city = array("基隆市", "台北市", "新北市", "桃園縣", "新竹市", "新竹縣", "苗栗縣", "台中市", 
        "彰化縣", "南投縣", "雲林縣", "嘉義市", "嘉義縣", "台南市", "高雄市", "屏東縣", "台東縣", "花蓮縣", 
        "宜蘭縣", "澎湖縣", "金門縣", "連江縣");

密碼[edit]

varchar at least eight (8) characters^[24]

雜湊碼 (hash value) e.g. MD5, SHA[edit]

Hash Algorithm Output and Schema Reference ^[25]

Algorithm	Output Size (bits)	Max Length (chars)	Schema Recommendation (Text)	Schema Recommendation (Binary)
MD5	128	32	char(32)	binary(16)
SHA-1	160	40	char(40)	binary(20)
SHA-224	224	56	char(56)	binary(28)
SHA-256	256	64	char(64)	binary(32)
SHA-384	384	96	char(96)	binary(48)
SHA-512	512	128	char(128)	binary(64)

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

MD5: Not recommended to use this function to secure passwords

Data type: CHAR(32) or VARCHAR(32)^[26]
Framework: MySQL md5(), PHP: md5 - Manual

SHA^[27]^[28]:

(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))

SHA-1 (Secure Hash Algorithm 1) 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 SHA1() function, PHP: sha1 - Manual

SHA-256 (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 MySQL :: MySQL 8.0 Reference Manual :: 8.4.1.3 SHA-256 Pluggable Authentication, PHP: hash - Manual

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)));^[29]

UUID 通用唯一辨識碼、GUID 全域唯一識別碼字串[edit]

通用唯一辨識碼 - 維基百科，自由的百科全書、全域唯一識別碼 - 維基百科，自由的百科全書

BINARY(16)^[30]
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]

Membership Providers

台灣公司統一編號[edit]

數值長度：八位數字
建議資料類型：因為可能以 0 開頭，所以建議使用 CHAR(8)，而不建議使用 INT(8) ^[31]^[32] 早期公司統一編號不是八位數字

台灣身分證號/統一證號[edit]

最長 10 位文字，所以建議使用 CHAR(10)：(1)身分證號：1碼英文字母加上9碼數字組成、(2) 統一證號：2碼英文字母加上8碼數字，一共10個字元組成 ^[33]。

商品序號或商品條碼[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]

MySQL: LONGTEXT: 4GB / TEXT: 65,535 bytes ~64kb ^[34]^[35]
MsSQL: NVARCHAR(max): 2GB ^[36]

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

comparision of datatypes in different database server[edit]

numeric[edit]

bigint

MS SQL^[37] & MySQL^[38] are equivalent

text[edit]

Storing Unicode text^[39]

MySQL: varchar / MS SQL 2008: nvarchar
MySQL: LONGTEXT (4GB)^[40] / MS SQL 2008: nvarchar(max) (2GB)^[41]

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^[42]

MS SQL 2008^[43] ex: 0x00000000000007D3
MySQL ex: 2013-06-13 03:33:33

tools[edit]

ER圖(entity-relationship diagram)

MySQL :: MySQL Workbench 6.0: Notice the installation prerequisiteshttps://www.planetoid.info/images/Icon_exclaim.gif

產生資料庫結構表格文件

Create database schema document

參考其他資料表的結構設計^[44]

DESCRIBE table_name;

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
↑ 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 <...>)
↑ 改名字數無上限戶政司：會念很久
↑ 中國新聞網 (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."
↑ Secure Hash Algorithms - Wikipedia
↑ 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
↑
- 松露筆管麵: Mysql 等同SqlServer的nvarchar
- sql server - What is the difference between varchar and nvarchar? - Stack Overflow
↑ 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

[1] 最年長者 - 維基百科，自由的百科全書: 「根據金氏世界紀錄大全紀錄的最長壽者是活了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 各國人口預期壽命列表

[2] Type df -aT to list the file system types in Linux

[3] Comparison of file systems - Wikipedia, the free encyclopedia

[4] Naming Files, Paths, and Namespaces (Windows)

[5] Datatype for storing ip address in SQL Server - Stack Overflow

[6] Help:Range blocks - MediaWiki

[7] PostgreSQL: Documentation: Manuals: Network Address Types

[8] sql - Best database field type for a URL - Stack Overflow

[9] ttp - What is the maximum length of a URL in different browsers? - Stack Overflow

[10] Maximum URL lengths « The Wiert Corner – irregular stream of stuff

[11] 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 <...>)

[12] 改名字數無上限戶政司：會念很久

[13] 中國新聞網 (2010). 台灣“獨一姓氏”者達149人最長姓名有13個字_台灣頻道_新浪網-北美

[14] 姓名8個字外交部不給護照 - 生活 - 自由時報電子報

[15] MySQL :: MySQL 5.0 Reference Manual :: 11.3.2 The TIME Type

[16] 各級學校名錄--教育部統計處 Department of Statistics[Last visited: 2019-10-13]

[17] ress

[18] ysql - What is the ideal data type for latitude / longitude? - Stack Overflow

[19] Creating a Store Locator with PHP, MySQL & Google Maps - Google Maps API Family - Google Code

[20] 找出或輸入經緯度 - 電腦 - Google 地圖說明

[21] sql - Best Data Type for Currency - Stack Overflow[Last visited: 2012-03-07]

[22] ysql 用什么数据类型表示价格？_百度知道[Last visited: 2015-05-25]

[23] 取自中華郵政全球資訊網

[24] Password length & complexity - OWASP "Minimum length. Passwords should be at least eight (8) characters long."

[25] Secure Hash Algorithms - Wikipedia

[26] - Can I use VARCHAR(32) for md5() values? - Stack Overflow

[27] ysql - How long is the SHA256 hash? - Stack Overflow

[28] 魚乾的筆記本: MD5被破解了，要改用SHA

[29] sert - Storing a binary SHA1 hash into a mySQL BINARY(20) column - Stack Overflow

[30] How should I store GUID in MySQL tables? - Stack Overflow

[31] 公示資料查詢服務-財政部稅務入口網

[32] » 營利事業統一編號驗證完全手冊(Javascript,Java,C#,PHP) - Hero Think~用手摀住我的嘴

[33] 新式外來人口統一證號(宣導手冊)

[34] MySQL :: MySQL 5.7 Reference Manual :: 11.1.3 String Type Overview

[35] MySQL TEXT 格式的長度限制 - Tsung's Blog

[36] r and nvarchar (Transact-SQL)

[37] t、bigint、smallint 和 tinyint (Transact-SQL)

[38] MySQL :: MySQL 5.5 Reference Manual :: 11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

[39] 
松露筆管麵: Mysql 等同SqlServer的nvarchar

sql server - What is the difference between varchar and nvarchar? - Stack Overflow

[40] 松露筆管麵: Mysql 等同SqlServer的nvarchar

[41] sql server - What is the difference between varchar and nvarchar? - Stack Overflow

[40] Whats SQL Server NVARCHAR(max) equivalent in MySQL? - Database Administrators Stack Exchange

[41] r 和 nvarchar (Transact-SQL)

[42] SQL Server 2008 timestamp data type - Stack Overflow

[43] timestamp (Transact-SQL)

[44] MySQL :: MySQL 5.0 Reference Manual :: 3.4 Getting Information About Databases and Tables

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]

[14]

[15]

[16]

[17]

[18]

[19]

[20]

[21]

[22]

[23]

[24]

[25]

[26]

[27]

[28]

[29]

[30]

[31]

[32]

[33]

[34]

[35]

[36]

[37]

[38]

[39]

[40]

[41]

[42]

[43]

[44]

Data type

Contents

Examples of data and suggested datatype[edit]

birth year[edit]

file name[edit]

IP[edit]

網址[edit]

Email[edit]

姓名欄位[edit]

unix timestamp[edit]

duration 時長[edit]

學校代碼[edit]

地址[edit]

經緯度 (經度,緯度)[edit]

價錢/金額[edit]

台灣縣市欄位值[edit]

密碼[edit]

雜湊碼 (hash value) e.g. MD5, SHA[edit]

Retrieve the hash value from string or file content[edit]

Retrieve the hash value from binary value[edit]

UUID 通用唯一辨識碼、GUID 全域唯一識別碼字串[edit]

Store the GUID value[edit]

Retrieve the GUID value[edit]

membership[edit]

台灣公司統一編號[edit]

台灣身分證號/統一證號[edit]

商品序號或商品條碼[edit]

國際電話號碼[edit]

很長的文字[edit]

comparision of datatypes in different database server[edit]

numeric[edit]

text[edit]

date and time[edit]

tools[edit]

Further reading[edit]

References[edit]

Navigation menu

Data type

Examples of data and suggested datatype[edit]

birth year[edit]

file name[edit]

IP[edit]

網址[edit]

Email[edit]

姓名欄位[edit]

unix timestamp[edit]

duration 時長[edit]

學校代碼[edit]

地址[edit]

經緯度 (經度,緯度)[edit]

價錢/金額[edit]

台灣縣市欄位值[edit]

密碼[edit]

雜湊碼 (hash value) e.g. MD5, SHA[edit]

Retrieve the hash value from string or file content[edit]

Retrieve the hash value from binary value[edit]

UUID 通用唯一辨識碼、GUID 全域唯一識別碼字串[edit]

Store the GUID value[edit]

Retrieve the GUID value[edit]

membership[edit]

台灣公司統一編號[edit]

台灣身分證號/統一證號[edit]

商品序號或商品條碼[edit]

國際電話號碼[edit]

很長的文字[edit]

comparision of datatypes in different database server[edit]

numeric[edit]

text[edit]

date and time[edit]

tools[edit]

Further reading[edit]

References[edit]

Navigation menu

Search