Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "oracle how to find a field with blank data", the content is easy to understand, clear, hope to help you solve the doubt, the following let the editor lead you to study and learn "oracle how to find a field with blank data" this article.

Problem description: synchronize a table from oracle to Aliyun rds, and you are surprised to report an error that violates a unique key, and then check the error data in detail. The results are as follows: there are really two.

MySQL [bidinfo] > select record_id, login_id from meminfo.t_member_info where login_id='woaini1314'

+-+ +

| | record_id | login_id |

+-+ +

| | 15142804 | woaini1314 |

| | 78124319 | woaini1314 |

+-+ +

2 rows in set (0.00 sec)

Then according to id query in oracle, one of them is found with spaces and one without spaces.

Select record_id, login_id from infoservice.t_member_info where record_id in (15142804, 78124319)

This is troublesome, this is user information, can not be casually deleted, guess is that the user in the registration, hit more than a space, and then found that the login does not go up, re-registered a user with the same name without spaces, however, oracle can identify spaces, but mysql can not identify spaces, we happen to have member login log, after inquiry is indeed guessed like this. So how to count the primary key record_id of users with spaces?

Specific description:

Liuwenhe and liuwenhe+ spaces

Because spaces are identified in oracle, the results of the following two sql checks are not the same.

Select record_id, login_id from t_member_info where login_id='woaini1314'

Select record_id, login_id from t_member_info where login_id='woaini1314'

But spaces are not recognized in mysql, and the result of the following query is the same. Both are two.

MySQL [bidinfo] > select record_id, login_id from meminfo.t_member_info where login_id='woaini1314'

+-+ +

| | record_id | login_id |

+-+ +

| | 15142804 | woaini1314 |

| | 78124319 | woaini1314 |

+-+ +

2 rows in set (0.00 sec)

MySQL [bidinfo] > select record_id, login_id from meminfo.t_member_info where login_id='woaini1314'; +-+-+

| | record_id | login_id |

+-+ +

| | 15142804 | woaini1314 |

| | 78124319 | woaini1314 |

+-+ +

2 rows in set (0.00 sec)

The problem arises. This login_id is a unique key, and there is no conflict in oracle. Migrating to rds violates the unique key.

The following is how to find the record_id with spaces in oracle in order to delete the data with spaces that violate the unique key from mysql.

One: first find out the problematic login_id (login_id after removing spaces)

Select count (record_id), trim (login_id) from infoservice.t_member_info group by trim (login_id) having count (record_id) > 1

Two: then find out all the problematic login_id (including those with and without spaces) and put them in a temporary table

Create table liuwenhe.t_mmeber_info_bak as select record_id, login_id from t_member_info where trim (login_id) in (select login from (

Select count (record_id), trim (login_id) login from infoservice.t_member_info group by trim (login_id) having count (record_id) > 1))

Finally, we use the results of the first two steps to find out the corresponding record_id of the data with spaces in the problematic login_id.

Select record_id from t_mmeber_info_bak where login_id not in (select trim (login_id) from t_mmeber_info_bak)

Four: import into txt file, and then use lord to enter mysql.

Finally, delete the corresponding data in mysql according to record_id.

The above is all the content of the article "how to find data with spaces in a field by oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.