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]. 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:

學校代碼

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

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

經緯度 (經度,緯度)

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

價錢/金額

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

台灣縣市欄位值

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

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

密碼

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

membership

很長的文字

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

comparision of datatypes in different database server

Storing Unicode text[21]

  • MySQL: varchar
  • MS SQL 2008: nvarchar

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

  • MS SQL 2008, MySQL is equivalent

TIMESTAMP: MS SQL 2008 and MySQL is NOT equivalent[22] Icon_exclaim.gif

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

tools

ER圖(entity-relationship diagram)

references

  1. 最年長者 - 維基百科,自由的百科全書: 「根據金氏世界紀錄大全紀錄的最長壽者是活了122年的雅娜·卡爾曼特」
  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. 各級學校名錄--教育部統計處 Department of Statistics[Last visited: 2012-02-16]
  12. mysql - What is the ideal data type for latitude / longitude? - Stack Overflow
  13. Creating a Store Locator with PHP, MySQL & Google Maps - Google Maps API Family - Google Code
  14. sql - Best Data Type for Currency - Stack Overflow[Last visited: 2012-03-07]
  15. 取自中華郵政全球資訊網
  16. Password length & complexity - OWASP "Minimum length. Passwords should be at least eight (8) characters long."
  17. MySQL TEXT 格式 的 長度限制 - Tsung's Blog
  18. MySQL :: MySQL 5.0 Reference Manual :: 11.1.6.3 The BLOB and TEXT Types
  19. nchar and nvarchar (Transact-SQL)
  20. SQL Server 2008 timestamp data type - Stack Overflow
  21. timestamp (Transact-SQL)

further reading