mysql

MySQL offers several types of charset (CHARACTER_SET), thereby satisfying the most varied projects.

See character set supported by the MySQL Server

> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------
| Charset  | Description                 | Default collation   
+----------+-----------------------------+--------------------
| big5     | Big5 Traditional Chinese    | big5_chinese_ci    
| dec8     | DEC West European           | dec8_swedish_ci    
| cp850    | DOS West European           | cp850_general_ci   
| hp8      | HP West European            | hp8_english_ci     
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci   
| latin1   | cp1252 West European        | latin1_swedish_ci  
| latin2   | ISO 8859-2 Central European | latin2_general_ci  
| swe7     | 7bit Swedish                | swe7_swedish_ci    
| ascii    | US ASCII                    | ascii_general_ci   
....

See available COLLATION for a CHARACTER SET

> SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS 
WHERE CHARACTER_SET_NAME = 'utf8';
+--------------------+
| COLLATION_NAME     |
+--------------------+
| utf8_general_ci    |
| utf8_bin           |
| utf8_unicode_ci    |
| utf8_icelandic_ci  |
| utf8_latvian_ci    |
| utf8_romanian_ci   |
| utf8_slovenian_ci  |
...

See COLLATION, CHARACTER_SET for the columns in a particular table

Here we take as an example the BD: Sakila

> SELECT COLUMN_NAME,COLLATION_NAME, CHARACTER_SET_NAME FROM 
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'actor' AND 
TABLE_SCHEMA = 'sakila';
+-------------+-----------------+--------------------+
| COLUMN_NAME | COLLATION_NAME  | CHARACTER_SET_NAME |
+-------------+-----------------+--------------------+
| actor_id    | NULL            | NULL               |
| first_name  | utf8_general_ci | utf8               |
| last_name   | utf8_general_ci | utf8               |
| last_update | NULL            | NULL               |
+-------------+-----------------+--------------------+

Recommended reading

– F. van der lan, Rick. SQL for MySQL Developers, Part III, Chapter 22.

MySQL is a relational database server with a great reputation for being fast, consume few resources and adapt very well to the demands of the web. In this article we will show how to connect, from the command line, to a MySQL server, as well as create, list, select and delete a database as well as show information about tables and storage engines

Connect to the mysql server

mysql [-h mysql-server] -u user-name -p[password] [BD]

We encourage using the option -p instead of -ppassword, because the latter is less secure (password is saved in the history)

The default mysql server is localhost

Create database

create schema DB;

Create a database specifying charset and collation

create database DB default character set = UTF8 default collate =utf8_general_ci;

schema is an alias for database command and can be used both interchangeably

See the charset and available collations

show collation;

Or by specifying a specific charset

show collation like 'utf8%';

Delete database

drop schema BD;

List databases

show schemas;

Select a database

use DB;

List tables

show tables;

Show the info for a table

desc table-name;

Show description of a column

show columns from table-name where Field='column-name'

Show the indexes for a table

show index from table-name;

List storage engines

show engines;

Recommended reading

-man mysql