MySQLは ON UPDATE CURRENT_TIMESTAMPで更新日時を自動的に更新できる

MySQLのタイムスタンプ型では、DEFAULT句に追加でon updateをつけることで更新時にも日時を自動設定できるらしいので試してみた。
試した時に使ったバージョンはMySQL Communityの5.7.10です。

DDLとテーブル定義

テーブル定義を見ると、on updateを指定したカラムはExtra列で確認することができる。

create table test_table (
    id int AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    last_modified TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp,
    PRIMARY KEY (id)
)

mysql> desc test_table;
+---------------+--------------+------+-----+-------------------+-----------------------------+
| Field         | Type         | Null | Key | Default           | Extra                       |
+---------------+--------------+------+-----+-------------------+-----------------------------+
| id            | int(11)      | NO   | PRI | NULL              | auto_increment              |
| name          | varchar(100) | NO   |     | NULL              |                             |
| last_modified | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+--------------+------+-----+-------------------+-----------------------------+

結果

insertやupdate時に対象のカラムを指定しないと、自動的にcurrent_timestampの値が入ることがわかる。
update時には、カラムの値が変更されている場合のみ、対象カラムのtaimustampが変更されている。

なお、insertやupdate時に任意の値を直接設定することもできる。

-- INSERT
mysql>  insert into test_table (name) values ('hoge');
Query OK, 1 row affected (0.07 sec)

-- 自動的にtimestampが設定される。
mysql> select * from test_table;
+----+------+---------------------+
| id | name | last_modified       |
+----+------+---------------------+
|  1 | hoge | 2015-12-14 12:24:09 |
+----+------+---------------------+
1 row in set (0.00 sec)

-- UPDATE
mysql> update test_table set name = 'fuga';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- timestampの値が更新される。
mysql> select * from test_table;
+----+------+---------------------+
| id | name | last_modified       |
+----+------+---------------------+
|  1 | fuga | 2015-12-14 12:24:32 |
+----+------+---------------------+
1 row in set (0.03 sec)

-- 値が変更されないUPDATE
mysql> update test_table set name = 'fuga';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

-- timestampは更新されない
mysql> select * from test_table;
+----+------+---------------------+
| id | name | last_modified       |
+----+------+---------------------+
|  1 | fuga | 2015-12-14 12:24:32 |
+----+------+---------------------+
1 row in set (0.00 sec)

[mysql]現在時間の取得

curdate()やcurtime()関数などを使用して現在日付や時間を取得できる。

例

-- 現在日付（current_dateを使用してもcurdate()と同じ結果が得られる)
mysql> select curdate() ;
+------------+
| curdate()  |
+------------+
| 2013-07-16 |
+------------+
1 row in set (0.01 sec)

-- 現在時間（current_timeを使用してもcurtimeと同じ結果が得られる）
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 08:45:46  |
+-----------+
1 row in set (0.00 sec)

-- 現在日時（current_timestampを使用してもnowと同じ結果が得られる）
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-07-16 08:47:20 |
+---------------------+
1 row in set (0.00 sec)

[mysql]indexが使われていないSQLを調べる方法

log_queries_not_using_indexesというオプションを有効にすることによって、indexが使われていないSQLを抽出することができる。

log_queries_not_using_indexesオプションの値の確認・設定方法

global variablesの値が「on」になっていれば、indexが使われていないSQLが抽出できます。

mysql> show  global variables like '%indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)

もし、offになっている場合には以下のコマンドで有効に変更できます。

mysql> set global log_queries_not_using_indexes = on;

抽出されたSQLの出力先

log_queries_not_using_indexesのログは、slow queryログに出力されるのでslow queryの設定で確認できます。

slow_query_logが、slow queryログを出力するかどうかの設定。onになっていれば、ログが出力されます。
slow_query_log_fileが、ログの出力先となります。

mysql> show global variables like '%slow%';
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| log_slow_queries    | ON               |
| slow_launch_time    | 2                |
| slow_query_log      | ON               | 
| slow_query_log_file | c:\work\slow.log |
+---------------------+------------------+
4 rows in set (0.00 sec)

