Data type

From LemonWiki共筆
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

資料表欄位設計時,針對不同資料類型,建議的資料型態(data type)


Web site design and development process


examples of data and suggested datatype

birth year

  • data type: int
  • ex: 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 Os windows.png FTFS ; Linux Os linux.png ex3 and ex4 file system types[2].

  • varchar(255) [3][4] Icon_exclaim.gif 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

網址

Chinese name

  • 最長姓名有13個字 [10][11]
  • 參考別人: LINE姓名欄位長度限制: 15字(15個中文字或15英文字母)

unix timestamp

  • bigint(10) ;
  • ex: 1328664539
  • range/limit:

duration 時長

  • data type: TIMETIME 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:

經緯度 (經度,緯度)

  • DECIMAL(18,12)[14] or FLOAT( 10, 6 )[15] or VARCHAR( 30 )
  • ex: 37.401724,-122.114646
  • range/limit:

價錢/金額

  • DECIMAL(10,2)[16][17]
  • ex:
  • range/limit: 0.00 ~ 99999999.99

台灣縣市欄位值

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

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

密碼

  • varchar at least eight (8) characters[19]

membership

商品序號或商品條碼

網站專屬的商品序號

書籍

很長的文字

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

comparision of datatypes in different database server

Storing Unicode text[23]

  • MySQL: varchar
  • MS SQL 2008: nvarchar

DATETIME: ex: "2013-06-13 03:33:33"

  • MS SQL 2008 & MySQL are equivalent

TIMESTAMP: MS SQL 2008 and MySQL are NOT equivalent[24] Icon_exclaim.gif

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

conversion tools

tools

ER圖(entity-relationship diagram)

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

  • DESCRIBE table_name;

references

  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. 中國新聞網 (2010). 台灣“獨一姓氏”者達149人 最長姓名有13個字_台灣頻道_新浪網-北美
  10. 姓名8個字 外交部不給護照 - 生活 - 自由時報電子報
  11. MySQL :: MySQL 5.0 Reference Manual :: 11.3.2 The TIME Type
  12. 各級學校名錄--教育部統計處 Department of Statistics[Last visited: 2012-02-16]
  13. mysql - What is the ideal data type for latitude / longitude? - Stack Overflow
  14. Creating a Store Locator with PHP, MySQL & Google Maps - Google Maps API Family - Google Code
  15. sql - Best Data Type for Currency - Stack Overflow[Last visited: 2012-03-07]
  16. mysql 用什么数据类型表示价格?_百度知道[Last visited: 2015-05-25]
  17. 取自中華郵政全球資訊網
  18. Password length & complexity - OWASP "Minimum length. Passwords should be at least eight (8) characters long."
  19. MySQL TEXT 格式 的 長度限制 - Tsung's Blog
  20. MySQL :: MySQL 5.0 Reference Manual :: 11.1.6.3 The BLOB and TEXT Types
  21. nchar and nvarchar (Transact-SQL)
  22. SQL Server 2008 timestamp data type - Stack Overflow
  23. timestamp (Transact-SQL)
  24. MySQL :: MySQL 5.0 Reference Manual :: 3.4 Getting Information About Databases and Tables

further reading