MySQLの文字型 ～ varcharではなくintで返して欲しい

Question

まず以下のSQLでテーブルを作成します。

MySQL 5.7.25-log
JDBC jdbc_4.3.15
Java 1.8

CREATE TABLE `new_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `a` INT NULL,
  `b` INT NULL,
  PRIMARY KEY (`id`));


insert into `new_table`
(`a`, `b`)VALUES
(0,1),
(1,0),
(0,0),
(1,1),
(0,1),
(1,0),
(0,0),
(1,1),
(0,1),
(1,0),
(0,0),
(1,1);

次に、このSQLで結果を返してもらいます。具体的には、aが1であれば、5点を上げるというものです。

SELECT 
    COALESCE(SUM((CASE
                WHEN (`a` = 1) THEN 5
                ELSE NULL
            END)),
            0) AS `aa`
FROM
    new_table;

ここから、私のやりたいことです。

これによって返ってくるaaは、intではなく、varchar型のようです。
これをintで返してもらいたいと思います。どこか方法を変えればよろしいでしょうか？
よろしくおねがいします。

---

（追加）
SQLのプロフェッショナルより回答が付きましたので、詳しい事情をお伝えしたいと思います。

実はこれはJavaと連携しており、JavaからみたMySQLのメタデータは、intではなく、Stringとして解釈しているようです。
以下のソースコードはJavaをご存じない方にとっては難しいかもしれませんが、要はMySQLの方でintとして認識してもらえるように作れたら、Javaの知識は特に必要ないと思っております。

		String query = "SELECT * from `new_table` limit 0, 1;";
		Map<String, String> mp = new LinkedHashMap<String, String>();

		try {

			Class.forName("com.mysql.jdbc.Driver");

			Connection conn = DriverManager.getConnection(Constant.JDBC_URL, Constant.PASS, Constant.ANGO);
			java.sql.Statement st = conn.createStatement();
			ResultSet resultSet = st.executeQuery(query);

			ResultSetMetaData metadata = resultSet.getMetaData();
			int column_count = metadata.getColumnCount();

			for (int i = 0; i < column_count; i++) {

				mp.put(metadata.getColumnName(i + 1), metadata.getColumnTypeName(i + 1));

			}

		} catch (Exception e) {

			System.out.println("error");
		}

Comments

[rubytomato]

MySQLのバージョン、JDBC Driverのバージョン、Javaのバージョンを質問に追記ください。
また、MySQLのInt型をJDBCドライバがVARCHARと認識するということは考えにくいので、念のため
`show create table new_table`の結果も教えてください。

[xebme]

MariaDb、jdbcの組み合わでMetadataを取得するとBIGINT型でした。`aa`にSQLのcast(... as int)を適用するとDECIMAL型になりました。
たとえVARCHAR型でも内容が数字のみならResultSet#getLongやResultSet#getIntで数値を取得できるのですが、MetaDataを使うフレームワークをお考えですか？それなら上の CASTを考えてみたら。
MyslとJDBCドライバ、Javaのバージョンは？

[tiqua_nibio]

`show create table new_table`

CREATE TABLE `new_table` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `a` int(11) DEFAULT NULL,\n `b` int(11) DEFAULT NULL,\n `new_tablecol` varchar(45) DEFAULT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

でした。

[tiqua_nibio]

cast、確かにできるかもしれないと思いますので試してみます。

[rubytomato]

追記ありがとうございました。なるべく同じ環境を用意して動作確認を行った結果を回答しました。
それと一点確認させてほしいのですが、質問文にJDBC ドライバを

> JDBC jdbc_4.3.15

と書かれていますが、MySQL Connector/jに4.3.15というバージョンは無いように思います。
使われているドライバのjarファイル名は何になりますか？

[tiqua_nibio]

mysql-connector-java-5.1.46.jarのようです。

[rubytomato]

> mysql-connector-java-5.1.46.jarのようです。

こちらのバージョンでも確認しましたが、MySQLのInt型はJavaのIntegerにマップされました。

また、以下のリファレンスでも確認しましたがMySQLのIntがJavaのjava.lang.Integerに変換されるのは仕様のようです。

5.5 Java, JDBC and MySQL Types
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html

回答の方にも書きましたが、プログラムの問題というより環境に起因する気がします。
なので、他に確認することといったら、新しいデータベースを構築し、なるべくカスタマイズしない状態で接続確認するということくらいでしょうか。

[tiqua_nibio]

有益な情報のページを教えていただき、ありがとうございます。詳しく書かれており、参考になります。

時間ができましたらデータベースの再構築など、色々と検証してみたいと思います。

Answer 1

ResultSet
ResultSetのgetXXX()メソッドについて誰も書いていないので指摘しておきます。ソースコードに以下を追加しました。getLong()、getBigDecimal()で数値を取得します。

for (int i = 0; i < column_count; i++) {
  mp.put(metadata.getColumnName(i + 1), metadata.getColumnTypeName(i + 1));
}

// ここから追加 -----------------------------------------------------------           
System.out.println(mp);
if (resultSet.next()) {
  String b = "", c= "";
  for (String key : mp.keySet()) {
    b += ((b.length()==0)?"":",")+resultSet.getLong(key);
    c += ((c.length()==0)?"":",")+resultSet.getBigDecimal(key);
  }
  System.out.println(b);
  System.out.println(c);
}

// ここまで追加 -----------------------------------------------------------

JDBCを使って実行したSQL。castを使って文字列を数値に、数値を文字列に変換。

"select cast('987654321' as int) as `i`, cast(12345 as char) as `vc`"
"select cast('9999999990' as decimal) as `i`, cast(3456 as char) as `vc`"

実行結果

{i=INT, vc=VARCHAR}
987654321,12345
987654321,12345
{i=DECIMAL, vc=VARCHAR}
9999999990,3456
9999999990,3456

たとえ数値がvarchar型でも getLong()、getBigDecimal()で変換できるのでJava側で問題になりません。

Comments

[momon-ga]

他の方の回答のコメント欄を見ると
値でなくメタデータをINTで受け取りたいようです。
> メタデータは
> {id=INT, a=VARCHAR, b=VARCHAR}
> と出ます。ですので、VARCHARとして認識しているようです。これをINTになるようにしたいです。

castでintが取れるようなので解決に向かいそうですね。

[tiqua_nibio]

ありがとうございます。当方、Javaで確かになんとか書けるのではありますが、tableは百個ぐらいあります。それなら、SQLを少し修正してから、どのtableにも適用できるような形（メソッド）にしたいと考えておりました。

[xebme]

ResutSetのメタデータを利用する機能（フレームワーク）は、メタデータだけに頼ると硬直的。型変換機能も検討されるとよいでしょう。BeanUtilsに型変換機能があったと記憶しています。(釈迦に説法かもしれません)

VARCHARは、たとえば、VIEWのネストが深いと型認識がゆるくなるのか、型不明ならVARCHARで値を保持すれば最も安全だろうと想像しました。根本的な解決を期待します。

[tiqua_nibio]

確かにmetadataに頼ると良くないかもしれませんね。型変換が必要な場合、型変換をするような形に持っていく実装が必要かもしれません。検討してみます。

Answer 2

以下の環境とプログラムで動作確認を行いました。この結果の通りINTをVARCHARと認識することはなかったので、原因は少なくともJavaプログラムには無いように思います。

環境

• Windows 10
• MySQL 5.7.26 Community Edition
• Java 1.8
• MySQL Connector/j 5.1.48

MySQL側

テーブルとテストデータの作成。

CREATE TABLE `new_table` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a`  int(11) DEFAULT NULL,
 `b`  int(11) DEFAULT NULL,
 `new_tablecol` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARSET=utf8;

