| |
Speaking of data type conversion, in the development of the case, in the database, too, before the simple comparison of MySQL, and Oracle data type conversions
But after that time finished, there is a reader casually asked one why, why? It seems he is still scanty, he said the rules, no rules no standards, the idea is beyond reproach, but I think it is again a look and see what can harvest, the next I can not guarantee the correctness of the content, I hope everyone out Discrimination also wish to make comments, after all, it is hoped the problem thoroughly understand it.
Language first developed languages have implicit conversion of data types, which is particularly evident in java, after all, a mission of carrying too much so huge, is a strongly typed language, data type conversion is a particularly important part of the . The Java data type conversion rules are the following.
// Conversion rules: range from small storage type to store range type.
// Specific rules: byte -> short (char) -> int -> long -> float -> double
Joseph himself tinkling a bit, write a simple little program to show proof, I will not explain this program java.
public class Test {
public static void main (String args []) {
/ * 1 * / System.out.println ( "aa");
/ * 2 * / System.out.println ( 'a');
/ * 3 * / byte a = 10;
/ * 4 * / System.out.println (a);
/ * 5 * / char b = 'b';
/ * 6 * / int c = b;
/ * 7 * / System.out.println (b);
/ * 8 * / System.out.println (c);
}
}
The output of this program
aa
a
10
b
98
The purpose is to write,
First row, the second row of single quotes, double quotes need to do is to indicate that it is a variable value, the effect of both is the same at this time.
Line 3 initializes a byte variable, then the output of this time or byte
However, Line 5 declares a type char variable, and then do a type of implicit conversion on line 6, the output of the character b in the seventh row, but the output is on line 8
Through this simple example can be found in data types really do implicit conversion, and single quotes, double quotes role in this case is consistent, that is marked variables.
Because the cost of viewing data type conversion in Java is still relatively difficult, we can come analogy in the database.
First or repeat the previous test, prepare a batch of data. Create a table and insert some values.
create table test (id1 number, id2 varchar2 (10));
begin
for i in 1..100 loop
insert into test values (i, chr (39) || i || chr (39));
end loop;
commit;
end;
/
create index ind1_test on n1.test (id1);
create index ind2_test on n1.test (id2);
Then gather the statistics.
exec dbms_stats.gather_table_stats ( 'TEST', 'TEST', CASCADE => TRUE);
This time to view the execution plan
explain plan for select * from test where id1 = '2';
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------- --------------------------------------------------
Plan hash value: 2759464289
-------------------------------------------------- ---------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- ---------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 20 | 1 (0) | 00:00:01 |
| * 2 | INDEX RANGE SCAN | IND1_TEST | 1 | | 1 (0) | 00:00:01 |
-------------------------------------------------- ---------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-------------------------------------------------- -----------
2 - access ( "ID1" = 2)
Through this can indeed see section 2 predicate information - access ( "ID1" = 2) it has been automatically made the conversion, this time a triggered an index scan.
But the process is still not see traces of data type conversion, we do have a problem seemingly example, to trigger it. Although id1 bit int type, but the use of character to trigger.
SQL> explain plan for select * from test where id1 = 'A';
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------- --------------------------------------------------
Plan hash value: 2759464289
-------------------------------------------------- ---------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- ---------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 20 | 1 (0) | 00:00:01 |
| * 2 | INDEX RANGE SCAN | IND1_TEST | 1 | | 1 (0) | 00:00:01 |
-------------------------------------------------- ---------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------
2 - access ( "ID1" = TO_NUMBER ( 'A'))
We can see the predicate information has been changed. 2 - access ( "ID1" = TO_NUMBER ( 'A')) from this place we can see that really triggered a to_number operation.
The optimizer at this time, although the trigger, but when sql running, it will report an error, this time you can see that Oracle is still quite strict.
SQL> select * from test where id1 = 'A';
select * from test where id1 = 'A'
*
ERROR at line 1:
ORA-01722: invalid number
If you use double quotes, execution plan will be throwing error.
SQL> explain plan for select * from test where id1 = "A";
explain plan for select * from test where id1 = "A"
*
ERROR at line 1:
ORA-00904: "A": invalid identifier
Visible single and double quotes is very different meaning in Oracle representative.
Let's look at MySQL's performance.
Or create a simple table, insert some data.
> Create table test (id1 int, id2 varchar (10));
> Insert into test values (1, '1');
> Insert into test values (2, '2');
> Insert into test values (3, '3');
> Commit;
> Create index idx_id1 on test (id1);
> Create index idx_id2 on test (id2);
This time execution plan can be found walking the index
> Explain select * from test where id1 = '1';
+ ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
| 1 | SIMPLE | test | ref | idx_id1 | idx_id1 | 5 | const | 1 | Using where |
+ ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
1 row in set (0.00 sec)
And if the view is id1 of type varchar, also walked the index.
> Explain select * from test where id1 = 'a';
+ ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
| 1 | SIMPLE | test | ref | idx_id1 | idx_id1 | 5 | const | 1 | Using where |
+ ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
1 row in set (0.00 sec)
If the difference is greater id1 = 'a', it is possible to perform normal, but there is no matching record.
> Select * from test where id1 = 'a';
Empty set (0.00 sec)
And if the single quotes to double quotes, and can operate normally.
> Select * from test where id1 = "a";
Empty set (0.00 sec)
And the case of double quotation marks, the execution plan is not a problem.
> Explain select * from test where id1 = "a";
+ ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
| 1 | SIMPLE | test | ref | idx_id1 | idx_id1 | 5 | const | 1 | Using where |
+ ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
1 row in set (0.00 sec)
MySQL can be seen in the wider scope this time seems to be, not only in MySQL with single quotes, double quotes, but also often see this * symbol.
Such flexibility can declare some changes in MySQL, for example, is not appropriate, for example, we create a table, a field int, int type directly in the following way to write, certainly throwing error.
> Create table test1 (int int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int int)' at line 1
crea 'at line 1
* You can add, it can be identified.
> Create table test1 ( `int` int);
Query OK, 0 rows affected (0.00 sec)
Contrast this span of a little big, but with some little trick seems to be able to be seen in these types of conversions, the optimizer side of the trigger situation. Redouble their efforts to continue to explore. |
|
|
|