Jump to content

Data type

From LemonWiki共筆

資料庫欄位設計時，針對不同資料類型，建議的資料型態 (data type) 與長度

Examples of data and suggested datatype[edit]

birth year[edit]

file name[edit]

file name for Win FTFS ; Linux ex3 and ex4 file system types<ref>Type df -aT to list the file system types in Linux</ref>.

IP[edit]

IP(v4)

range/limit

IPv6: 128位元長度，以16位元為一組，每組以冒號":"隔開，可以分為8組，每組以4位元十六進制方式表示

    • data type:
    • ex: 2001:0db8:85a3:08d3:1319:8a2e:0370:7344

網址[edit]

參考資料

Email[edit]

姓名欄位[edit]

unix timestamp[edit]

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

duration 時長[edit]

學校代碼[edit]

School ID defined by MOE at Taiwan / 學校代碼<ref>各級學校名錄--教育部統計處 Department of Statistics[Last visited: 2019-10-13]</ref>

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

地址[edit]

  • 資料類型: TEXT or VARCHAR(255)<ref>address</ref>
  • 參考別人: 觀察 Google payment 付款地址的文字輸入框並沒有限制文字長度

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

價錢/金額[edit]

台灣縣市欄位值[edit]

台灣縣市欄位值 下拉式選單<ref>取自中華郵政全球資訊網</ref> 

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

密碼[edit]

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

Hash Algorithm Output and Schema Reference <ref>Secure Hash Algorithms - Wikipedia</ref>

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: Icon_exclaim.gif Not recommended to use this function to secure passwords

SHA<ref>mysql - How long is the SHA256 hash? - Stack Overflow</ref><ref>魚乾的筆記本: MD5被破解了，要改用SHA</ref>:

SHA-1 (Secure Hash Algorithm 1) Icon_exclaim.gif 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]

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

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

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", "-",""))
);

Icon_exclaim.gif 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]

統一編號 (Unified Business Number, 簡稱 UBN)

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

  • 最長 10 位文字，所以建議使用 CHAR(10)：(1)身分證號：1碼英文字母加上9碼數字組成、(2) 統一證號：2碼英文字母加上8碼數字，一共10個字元組成 <ref>新式外來人口統一證號(宣導手冊)</ref>。

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

MySQL: VARCHAR(14)

書籍

國際電話號碼[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<ref>

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<ref>SQL Server 2008 timestamp data type - Stack Overflow</ref> Icon_exclaim.gif

further reading

tools[edit]

ER圖(entity-relationship diagram)

產生資料庫結構表格文件

參考其他資料表的結構設計<ref>MySQL :: MySQL 5.0 Reference Manual :: 3.4 Getting Information About Databases and Tables</ref>

  • DESCRIBE table_name;

Further reading[edit]


References[edit]

<references />


Web site design and development process

Retrieved from "https://wiki.planetoid.info/index.php?title=Data_type&oldid=26064"