INSERT INTO `new_table` (`a`, `b`) VALUES
(0,1),
(1,0),
(0,0),
(1,1),
(0,1),
(1,0),
(0,0),
(1,1),
(0,1),
(1,0),
(0,0),
(1,1);

作成したテーブルのスキーマ情報を確認。

> SELECT column_name, data_type,column_type FROM information_schema.columns WHERE table_name = 'new_table';
+--------------+-----------+-------------+
| column_name  | data_type | column_type |
+--------------+-----------+-------------+
| id           | int       | int(11)     |
| a            | int       | int(11)     |
| b            | int       | int(11)     |
| new_tablecol | varchar   | varchar(45) |
+--------------+-----------+-------------+
4 rows in set (0.00 sec)

Javaで実行するSQLをMySQL Clientからも実行して確認。

> SELECT * FROM `new_table` LIMIT 0, 1;

+----+------+------+--------------+
| id | a    | b    | new_tablecol |
+----+------+------+--------------+
|  1 |    0 |    1 | NULL         |
+----+------+------+--------------+
1 row in set (0.00 sec)

Java側

質問文に記載のプログラムとほぼ同じです。Eclipse上からJavaアプリケーションとして実行しました。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class MetaDataCheck {

	public static void main(String[] args) {

		Connection conn = null;
		try {
			String jdbcUrl = "jdbc:mysql://localhost:3306/test_db";

			String query = "SELECT * FROM `new_table` LIMIT 0, 1";
			//String query = "SELECT COALESCE(SUM((CASE WHEN (`a` = 1) THEN 5 ELSE NULL END)), 0) AS c FROM `new_table`";

			conn = DriverManager.getConnection(jdbcUrl, "test_user", "test_user");
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(query);

			ResultSetMetaData meta = rs.getMetaData();

			for (int i=0; i<meta.getColumnCount(); i++) {
				String columnName = meta.getColumnName(i+1);
				String columnTpeName = meta.getColumnTypeName(i+1);
				String columnClassName = meta.getColumnClassName(i+1);
				System.out.println(String.format("name:[%-13s] type:[%-8s] class:[%s]", columnName, columnTpeName, columnClassName));
			}
			System.out.println();

			while (rs.next()) {
				Integer id = rs.getInt("id");
				Integer a = rs.getInt("a");
				Integer b = rs.getInt("b");
				String n = rs.getString("new_tablecol");
				System.out.println(String.format("id:[%d] a:[%d] b:[%d] new_tablecol:[%s]", id, a, b, n));
				//BigDecimal c = rs.getBigDecimal("c");
				//System.out.println(String.format("c:[%f]", c));
			}

		} catch (Exception e) {
			e.printStackTrace(System.err);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace(System.err);
				}
			}
		}
	}

}

