MySQL 2.4でユーザ毎の公開ディレクトリを設定する

MySQL2.4をインストールしただけでは、各ユーザがサイトを公開することが
できません。
そこで、UserDirを設定して、各ユーザがウェブ公開できるようにします。

1. UserDirを設定する。
作業は基本的に以下のファイルを編集して行います。

/usr/local/apache2/conf/httpd.conf

まずは、UserDirを設定します。
httpd.confに以下を追加します。

UserDir public_html

2. userdirのモジュールをロードするよう設定する

以下の行のコメントアウトをはずして、この行を有効にします。

# LoadModule userdir_module modules/mod_userdir.so

3. httpd-userdir.confを読み込む

以下の行のコメントアウトをはずして、この行を有効にします。

# Include conf/extra/httpd-userdir.conf

4. httpdを再起動します。

# /usr/local/apache2/bin/apachectl restart

/

テーブルの中身を空にする

テーブルの中身を空にするには、テーブルの中身をすべて削除するか、
テーブルを削除して再度作り直すという2通りの方法があります。

テーブル内のデータを削除する

mysql> delete from category;

deleteにより削除する場合はテーブル内のデータをそれぞれ削除し
テーブル自体は依然のままなので、AUTO_INCREMENT設定したidは
引き継がれます。

テーブルを削除して作り直す

mysql> truncate table category;

truncate句によりテーブルを空にする場合は、位置とテーブル自体を
削除してしまっているため、AUTO_INCREMENT設定したidはまた最初から
振りなおしになります。/

MySQLのあるデータベースのテーブルを別のデータベースにコピーする

あるデータベースで使用していたテーブルを、テスト用や本番稼動用などで別のデータベースに
データごとテーブルをコピーしたいこともあると思います。

そういう時は、CREATE COMMANDを使用して以下のようにコピーできます。

mysql> create table destination_db.destination_table select * from source_db.source_table;

destination_db:コピー先のデータベース名
destination_table:コピー先のテーブル名
source_db:コピー元のデータベース名
source_table:コピー元のテーブル/

バージョンの確認方法

mysqlコマンドで調べる

$ mysql --version
mysql  Ver 14.14 Distrib 5.5.25, for Linux (i686) using readline 5.1

mysqlサーバに接続して調べる

$ mysql -u root -pxxxx testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.5.25 MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysqlサーバに接続後に調べる (STATUS編)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.25, for Linux (i686) using readline 5.1

Connection id:          3
Current database:       testdb
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.25 MySQL Community Server (GPL) by Remi
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 7 min 58 sec

Threads: 1  Questions: 21  Slow queries: 0  Opens: 14  Flush tables: 1  Open tables: 8  Queries per second avg: 0.043
--------------

mysqlサーバ接続後に調べる(SELECT編)

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.5.25    |
+-----------+
1 row in set (0.00 sec)

/

既存テーブルのテーブル作成時のコマンドを表示する

以下で確認できます。

mysql> show create table 
mysql> show create table test2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

/

テーブルの情報を表示する

テーブルの詳細情報を知りたい場合は、”show table status”を実行します。

選択されているデータベース内のすべてのテーブルの情報を表示する場合

mysql> show table status;
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| test  | MyISAM |      10 | Dynamic    |    7 |             20 |         140 | 281474976710655 |         2048 |         0 |              8 | 2012-12-18 13:14:04 | 2012-12-18 13:36:02 | NULL       | latin1_swedish_ci |     NULL |                |         |
| test2 | MyISAM |      10 | Dynamic    |    1 |             20 |          20 | 281474976710655 |         2048 |         0 |              2 | 2012-12-18 13:42:14 | 2012-12-18 13:47:02 | NULL       | utf8_general_ci   |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+

選択されているデータベース内の特定のテーブルを表示する場合

mysql> show table status like 'test2';
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| test2 | MyISAM |      10 | Dynamic    |    1 |             20 |          20 | 281474976710655 |         2048 |         0 |              2 | 2012-12-18 13:42:14 | 2012-12-18 13:47:02 | NULL       | utf8_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+

データベースを指定する場合

mysql> show table status from mysql like 'user';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation | Checksum | Create_options | Comment                     |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| user | MyISAM |      10 | Dynamic    |   17 |             65 |        1108 | 281474976710655 |         2048 |         0 |           NULL | 2008-07-28 11:29:02 | 2012-12-06 08:34:32 | NULL       | utf8_bin  |     NULL |                | Users and global privileges |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
1 row in set (0.00 sec)

/

mysqlでの文字コードの確認方法と設定方法

1.statusコマンドを実行する

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.25, for Linux (i686) using readline 5.1

Connection id:          424
Current database:       symfony3
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.51a MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 11 days 23 hours 38 min 26 sec

Threads: 1  Questions: 107197  Slow queries: 0  Opens: 19  Flush tables: 1  Open tables: 13  Queries per second avg: 0.104
--------------

2. show variablesで調べる

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8                                   |
| character_set_connection | utf8                                   |
| character_set_database   | utf8                                   |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8                                   |
| character_set_server     | utf8                                   |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.00 sec)

/

テーブルごとの設定確認

