批量修改oracle的varchar2类型参数(Byte修改为Char)
varchar2(byte) 和varchar2(char) 的区别
varchar2(byte) 存储的是字节。
varchar2(char) 存储的是字符。
1.为 AL32UTF8 时,1个汉字是1个字符,1个汉字占3个字节,varchar2(2 byte) 一个汉字也不能存,varchar2(2 char) 能存2个汉字。
2.为 ZHS16GBK时,1个汉字是1个字符,1个汉字占2个字节,varchar2(2 byte) 能存1个汉字,varchar2(2 char) 能存2个汉字。
当类型为varchar2(2 byte)时,存不下一个中文
存字符是正常的
当类型为varchar2(2 char),不可存三个中文
前景:mysql迁移oracle数据库,在生成建表语句的时候没有指定类型,默认生成type参数,可能会出现中文在mysql数据库中可以插入的业务,到了oracle版本不行,所以需要替换varchar2参数
提前改造,避免出现此类状况:
1.varchar2默认参数类型是根据nls_length_semantics来设置,可通过以下Sql查询当前数据库的默认参数
select name,value from v$parameter where name='nls_length_semantics';
2.默认参数的修改(可将当前数据库的默认参数修改为char)
ALTER SESSION SET nls_length_semantics=char;
3.批量修改当前数据库用户下所有表的varchar2(byte)字段修改为varchar2(char)
import org.junit.Test;
import java.sql.*;
/**
* @author 熊
* #Description OracleMigration
* #Date: 2021/8/14 10:40
* oracle的carchar2字段批量修改参数类型
*/
public class OracleMigration {
// 几个数据源就配几个用户
protected static String url = "jdbc:oracle:thin:@ip:1521/ORCLPDB1";
protected static String username = "username";
protected static String password = "password ";
protected Connection getConn() throws SQLException {
return (Connection) DriverManager.getConnection(url, username, password);
}
@Test
public void test(){
try {
// 在循环查询结果集时,如果此时用同一个游标去执行sql,会中断当前结果集,可能只需要一个sql,创建多个statement(没测试)
Connection conn = this.getConn();
Connection conn2 = this.getConn();
Connection conn3 = this.getConn();
// 查询当前用户下所有表
String tables = "SELECT TABLE_NAME FROM USER_TABLES";
Statement statement = conn.createStatement();
Statement statement2 = conn2.createStatement();
Statement statement3 = conn3.createStatement();
ResultSet table = statement.executeQuery(tables);
while (table.next()){
String tableName = table.getString(1);
String sql =
"select t1.column_name,data_type,char_length" +
" from user_col_comments t2 left join user_tab_columns t1 " +
" on t1.table_name = t2.table_name and t1.column_name = t2.column_name " +
" where t1.table_name = '"+tableName+"'";
ResultSet rs = statement2.executeQuery(sql);
while (rs.next()) {
String type = rs.getString(2);
if(type.equals("VARCHAR2")){
String column = rs.getString(1);
int length = rs.getInt(3);
String upSql = "alter table \""+tableName+"\" modify (\""+ column+"\" VARCHAR2("+length+" char))";
System.out.println(upSql);
statement3.executeUpdate(upSql);
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}