MySQL, MariaDB and SQL – {dev

Published 3 years ago.
Last updated August 18th, 2021

Common datatypes

Types	Description	Default Value
BLOB	BLOB (Binary Large OBject) and TEXT hold large values. BLOB values are treated as binary strings (byte strings) and can hold anything from text to file data (images, PDF’s, etc.). TEXT strings are treated as non-binary character strings. BLOB and TEXT columns cannot have DEFAULT values. Sizes: TINYBLOB (256 bytes), BLOB (65KB), MEDIUMBLOB (16MB), and LONGBLOB (4GB). See BLOB and TEXT Types.	n/a
DATETIME	A date and time combination formatted as `YYYY-MM-DD HH:MM:SS`. The allowed range is from `1000-01-01 00:00:00` to `9999-12-31 23:59:59`. NOTE Always store datetime in UTC and convert it once extracted. See Date Types.	`1000-01-01`
INT	An integer (whole number). A signed integer can have negative numbers, unsigned only positive. See Integer Types.	`0`
VÀRCHAR	VÀRCHAR must declare a max character length (example: `CHAR(30)` can hold up to 30 characters). The maximum characters limit is `21,844` for UTF8. See CHAR and VÀRCHAR Types.	`NULL`

TINYTEXT:   255 chars           - 255 B
TEXT:       65,535 chars        - 64 KB
MEDIUMTEXT: 16,777,215 chars    - 16 MB
LONGTEXT:   4,294,967,295 chars - 4 GB

Null and Empty Values

NULL means “a value is not know.” An empty var '' means “the value is empty”.

Configs

my.cnf file

Find the location of the MySQL my.cnf file by running:

mysql --help | grep cnf

This should return something like (if your using XAMPP):

/Applications/XAMPP/xamppfiles/etc/my.cnf ~/.my.cnf 
                      order of preference, my.cnf, $MYSQL_TCP_PORT,

The first entry on the first line is the location of the my.cnf file: /Applications/XAMPP/xamppfiles/etc/my.cnf.

SQL Mode

MariaDB supports several different modes which allow you to tune it to suit your needs.
https://mariadb.com/kb/en/sql-mode/

SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;

Set the SQL Mode Temporarily

The mode can be set temporarily by running queries. This can be useful to test a mode or change, and then change-back for specific needs. The mode will reset to its default if MySQL is restarted.

SET GLOBAL sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Set the SQL Mode Permanently

Change to a sudo user and open the my.cnf file for editing.

sudo nano /Applications/XAMPP/xamppfiles/etc/my.cnf

Find the section starting with [mysqld]
Add the following to the bottom of the definition block:

sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The full [mysqld] block should look something like:

# The MySQL server
default-character-set=utf8mb4
[mysqld]
user=mysql
port=3306
socket          =/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock
key_buffer=16M
max_allowed_packet=1M
table_open_cache=64
sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=8M
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Command Line

Installed MySQL Version

Show the version of MySQL instance running as mysql on the local machine.

mysql -V

MySQL Binary File Location

Show the path to the binary file for the MySQL instance running as mysql on the local machine.

which mysql

Login to MySQL

By default most localhost implementations use the root user, but other users can be added.

mysql -u root -p

If MySQL is not properly aliased or there are multiple instance, the full path to the mysql instance may be needed to login:

/Applications/xampp/xamppfiles/bin/mysql -u root -p

Login to MySQL Remotely

From a machine running mysql:

mysql -u my_user_name -p'my_user_password' \
  -h remote.hosts.ip.or.domain.name -P 3306 \
  -D the_db_name

Structured Queries

IF()

Syntax

IF(condition, value_if_true, value_if_false)

Examples

SELECT IF(field_a, field_a, field_b) AS my_field FROM my_table

Database Collation

Use utf8 encoding and set collation to utf8_unicode_ci (to prefer accuracy over a small performance improvement), unless there is a good reason to prefer a specific language. For instance utf8mb4_unicode_520_ci has all the Chinese characters.

Database collation makes a difference in sorting accuracy (when comparing characters in the language) and performance. The only special case is utf8_bin which compares characters in binary format.

utf8_general_ci is somewhat faster than utf8_unicode_ci, but less accurate (for sorting). The specific language utf8 encoding (such as utf8_swedish_ci) contain additional language rules that make them the most accurate to sort for those languages.

Queries

MySQL Variables

SHOW VARIABLES;

To see specific variables use LIKE with a search string:

SHOW VARIABLES LIKE "%version%";

MySQL Info	Variable Search String
Host Name	`SHOW VARIABLES LIKE '%host%';`
Port	`SHOW VARIABLES LIKE '%port%';`
Character Sets	`SHOW VARIABLES LIKE '%character_set%';`
Collations	`SHOW VARIABLES LIKE '%collation%';`
SQL Mode	`SHOW VARIABLES LIKE '%sql_mode%';`
Socket	`SHOW VARIABLES LIKE '%socket%';`
DB Data Path	`SHOW VARIABLES LIKE '%datadir%';`
Engine Type	`SHOW VARIABLES LIKE '%storage_engine%';`
Error Log Path	`SHOW VARIABLES LIKE '%log_error%';`

Database

Show all databases      SHOW DATABASES;
Create a new database   CREATE DATABASE 'my_db_name';
Use a database          USE my_db_name;

Tables

Show all tables         SHOW tables;
Show table fields       DESCRIBE my_table_name;
Show table full-fields  SHOW FULL COLUMNS FROM my_table_name;

Users

Also see: MySQL Users

Show the current user   SELECT USER();
Show all users          SELECT User FROM mysql.user;
Show user privileges    SHOW GRANTS FOR 'my_user'@localhost;
Create a user           CREATE USER 'my_user'@localhost IDENTIFIED BY 'my_users_password';
Delete a user           DROP USER 'my_user'@localhost;

Count Records

SELECT COUNT(*) FROM my_table;