MySQL, MariaDB and SQL
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;