MySQL/MariaDB

From The Travis van der Font Wiki
Jump to: navigation, search
Mysql-mariadb.png

For now, MariaDB will share a page with MySQL until there are enough resourceful pages to separate them, if ever.

-> MySQL Bin

General

Maintance / Debugging

WordPress Related

Stored Procedures

Functions

strip_tags() Removes any HTML
st_distance_sphere() Calculating the distance between two points of latitude and longitude (Haversine Formula)
remove_accents() Replaces most accent characters with non-accent matching characters (within UTF8)
[NEEDS REVIEWING] https://github.com/curzon01/mysql_PBKDF2

Data Type Dictionary

Name Data Type Reference(s)
id BIGINT(20) (unsigned)
btc_address VARCHAR(34) Bitcoin Wiki
country_alpha VARCHAR(2) ISO-3166-1: Alpha-2 Codes, & Wikipedia
country_id SMALLINT(3) (unsigned) ISO-3166-1: Numeric Codes, & Wikipedia
country_name VARCHAR(48)
email_address_local VARCHAR(64) RFC 5322, RFC 5321, RFC 3696, associated errata, & Wikipedia
email_address_full VARCHAR(254) RFC 5322, RFC 5321, & Wikipedia
five_star_rating DECIMAL(3,2) (unsigned)
phone_prefix MEDIUMINT(5) IDD Prefix
phone_national BIGINT(15) ITU-T - E.164
phone_suffix BIGINT(11) PBX
phone_full VARCHAR(40) LisaTerminal
domain_name_full VARCHAR(253) RFC 1034 & RFC 1123
filename VARCHAR(255)
filepath TEXT
url TEXT 2048 Chars
ipv4_address
ipv6_address
latitude US 9501526 B2
longitude US 9501526 B2
user_agent TEXT Microsoft, Stackoverflow
uuid BINARY(16) SELECT UNHEX(REPLACE(UUID(), '-', '')) - Wikpedia
description TINYTEXT
title VARCHAR(255)
gender TINYINT(1) (unsigned)
price DECIMAL(10,2) (unsigned)
vat_id_number VARCHAR(21) Wikipedia
date_created DATETIME
date_modified TIMESTAMP
soft_delete DATETIME

This below table is exclusively for hash data types:

Name Data Type Estimated Speed
adler32 8 ~15796.184ms
crc32 8 ~7561.922ms
crc32b 8 ~7298.946ms
gost 64 ~57237.148ms
md2 BINARY(16) (ascii_bin) ~705397.844ms
md4 BINARY(16) (ascii_bin) ~5307.912ms
md5 BINARY(16) (ascii_bin) / VARCHAR(24) [Base64] ~6890.058ms
haval128,3 32 (same as md5?) ~11880.874ms
haval128,4 32 (same as md5?) ~15887.022ms
haval128,5 32 (same as md5?) ~17887.115ms
haval160,3 40 ~11242.151ms
haval160,4 40 ~15661.954ms
haval160,5 40 ~17818.927ms
haval192,3 48 ~11132.955ms
haval192,4 48 ~15717.029ms
haval192,5 48 ~18135.07ms
haval224,3 56 ~11160.135ms
haval224,4 56 ~16047.954ms
haval224,5 56 ~18085.002ms
haval256,3 64 ~11327.981ms
haval256,4 64 ~15759.944ms
haval256,5 64 ~18678.903ms
ripemd128 32 (same as md5?) ~20671.844ms
ripemd160 40 ~21853.923ms
ripemd256 64 ~16245.126ms
ripemd320 80 ~22425.889ms
sha1 40 ~8886.098ms
sha256 64 hexadecimal - VARCHAR(44) [Base64] ~19020.08ms
sha384 96 ~45102.119ms
sha512 128 hexadecimal - CHAR(128) / BINARY(64) / VARCHAR(88) [Base64] ~45655.965ms
snefru 64 ~80352.783ms
tiger128,3 32 (same as md5?) ~11054.992ms
tiger160,3 40 ~11162.996ms
tiger192,3 48 ~11630.058ms
tiger128,4 32 (same as md5?) ~14871.12ms
tiger160,4 40 ~14946.937ms
tiger192,4 48 ~14776.945ms
whirlpool 128 ~64682.96ms
bcrypt
PBKDF2
scrypt

Integer Data Types

Type Storage

(Bytes)

Minimum Value

(Signed/Unsigned)

Maximum Value

(Signed/Unsigned)

TINYINT 1 -128 127 Signed
    0 255 Unsigned
SMALLINT 2 -32768 32767 Signed
    0 65535 Unsigned
MEDIUMINT 3 -8388608 8388607 Signed
    0 16777215 Unsigned
INT 4 -2147483648 2147483647 Signed
    0 4294967295 Unsigned
BIGINT 8 -9223372036854775808 9223372036854775807 Signed
    0 18446744073709551615 Unsigned

String Data Types

TINYTEXT          255 (+1 byte  overhead)

TEXT          64K - 1 (+2 bytes overhead)

MEDIUMTEXT    16M - 1 (+3 bytes overhead)

LONGTEXT      4G  - 1 (+4 bytes overhead)

Reference: https://stackoverflow.com/questions/13506832/what-is-the-mysql-varchar-max-size