標準出力の内容
この内容の通りMySQLのInt型はJavaではIntegerにマップされています。

name:[id           ] type:[INT     ] class:[java.lang.Integer]
name:[a            ] type:[INT     ] class:[java.lang.Integer]
name:[b            ] type:[INT     ] class:[java.lang.Integer]
name:[new_tablecol ] type:[VARCHAR ] class:[java.lang.String]

id:[1] a:[0] b:[1] new_tablecol:[null]

また、下記のSQLL文を実行した結果も確認しました。

String query = "SELECT COALESCE(SUM((CASE WHEN (`a` = 1) THEN 5 ELSE NULL END)), 0) AS c FROM `new_table`";

JavaではBigDecimalにマップされています。

name:[c            ] type:[DECIMAL ] class:[java.math.BigDecimal]

c:[30.000000]

なお、以下の環境でも動作確認を行いましたが結果は変わりませんでした。

• MySQL 8.0.17 CE
• OpenJDK 13.0.2
• MySQL Connector/j 8.0.17

Comments

[tiqua_nibio]

色々とお手数おかけし、ありがとうございました。なぜ、私の方ではvarcharとして認識されるのか、未だに不明です。

Answer 3

NULLの時も0なので、1以外は0でも一緒と考えてCOALESCEを取りました。