slow queryの設定は、以下のように「set global」コマンドで変更できます。

mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)

mysql> set global slow_query_log_file = 'c:\\slow.log';
Query OK, 0 rows affected (0.00 sec)

動作を確認してみよう

テスト用に主キーのみのテーブルを作成

mysql> create table test_table (col1 char(1), primary key (col1));
Query OK, 0 rows affected (0.02 sec)

以下の２種類のSQLを実行。前者のSQLは、主キー検索のためindexが使用される。
後者のSQLは、関数を使用しているのでindexは使用されずに検索が行われる。

mysql> select * from test_table where col1 = '1';
Empty set (0.00 sec)

mysql> select * from test_table where substr(col1, 1, 1) = '1';
Empty set (0.00 sec)

slow queryログには、以下のようにindexが使用されなかったSQLの情報が取得できる。

# Time: 111030 16:00:38
# User@Host: root[root] @ localhost [::1]
# Query_time: 0.000500  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use test_db;
SET timestamp=1319958038;
select * from test_table where substr(col1, 1, 1) = '1';

これを使えばindexの張り忘れや、SQLのつくりが原因でindex使われていないSQLを機械的に抽出できるかなと。

【MySql】DatabaseMetaDataからテーブルの情報取得

DatabaseMetaDataからデータベースの情報を抜き出す方法

カラム名の取得

テーブルに対応するカラムのリストを定義順に取得するコード。

Oracleだと、スキーマ名やテーブル名は大文字じゃないとNGだけど、MySqlだと小文字じゃないとダメらしい。
ずっと大文字で指定してて、データ取得できずにかなりはまった・・・。

Map<Integer, String> columns = new TreeMap<Integer, String>();
try {
    DatabaseMetaData metaData = con.getMetaData();
    // testスキーマのtest_tableのカラム情報を取得
    ResultSet resultSet = metaData.getColumns(
            con.getCatalog(), schema, table, null);
    try {
        while (resultSet.next()) {
            columns.put(resultSet.getInt("ORDINAL_POSITION"),
                    resultSet.getString("COLUMN_NAME"));
        }
    } finally {
        resultSet.close();
    }
} catch (SQLException e) {
    throw new RuntimeException(
            String.format("エラー. テーブル名 = [%s]",
                    table), e);
}

主キーの取得

テーブルに対応する主キー情報を取得するコード。

MySqlは、主キーが定義されていないテーブルがあるとSQLExceptionを投げてくれる。
0件取得でいいのに・・・。

Map<Integer, String> primaryKeys = new TreeMap<Integer, String>();
try {
    DatabaseMetaData metaData = con.getMetaData();
    // testスキーマのtest_tableの主キー情報を取得
    ResultSet resultSet = metaData.getPrimaryKeys(
            con.getCatalog(), "test", "test_table");
    try {
        while (resultSet.next()) {
            primaryKeys.put(resultSet.getInt("KEY_SEQ"),
                    resultSet.getString("COLUMN_NAME"));
        }
    } finally {
        resultSet.close();
    }
} catch (SQLException e) {
    // NOP
}

【MySql】DataSouceを使用してデータベース接続

MySqlのJDBCドライバ(Connector/J)を使用してデータベース接続を行う方法。

DataSourceのインスタンスを生成して接続先情報を設定

// MysqlDataSourceのインスタンスを生成
MysqlDataSource dataSource = new MysqlDataSource();
// 接続先情報として、URLとユーザ、パスワードを設定
dataSource.setURL("jdbc:mysql://localhost/test");
dataSource.setUser("root");
dataSource.setPassword("password");

java.sql.Connectionの取得

Connection connection = dataSource.getConnection();

取得したConnectionを使用してSQLを実行

// JDBCのAPIを使ってSQL文(testスキーマのテーブル名をリスト表示する)を実行。
PreparedStatement statement = connection.prepareStatement(
        "select * "
        + "from INFORMATION_SCHEMA.TABLES"
        + " where table_schema = 'test'");
ResultSet set = statement.executeQuery();
while (set.next()) {
    System.out.println("set.getString(1) = " + set.getString("table_name"));
}