INSERT INTO performance_history_15_minutes SELECT * FROM performance_history_1_minute;
INSERT INTO performance_history_15_minutes (ip_address,if_index,collection_time,
if_in_octets,if_in_ucast_pkts,if_in_n_ucast_pkts,
if_in_discards,if_in_errors,if_in_unknown_protos,
if_out_octets,if_out_ucast_pkts,if_out_n_ucast_pkts,
if_out_discards,if_out_errors,if_out_q_len) SELECT ip_address,if_index,collection_time,
if_in_octets,if_in_ucast_pkts,if_in_n_ucast_pkts,
if_in_discards,if_in_errors,if_in_unknown_protos,
if_out_octets,if_out_ucast_pkts,if_out_n_ucast_pkts,
if_out_discards,if_out_errors,if_out_q_len FROM performance_history_1_minute;
BIGINT [UNSIGNED]
Integer data. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. Storage size is 8 bytes.
INT [UNSIGNED]
Integer data. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. Storage size is 4 bytes.
INTEGER [UNSIGNED]
This is a synonym for INT.
MEDIUMINT [UNSIGNED]
Integer data. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215. Storage size is 3 bytes.
SMALLINT [UNSIGNED]
Integer data. The signed range is -32768 to 32767. The unsigned range is 0 to 65535. Storage size is 2 bytes.
TINYINT [UNSIGNED]
Integer data. The signed range is -128 to 127. The unsigned range is 0 to 255. Storage size is 1 byte.
Reference:
http://www.ispirer.com/doc/sqlways38/Output/SQLWays-1-190.html
mysql> select * from performance_history_1_minute;
ERROR 1030: 테이블 handler에서 127 에러가 발생 하였습니다.
mysql>
mysql> REPAIR TABLE performance_history_1_minute;
+------------------------------------+--------+----------+----------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------------------------+--------+----------+----------------------------------------+
| mynms.performance_history_1_minute | repair | info | Wrong bytesec: 0-0-0 at 7660; Skipped |
| mynms.performance_history_1_minute | repair | warning | Number of rows changed from 231 to 227 |
| mynms.performance_history_1_minute | repair | status | OK |
+------------------------------------+--------+----------+----------------------------------------+
3 rows in set (0.01 sec)
mysql>
문제
DATETIME 형식의 열에 0에 해당하는 값을 INSERT하고,
자바에서 SELECT하였을 때, 다음과 같은 예외에 직면하게 된다.
해결
JDBC 연결 시 사용하는 URL에 zeroDateTimeBehavior=convertToNull 옵션을 추가한다.
다음은 vsms라는 데이터베이스 내에 test라는 테이블에 한글 데이터를 넣는 예제 프로그램이다.
public class MySQLKoreanInsertDemo {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (ClassNotFoundException e) {
System.err.println(e);
} catch (InstantiationException e) {
System.err.println(e);
} catch (IllegalAccessException e) {
System.err.println(e);
}
String server = "localhost";
String dbname = "vsms";
String username = "user1";
String password = "passwd1";
String url = "jdbc:mysql://" + server + "/" + dbname
+ "?user=" + username
+ "&password=" + password
+ "&useUnicode=true&characterEncoding=euc_kr";
try {
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
String query = "INSERT INTO test (data) VALUES ('임정묵')";
stmt.executeUpdate(query);
stmt.close();
conn.close();
} catch (SQLException e) {
System.err.println(e);
}
}
}
위에서 "&useUnicode=true&characterEncoding=euc_kr"가 필요함에 유의한다.
|
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | |||
| 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 26 | 27 | 28 | 29 | 30 | 31 |