SELECT SUM(CASE WHEN (a = 1) THEN 5 ELSE 0 END) AS aa FROM new_table;

COALESCEを使うケースも作成してみました。

SELECT SUM(COALESCE(CASE WHEN a = 1 THEN 5 ELSE NULL END, 0)) AS aa FROM new_table;

Comments

[tiqua_nibio]

SUMを使えば、たしかにintで返ってきているようです。

Answer 4

的外れかもしれませんが・・・

MySQLの関数COALESCEの戻り値は、そもそもvarcharをmetadataとして返すのでは？
実行時の結果に合わせて動的に変わらないような気がします。

で、対処方法としては型キャスト(cast関数)をすれば、いけるのでは？（動作未確認）

Comments

[tiqua_nibio]

おっしゃった回答の通りで、おおむね正しいようです。castは有効でした。

[tiqua_nibio]

ありがとうございました。みなさん色々とご教示いただき、どれをベストアンサーに選ぶか迷いましたが、一番早く正答を回答していただいた方につけようと思いました。

Answer 5

数値型で返却されそうですけど、直接SQLを試してみましたか？
Nullを挟むけどSum()がvarcharを返すとは思えません。
以下でも結果は同じはずですから、試してみて下さい。

SELECT 
    cast(SUM(CASE WHEN `a`=1 THEN 5 ELSE 0 END) as UNSIGNED) AS `aa`
FROM
    new_table

Comments

[tiqua_nibio]

結果は確かに同じなんですが、Javaから見ると、Stringとして見えるようなんです。viewでは、文字型を指定できないみたいなので、どうしたらいいか、困っています。
質問に補足いたしました。

[sazi]

いや、それは受け取るJava側の問題ですね。受け取る方での型を確認してみて下さい。

[tiqua_nibio]

他のカラム（例えばカラムそのものを取り出しており、関数を使っていない）
SELECT `a`,
COALESCE(SUM((CASE
WHEN (`a` = 1) THEN 5
ELSE NULL
END)),
0) AS `aa`
FROM
new_table;
とすれば、`a`のほうはintと認識されています。

[sazi]

javaでの受け取り時に暗黙変換されているんだと思いますけどね。
※SQLの結果がdecimalなので文字に変換しているんだと思いますけど。
取り敢えず、CAST()してみて下さい。（回答変更済み）

[tiqua_nibio]

ありがとうございます。castで成功しました。

Answer 6

mpはMap<String, String>ですので、"カラム名"="INT"と文字列になりますがその事でしょうか？
メタデータは"INT"という文字列になると思うのですが。

Comments

[tiqua_nibio]

ありがとうございます。
メタデータは
System.out.println(mp);
で
{id=INT, a=VARCHAR, b=VARCHAR}
と出ます。ですので、VARCHARとして認識しているようです。これをINTになるようにしたいです。

Answer 7

CREATE TABLE `new_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `a` INT NULL,
  `b` INT NULL,
  PRIMARY KEY (`id`));
  
  
  INSERT INTO new_table VALUES 
   ( 1,1,1)
   ,( 2,2,2);
   
   CREATE TABLE a as SELECT 
    COALESCE(SUM((CASE
                WHEN (`a` = 1) THEN 5
                ELSE NULL
            END)),
            0) AS `aa`
FROM
    new_table;
    
    CREATE TABLE b AS SELECT 
    SUM(CASE WHEN `a`=1 THEN 5 ELSE 0 END) AS `aa`
FROM
    new_table;

SHOW CREATE TABLE a;
SHOW CREATE TABLE b;

Table	Create Table
a	CREATE TABLE a ( aa decimal(23,0) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Table	Create Table
b	CREATE TABLE b ( aa decimal(23,0) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

双方ともにmysql判定は「decimal」でしたが、何をもとにvarcharと思ったのでしょうか?