외부 키를 사용하여 .xls 파일을 .sql로 가져오는 방법

외부 키를 사용하여 .xls 파일을 .sql로 가져오는 방법

Excel 스프레드시트가 있습니다.

id name  region  zone  
1  pokin Polon   Riny
2  lucy  yerni   kinta
...

그리고 나는 mysql 데이터베이스에 있는 테이블들이 있습니다.region_id그리고.zone_id대신 지역 및 구역의 ID에 대한 외부 키입니다.

사용자 테이블:

id name region_id zone_id
1  retre  1        1
...

영역 표:

id region_name
1   Polon
...

및 영역 테이블

id zone_name
1   kinta
...

엑셀 스프레드시트를 사용자 테이블로 가져와야 합니다.

1. 스프레드시트에 표시된 대로 테이블을 내보냅니다(이름 철자 포함).
2. MySQL - 테이블로 가져오기X,말합니다.
3. 다음 쿼리를 수행하여 원하는 테이블(이름 대신 숫자 사용)을 만들 때 "정규화"를 수행합니다.

이와 같은 것:

INSERT INTO users
        (id, name, region_id, zone_id)
    SELECT X.id, X.name, r.id, z.id
        FROM X
        LEFT JOIN region AS r ON r.region_name = X.region
        LEFT JOIN zone   AS z ON z.zone_name   = X.zone;

한다면id이다.AUTO_INCREMENT그러면 약간 다르게 하는 것이 좋을 것입니다. (떠납니다.)id의 범위 밖에INSERT그리고SELECT.)

사용한LEFT누락된 지역이나 구역이 있는 경우.이 경우, 당신은 다음과 같은 결과를 얻게 될 것입니다.NULLs또는 의 기본값region_id또는zone_id따라서 무언가를 고쳐야 한다는 것을 나타냅니다.

.xls 시트에서 영역 및 영역 이름을 해당 필드에 대한 데이터베이스의 실제 ID로 바꾸는 것이 좋습니다.그런 다음 xls 파일을 csv 파일로 내보낸 다음 mysqlimport를 사용하여 데이터베이스로 쉽게 가져올 수 있습니다.

mysqlimport --ignore-lines=1 \
        --fields-terminated-by=, \
        --local -u root \
        -p DatabaseName \
         YourExportedFile.csv

여기서 고려해야 할 몇 가지 사항:

1. 명령을 실행할 때 파일의 경로입니다.
2. 필즈 종단 문자
3. "-p DatabaseName"은 암호가 아니며 데이터베이스 이름입니다. 명령을 실행할 때 암호가 표시됩니다.

LOAD DATA INFILE을 사용하여 SQL 문이 있는 CSV 파일을 가져올 수 있습니다.

LOAD DATA INFILE "/home/user/YourExportedFile.csv"
INTO TABLE YOUR_TABLE
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

또한 그래픽 MySQL 클라이언트(mysql workbench, heideSql, mysqlpro 등)를 사용하는 경우 가져오기 기능을 대신 사용할 수 있습니다.

이러한 테이블이 없는 경우 먼저 세 개의 CSV 파일 users.csv, regions.csv, zone.csv를 생성합니다.

regions.csv 및 zones.csv에는 DB로 가져올 수 있는 단일 열이 있습니다. region_name 또는 zone_name.제 생각에 테이블은 다음과 같이 설치될 것 같습니다.AUTO_INCREMENTID 값이 알아서 처리됩니다.

Excel에서 이러한 파일을 만들려면 Data > Remove Duplicates를 사용하여 고유한 값을 얻습니다.phpMyAdmin 파일을 데이터베이스로 가져올 수 있는 권한이 있는 경우 이 두 테이블을 가져옵니다.

사용자 만들기:외부 키를 사용자 테이블로 가져오기 위해 다음을 사용하여 사용자 테이블을 만듭니다.

이름, 지역, 구역, region_id, zone_id

원래 파일을 이름, 지역, 영역 필드로 가져온 다음 외부 키를 업데이트할 수 있습니다.

UPDATE users as u SET region_id = (SELECT id FROM regions as r WHERE r.region_name = u.region) 

그런 다음 지역 및 구역 열을 삭제합니다.

일반적으로 이것이 제가 하는 일입니다.

삽입 스크립트를 생성할 Excel 수식을 만듭니다.자동 생성기가 될 수 있으니 그냥 건너뜁니다.당신의 ID가 A 셀에 있다고 가정하면, B 셀에 이름, C 셀에 지역, D에 구역이 각각 있습니다.

="INSERT INTO USER_TABLE(NAME, REGION_ID, ZONE_ID) VALUES ('"&B1&"',(select id
from region where region_name = '"&C1&"'), (select id from zone where 
zone_name = '"&D1&"')";

1을 만든 후 수식을 끝 행까지 끌어다 놓으면 스크립트를 복사하여 실행합니다.

제가 고려하는 사항:

1. 로컬, SIT, UAT, 운영 등 다양한 환경에서 패치를 적용해야 합니다.운영 환경에 대한 DB 액세스 권한이 없으므로 스크립트를 삽입하는 것이 가장 좋습니다.
2. 새 열이 추가되어 다음에 필요한 경우에 대비하여 많은 항목을 포함하지 않고 쉽게 변경할 수 있습니다.

반복적인 요구사항의 경우 Excel에서 외부 키를 매핑할 수 있습니다.영역 테이블 및 영역 테이블을 Excel 파일로 내보냅니다.like (ex: filename: lookupmaster.xlsx)

region name   region Id
 Polon          1
 yerni          2

실제 데이터가 있는 스프레드시트에서 다음과 같이 두 개의 열을 추가합니다.

region_id, zone_id

vlookup을 사용하여 모든 행에 대한 영역 ID 가져오기

VLOOKUP(B2,[lookumaster.xlsx]Sheet1!$A$1:$B$4,2,FALSE)

마스터, 키 열에 대한 종속성을 제거하려면 영역 ID 열을 복사하고 해당 위치에만 값으로 특수하게 붙여넣습니다.

영역 ID에 대해 동일한 작업 반복

스프레드시트에서 영역 및 영역 열을 제거하고 데이터베이스로 가져올 수 있습니다.

에서 스프레드시트를 .Export/Import feature

스프레드시트에 표시된 대로 테이블을 내보냅니다(이름 철자 포함).-이라고 하는 테이블로 tempUsers.

다음 쿼리를 수행하여 원하는 테이블(이름 대신 숫자 사용)을 만들 때 정규화를 수행합니다.

이와 같은 것:

INSERT INTO users
        (id, name, region_id, zone_id)
    SELECT tempUsers.id, tempUsers.name, region.id, zone.id
        FROM X
        LEFT JOIN region AS region ON region.region_name = tempUsers.region
        LEFT JOIN zone   AS zone ON zone.zone_name   = tempUsers.zone;

가 약만 ID인 AUTO_INCREMENT그러면 게 것 (Leave 그약다하떠것좋것이다을입니다는니게르납간러면떠▁()▁thenleave납▁(니다▁you▁different다니.)id에서 INSERT는 SELECT이기 AUTO_INCREMENT새 레코드가 입력될 때마다 행 수가 1씩 증가합니다.

해피 프로그래밍

언급URL : https://stackoverflow.com/questions/44106848/how-to-import-xls-file-to-sql-with-a-foreign-key