The options for our system's selection box are stored in a database table, which roughly contains the ID of the selection box and the data for selecting options, roughly the following table. Where FieldID is of type int
, Optionval is int
, Optionname is varchar
.
FieldID |
Optionval |
Optionname |
1 |
0 |
Beijing |
1 |
1 |
Nanjing |
1 |
2 |
Tianjin |
1 |
3 |
Hangzhou |
2 |
0 |
Ramen |
2 |
1 |
Rice |
Today, when it comes to the requirements, we don't pay attention to the type of the corresponding field, and the SQL is spliced directly through Java code, roughly as follows.
new StringBuilder();sqlBuilder.append("UPDATE tablename SET") .append(optionFieldid) .append(" = ‘") .append(optionValue); .append("‘");
OptionValue string is the ‘‘
value that he stored in the database becomes 0, originally thought should turn this field into ‘‘
, then I noticed that this field is type int, an implicit type conversion occurred. This summarizes the implicit type conversion of the relevant database, and in the following development process, you need to pay more attention to the type of database, to avoid unexpected results.
Oracle
Oracle follows the following data type conversion rules:
- For
insert
and update
operations, Oracle converts the value to the type of the affected column.
- For
SELECT FROM
Operations, Oracle converts the type of the column's value to the type of the target variable.
- When manipulating the value of a numeric type, Oracle usually adjusts the precision to accommodate the maximum capacity. In the case of a number type, the results of the operation of the numeric types may differ from the previous table.
When comparing string types to numeric types, Oracle converts string types to numeric types.
selectsysdatefromwhere1.2‘1.2‘; //正确selectsysdatefromwhere1.2‘1.2a‘;//ORA-01722: 无效数字
- Comparisons using a string or
NUMBER
type and floating-point type are imprecise. Because strings and NUMBER
types use decimal
precision, and floating-point numbers use binary
precision.
- When converting data of the CLOB type to
VARCHAR2
such a type, or BLOB
converting to RAW
data, if the converted data is larger than the target type, an error will be made.
- When you
timestamp
convert a type to a date
type, the fractional seconds part is staged.
- From the
BINARY_FLOAT
conversion to BINARY_DOUBLE
be accurate.
BINARY_DOUBLE
when converting to a BINARY_FLOAT
type, the BINARY_ DOUBLE
BINARY_FLOAT
conversion is inaccurate if more than the supported precision is used.
When a string value is compared to a date
value, the string value is converted to a date
type. For this scenario we should not rely on implicit conversions, which should be used TO_DATE
for display conversions, and the conversion format followed by implicit conversions is as follows:
selectfrom nls_session_parameters WHERE parameter=‘NLS_DATE_FORMAT‘; //DD-MON-RR
So in the Chinese environment, use the following, the use of other formats will be error ORA-01861: 文字与格式字符串不匹配
.
select 1 + 1 from dual where to_date(‘2018-1-15 12:23:23‘,‘yyyy-mm-dd hh24:mi:ss‘) > ‘15-1月 -18‘;
- When you use SQL or a function, the passed parameter is not the type that the parameter accepts, then it converts this argument to the type accepted by the function.
When a string and a non-string type are compared or arithmetic operations, the string is converted to the type of the column data. When both sides of the arithmetic operation are CHAR/VARCHAR2
and NCHAR/NVARCHAR2
, Oracle converts them to NUMBER
.
select‘123‘‘123.2‘from dual; //246.2
Oracle Implicit type Conversion