Data type: Difference between revisions
Jump to navigation
Jump to search
m (→商品序號或商品條碼) |
|||
Line 111: | Line 111: | ||
=== membership === | === membership === | ||
* [http://msdn.microsoft.com/en-us/library/aa478949.aspx Membership Providers] | * [http://msdn.microsoft.com/en-us/library/aa478949.aspx Membership Providers] | ||
=== 台灣公司統一編號 === | |||
* 八位數字 {{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> | |||
=== 商品序號或商品條碼 === | === 商品序號或商品條碼 === |
Revision as of 12:06, 13 March 2019
資料表欄位設計時,針對不同資料類型,建議的資料型態(data type)
examples of data and suggested datatype
birth year
- data type: int
- example: Range from 1905 ~ 2013 (108 yeas old) from the Sing-up form from outlook.com [Last visited: 2013-02-04]
- range/limit: 122 yeas old[1]
file name
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
IP(v4)
- 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
網址
- data type: >= MySQL 5.0.3 use VARCHAR(2083)[8]; nvarchar(2083) for MS SQL
- 最大 URL 長度是在 Internet Explorer 中的 2,083 字元 for IE [9]
Chinese name
unix timestamp
- bigint(10) ;
- ex: 1328664539
- range/limit:
duration 時長
- data type: TIME 「TIME values may range from '-838:59:59' to '838:59:59'」[12]
- format: 小時:分鐘:秒 hh:mm:ss
- 參考別人: Google form 問卷題目選項的小時 0~72 ; 分鐘: 00~59: 秒: 00~59
學校代碼
School ID defined by MOE at Taiwan / 學校代碼[13]
- integer: 4(university) ~ 6
- ex: 0001(國立政治大學)、373607(臺北市立華江國小)。前面可能有零。
- range/limit:
地址
- 資料類型: TEXT or VARCHAR(255)[14]
- 參考別人: 觀察 Google payment 付款地址的文字輸入框並沒有限制文字長度
經緯度 (經度,緯度)
價錢/金額
台灣縣市欄位值
台灣縣市欄位值 下拉式選單[19]
$city = array("基隆市", "台北市", "新北市", "桃園縣", "新竹市", "新竹縣", "苗栗縣", "台中市", "彰化縣", "南投縣", "雲林縣", "嘉義市", "嘉義縣", "台南市", "高雄市", "屏東縣", "台東縣", "花蓮縣", "宜蘭縣", "澎湖縣", "金門縣", "連江縣");
密碼
- varchar at least eight (8) characters[20]
雜湊碼 (hash value)
- MD5: CHAR(32)[21]
- SHA 256[22][23]:
- (1) HEX: CHAR(64) Using PHP: hash_file(), 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))
Retrieve the hash value from string or file content
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
- 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)));[24]
membership
台灣公司統一編號
商品序號或商品條碼
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 位數
國際電話號碼
使用 Google libphonenumber 套件驗證國際電話號碼格式 – Frochu – Medium
很長的文字
(left blank intentionally) * data ** data type ** ex: ** range/limit:
comparision of datatypes in different database server
numeric
bigint
text
Storing Unicode text[32]
- MySQL: varchar / MS SQL 2008: nvarchar
- MySQL: LONGTEXT (4GB)[33] / MS SQL 2008: nvarchar(max) (2GB)[34]
date and time
DATETIME: ex: "2013-06-13 03:33:33"
- MS SQL 2008 & MySQL are equivalent
TIMESTAMP: MS SQL 2008 and MySQL are NOT equivalent[35]
- MS SQL 2008[36] 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
ER圖(entity-relationship diagram)
- MySQL :: MySQL Workbench 6.0: Notice the installation prerequisiteshttps://www.planetoid.info/images/Icon_exclaim.gif
產生資料庫結構表格文件
參考其他資料表的結構設計[37]
- DESCRIBE table_name;
references
- ↑ 最年長者 - 維基百科,自由的百科全書: 「根據金氏世界紀錄大全紀錄的最長壽者是活了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
- ↑
- ↑ 中國新聞網 (2010). 台灣“獨一姓氏”者達149人 最長姓名有13個字_台灣頻道_新浪網-北美
- ↑ 姓名8個字 外交部不給護照 - 生活 - 自由時報電子報
- ↑ MySQL :: MySQL 5.0 Reference Manual :: 11.3.2 The TIME Type
- ↑ 各級學校名錄--教育部統計處 Department of Statistics[Last visited: 2012-02-16]
- ↑ 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
- ↑ 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
- ↑ 公示資料查詢服務-財政部稅務入口網
- ↑ » 營利事業統一編號驗證完全手冊(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
further reading
- MySQL :: MySQL 5.0 Reference Manual :: 10 Data Types
- SQL Server 2005 Data Types (Database Engine) / 資料類型 (Database Engine)
- Oracle Data Types
- Industry Data Models
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