mysql 기반 sql 문에서 테이블 간 필드 값을 복사할 때 발생하는 몇 가지 문제

오랜만에 정원에 가보게 되었어요. 눈 깜짝할 새에 벌써 2017년 3월이 다가왔네요. 너무 부끄러워요. 어제 퇴근 전 기술사장이 갑자기 테이블 구조를 바꾸고 싶다고 말하며 한 테이블의 필드 값을 다른 테이블의 특정 필드에 복사할 수 있는지 물어봤습니다. 한 입이지만 실제로는 테이블 간 필드였습니다. 그래서 어젯밤에 야근을 하고 잠시 바이두를 검색한 뒤 로컬에서 테스트를 해보니 실제로 알아낸 내용이 있으니 참고삼아 이 SQL문을 적어보겠습니다.

1. 배경 및 요구 사항

a_user와 b_user 두 테이블의 구조는 다음과 같습니다.

a_user

+--------+-------------+------+-----+---------+----------------+
| Field    | Type          | Null  |  Key | Default | Extra             |
+--------+-------------+------+-----+---------+----------------+
| id_a     | int(11)       | NO   |  PRI | NULL    | auto_increment|
| a_name| varchar(45)| YES  |        | NULL     |                     |
+--------+-------------+------+-----+---------+----------------+

b_user

+--------+-------------+------+-----+---------+----------------+
| Field    | Type          | Null  | Key  | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id_b     | int(11)       | NO   | PRI  | NULL    |auto_increment|
| a_id     | int(11)       | NO   | MUL | NULL    |                      |
| b_name| varchar(45)| YES  |        | NULL    |                      |
+--------+-------------+------+-----+---------+----------------+

두 테이블 간의 관계: 테이블 b_user의 a_id 외래 키는 테이블 a_user의 기본 키 id_a를 참조합니다.

레코드는 다음과 같습니다.

a_user

+------+--------+
| id_a | a_name |
+------+--------+
|    1      |            |
|    2      |            |
|    3      |            |
|    4      |            |
+------+--------+

b_user

+------+------+--------+
| id_b | a_id | b_name |
+------+------+--------+
|    1      |    1      | 张三   |
|    2      |    2      | 李四   |
|    3      |    2      | 李四   |
|    4      |    3      | 王五   |
|    5      |    3      | 王五   |
|    6      |    3      | 王五   |
|    7      |    4      | 赵六   |
|    8      |    4      | 赵六   |
+------+------+--------+

요구 사항: b_user 테이블의 b_name 필드 값을 복사합니다. a_user 테이블 a_name에.

2, Baidu에서 발생한 문제를 해결합니다.

Baidu가 확인하고 찾은 내용을 사용하세요. 보다 정확하게는 sql 문입니다.

update a_user set a_name = (select b_name from b_user where id_a = a_id);

이 문은 아마도 a_user 테이블의 a_name 필드를 업데이트하고 b_user 테이블의 b_name 필드 값을 값 소스로 사용하되 위의 내용을 직접 실행한다는 의미일 것입니다. 명령문 mysql이 다음 오류를 보고할 때:

ERROR 1242 (21000): Subquery returns more than 1 row

는 업데이트 문이 예상하는 데이터 소스 행의 수를 의미합니다. a_user 테이블과 동일해야 합니다. 행 수는 4인데 위 서브 쿼리의 결과는... 잠깐, 위 서브 쿼리가 실행될 수 있나요? 물론 그렇지 않습니다. 실제로 위 하위 쿼리는

select b_name from b_user left join a_user on a_id = id_a;

와 동일합니다. 그러나 반환되는 결과는 8행입니다. a_user 테이블의 행 수가 다릅니다.

(1) 데이터 소스에서 중복 행 제거

그런 다음 이 문제를 먼저 해결하고 중복 레코드를 제거합니다. select 고유한 a_id, b_name from b_user left join a_user on a_id = id_a; 반환되는 결과는 다음과 같습니다.

+------+--------+
| a_id | b_name |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
|    4 | 赵六   |
+------+--------+

결과는 2개 열입니다. 다음 문을 실행하면 오류가 보고됩니다.

1 update a_user set a_name = (select distinct a_id, b_name from b_user left join a_user on a_id = id_a);
2 ERROR 1241 (21000): Operand should contain 1 column(s)

그럼 위 결과를 b_name만 포함하는 열로 변경하려면 어떻게 해야 할까요?

(2) Unique를 사용하여 a_id로 중복 행을 제거한 후 추가 a_id 열이 있습니다.

이 문제는 중첩으로 쉽게 해결할 수 있습니다. 하위 쿼리 한 번만 클릭하세요.

select b_name from (select distinct a_id, b_name from b_user left join a_user on a_id = id_a) t;

좋아, 업데이트 문을 다시 시도해 보세요.

1 update a_user set a_name = (select b_name from (select distinct a_id, b_name from b_user left join a_user on a_id = id_a) t);
2 ERROR 1242 (21000): Subquery returns more than 1 row

위에서 서브쿼리 결과와 업데이트된 행 개수가 일치하지 않는 문제가 보고된 것을 볼 수 있는데, 위 서브쿼리 select b_name from (select distinct a_id, b_name from b_user left join a_user on a_id = id_a) t; 결과는

+--------+
| b_name |
+--------+
| 张三   |
| 李四   |
| 王五   |
| 赵六   |
+--------+

중복 행이 제거되지 않았나요?

(3) 하위 쿼리 중첩 및 sql 문 실행 순서

위 문제를 분석합니다. 이제 select에 하위 쿼리가 두 개 있습니다. 문에서 외부 선택은 내부 선택을 쿼리할 데이터 소스로 사용합니다. 내부 선택과 외부 선택은 모두 별도로 실행될 때 예상된 결과를 반환할 수 있습니다. 그렇다면 업데이트를 실행할 때 왜 다음 오류가 발생합니까? >1242 (21000): 하위 쿼리 반환 1개 행 ?

다음은 내 추측입니다. 업데이트 문은 한 줄씩 실행되므로 첫 번째 레코드가 업데이트되면 업데이트는 선택 항목에서 첫 번째 레코드에 해당하는 레코드를 얻을 것으로 예상합니다. 기록된 데이터, 즉 update a_user set a_name = value source where id_a = a_id; 그러면 자격을 부여하기 위해 where 문을 추가해야 합니다:

update a_user set a_name = (select b_name from (select distinct a_id, b_name from b_user left join a_user on a_id = id_a) t where 
t.a_id = id_a);

그렇습니다.

+------+--------+
| id_a | a_name |
+------+--------+
|    1     | 张三   |
|    2     | 李四   |
|    3     | 王五   |
|    4     | 赵六   |
+------+--------+

3. 먼저 여기에 결과를 써보겠습니다.

솔직히 아직은 불안해요. 여기에는 SQL 중첩 쿼리, SQL 문 실행 순서, 업데이트 문 실행 프로세스 및 기타 SQL 지식이 포함됩니다. 간단히 말해서 Baidu와 내 실수에 의존하여 SQL을 생각해 냈지만 로컬에서만 테스트하지 않았습니다. 프로덕션 환경에서 사용하기 위해 이 SQL의 실행 효율성에 대해서는 먼저 기록하고 나중에 연구하겠습니다. 데이터베이스를 전공하는 학생들이 조언을 해주셨으면 좋겠습니다.

위 내용은 mysql 내용을 기반으로 SQL 문에서 테이블 간의 필드 값을 복사할 때 발생하는 몇 가지 문제입니다. 더 많은 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!