mysql> show table status from mysql;
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
| Name                      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length    | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options     | Comment                                 |
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
| columns_priv              | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 241505530017742847 |         4096 |         0 |           NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL       | utf8_bin          |     NULL |                    | Column privileges                       |
| db                        | MyISAM |      10 | Fixed      |   25 |            488 |       12200 | 137359788634800127 |         5120 |         0 |           NULL | 2018-09-04 11:04:29 | 2019-10-10 23:50:25 | NULL       | utf8_bin          |     NULL |                    | Database privileges                     |
| engine_cost               | InnoDB |      10 | Dynamic    |    2 |           8192 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 |                                         |
| event                     | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |    281474976710655 |         2048 |         0 |           NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL       | utf8_general_ci   |     NULL |                    | Events                                  |
| func                      | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 162974011515469823 |         1024 |         0 |           NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL       | utf8_bin          |     NULL |                    | User defined functions                  |
| general_log               | CSV    |      10 | Dynamic    |    2 |              0 |           0 |                  0 |            0 |         0 |           NULL | NULL                | NULL                | NULL       | utf8_general_ci   |     NULL |                    | General log                             |
| gtid_executed             | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | latin1_swedish_ci |     NULL |                    |                                         |
| help_category             | InnoDB |      10 | Dynamic    |   40 |            409 |       16384 |                  0 |        16384 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | help categories                         |
| help_keyword              | InnoDB |      10 | Dynamic    |  728 |            135 |       98304 |                  0 |        81920 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | help keywords                           |
| help_relation             | InnoDB |      10 | Dynamic    | 2253 |             36 |       81920 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | keyword-topic relation                  |
| help_topic                | InnoDB |      10 | Dynamic    |  656 |           2422 |     1589248 |                  0 |        81920 |   4194304 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | help topics                             |
| innodb_index_stats        | InnoDB |      10 | Dynamic    |  513 |            159 |       81920 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | 2020-02-15 17:45:56 | NULL       | utf8_bin          |     NULL | stats_persistent=0 |                                         |
| innodb_table_stats        | InnoDB |      10 | Dynamic    |   34 |            481 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | 2020-02-15 17:45:56 | NULL       | utf8_bin          |     NULL | stats_persistent=0 |                                         |
| ndb_binlog_index          | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |    281474976710655 |         1024 |         0 |           NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL       | latin1_swedish_ci |     NULL |                    |                                         |
| plugin                    | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | MySQL plugins                           |
| proc                      | MyISAM |      10 | Dynamic    |   48 |           6261 |      300528 |    281474976710655 |         4096 |         0 |           NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL       | utf8_general_ci   |     NULL |                    | Stored Procedures                       |
| procs_priv                | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 266275327968280575 |         4096 |         0 |           NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL       | utf8_bin          |     NULL |                    | Procedure privileges                    |
| proxies_priv              | MyISAM |      10 | Fixed      |    1 |            837 |         837 | 235594555506819071 |         9216 |         0 |           NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL       | utf8_bin          |     NULL |                    | User proxy privileges                   |
| server_cost               | InnoDB |      10 | Dynamic    |    6 |           2730 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 |                                         |
| servers                   | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | MySQL Foreign Servers table             |
| slave_master_info         | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Master Information                      |
| slave_relay_log_info      | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Relay Log Information                   |
| slave_worker_info         | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Worker Information                      |
| slow_log                  | CSV    |      10 | Dynamic    |    2 |              0 |           0 |                  0 |            0 |         0 |           NULL | NULL                | NULL                | NULL       | utf8_general_ci   |     NULL |                    | Slow log                                |
| tables_priv               | MyISAM |      10 | Fixed      |    2 |            947 |        1894 | 266556802944991231 |         9216 |         0 |           NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL       | utf8_bin          |     NULL |                    | Table privileges                        |
| time_zone                 | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                  0 |            0 |         0 |              1 | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Time zones                              |
| time_zone_leap_second     | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Leap seconds information for time zones |
| time_zone_name            | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Time zone names                         |
| time_zone_transition      | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Time zone transitions                   |
| time_zone_transition_type | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2018-09-04 11:04:29 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Time zone transition types              |
| user                      | MyISAM |      10 | Dynamic    |   17 |            128 |        2188 |    281474976710655 |         4096 |         0 |           NULL | 2018-09-04 11:04:29 | 2019-10-10 23:50:25 | NULL       | utf8_bin          |     NULL |                    | Users and global privileges             |
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
31 rows in set (0.00 sec)

各フィールドの文字コードを確認する

デフォルトの文字コード以外の場合、 show create table <テーブル名> を実行すると、デフォルト以外の文字コードのフィールドには”CHARACTER SET <文字コード>“と表記されます。

CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `password_last_changed` timestamp NULL DEFAULT NULL,
  `password_lifetime` smallint(5) unsigned DEFAULT NULL,
  `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' 

文字コードの設定方法

テーブル全体のデフォルト文字コードは以下で設定する。

mysql> alter table <テーブル名> default character set <文字コード>;

以下は例にになります。

mysql> show create table highlights;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                               |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| highlights | CREATE TABLE `highlights` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `attr_name` varchar(200) DEFAULT NULL,
  `attr_id` int(11) DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table highlights default character set utf8;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table highlights;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                            |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| highlights | CREATE TABLE `highlights` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `attr_name` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
  `attr_id` int(11) DEFAULT NULL,
  `title` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
  `content` text CHARACTER SET latin1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

テーブルのデフォルト文字コードを変更しても既存のフィールドの文字コードはそのままです。
以下のようにして各フィールドの文字コードも変更できます。

mysql> alter table highlights modify attr_name varchar(100) character set utf8;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQLのデータ型

整数型

</tr

データ型 使用バイト 最小値 最大値
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

浮動小数点型

データ型 使用バイト 最小値 最大値
FLOAT 1.175494351E-38, -3.402823466E+38 3.402823466E+38, -1.175494351E-38
DOUBLE 2.2250738585072014E-308, -1.7976931348623157E+308 1.7976931348623157E+308, -2.2250738585072014E-308

/