MEMO MySQL

From KuWiki

Contents 1 Account Management 2 Character Set 2.1 Database 2.2 Table 2.3 Charsets 2.4 Collations 2.5 Show columns charset 2.6 Changing columns charset 3 Miscellaneous 3.1 Dumping the databases 3.2 Execute SQL files

Account Management

Character Set

Database

mysql> CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

mysql> ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

Table

mysql> CREATE TABLE tbl_name (column_list)
    [DEFAULT CHARACTER SET charset_name] [COLLATE collation_name]]

mysql> ALTER TABLE tbl_name
    [DEFAULT CHARACTER SET charset_name] [COLLATE collation_name]

Charsets

1. utf8
2. latin1
3. big5

Collations

• utf8
  1. utf8_general_ci
  2. utf8_bin
  3. utf8_unicode_ci
• latin1
  1. latin1_general_ci
  2. latin1_bin
• big5
  1. big5_chinese_ci
  2. big5_bin

Show columns charset

mysql> SHOW FULL COLUMNS FROM tbl_name FROM db_name

Changing columns charset

Dump the database.

$ mysqldump [-u <<user>>] [-p] <<database>> >file.sql

Converting the charset of the dumped file. (optional)

$ mv file.sql file.sql.orig
$ iconv -f latin1 -t utf8 file.sql.orig >file.sql

Edit the dumped file.

$ sed -i 's/latin1_bin/utf8_general_ci/g; s/latin1/utf8/g;' file.sql

Commit the modified sql file.

mysql> source file.sql

or

$ mysql [-u <<user>>] [-p] <file.sql

Miscellaneous

Dumping the databases

$ mysqldump [-u <<user>>] [-p] <<database>> >file.sql

Execute SQL files

mysql> source file.sql

or

$ mysql [-u <<user>>] [-p] <file.sql