다음을 참조하여 진행한다.

https://m.blog.naver.com/pkm500/222380977788

create Table

CREATE TABLE road_names (
    law_code VARCHAR(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '1.법정동코드',
    city_name VARCHAR(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '2.시도명',
    district_name VARCHAR(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '3.시군구명',
    law_city_name VARCHAR(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '4.법정읍면동명',
    law_district_name VARCHAR(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '5.법정리명',
    industry VARCHAR(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '6.산여부',
    branch_id INT DEFAULT NULL COMMENT '7.지번본번(번지)',
    branch_no INT DEFAULT NULL COMMENT '8.지번부번(호)',
    road_code VARCHAR(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '9.도로명코드',
    road_name VARCHAR(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '10.도로명',
    ground VARCHAR(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '11.지하여부',
    building_no INT DEFAULT NULL COMMENT '12.건물본번',
    building_sub_no INT DEFAULT NULL COMMENT '13.건물부번',
    building_name VARCHAR(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '14.건축물대장 건물명',
    detail_building_name VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '15.상세건물명',
    building_management_no VARCHAR(25) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '16.건물관리번호',
    law_dong_code VARCHAR(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '17.읍면동일련번호',
    administrative_dong_code VARCHAR(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '18.행정동코드',
    administrative_dong_name VARCHAR(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '19.행정동명',
    postal_code VARCHAR(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '20.우편번호',
    postal_code_no VARCHAR(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '21.우편일련번호',
    multi_building_name VARCHAR(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '22.다량배달처명',
    move_code VARCHAR(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '23.이동사유코드',
    indicator VARCHAR(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '24.고시일자',
    previous_road_address VARCHAR(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '25.변동전도로명주소',
    district_building_name VARCHAR(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '26.시군구용 건물명',
    common_code VARCHAR(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '27.공동주택여부',
    additional_code VARCHAR(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '28.기초구역번호',
    detail_address_no VARCHAR(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '29.상세주소여부',
    note1 VARCHAR(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '30.비고1',
    note2 VARCHAR(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '31.비고2',
    PRIMARY KEY (building_management_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='도로명 주소 테이블';

다음 이미지를 처럼 importsetting 을 참조해서 설정한다.

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3ch42VzkX1gHDu-StIzc2uJOonrzY4TUAjUrPmu3AD62xZ1y1GUvXg-2NZhzbhNy1XiAbls9AOV5oDgRUSO0LGrTjHKjADY76aVqx5_nbkLybLbOlJkFP5qLMvQ8qb8MGn6yBYKEz4kL92mpuzBC_ahd9PMnTYb94ep5axJFrdpo3kazoEFLfS2ZfZAFM/s1622/2024-06-14%2011%2031%2022.png

테이블 매핑이 맞는지 필수적으로 확인한다.

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1VB0IDPh9fSHfZe-Ezp_NpqP3XvLReE08oP8h47GwbmQTZhl6JN0ZRj0CMqO2RNQ_cAA7jMmkF80BmqZ2Unt94tBEYexz0OogcGXQzGTbfU_hkV3yve6pjU4YgN0xDoGv8_Yc6A_6QGSJKr8l0SPk2rgZmqCg2cGccF3zMyuOdu0ZEar1TzegYom7D1Jv/s1810/2024-06-14%2011%2034%2027.png

1.랜덤 이름  생성후 업데이트

CREATE TEMPORARY TABLE 로 임시 테이블을 만들경우 데이터양이 적으면 상관없으나

데이터 양이 많으면 중간에 오류난다.

-- 성 목록 테이블 생성
CREATE  TABLE temp_surnames (
  id INT AUTO_INCREMENT PRIMARY KEY,
  surname VARCHAR(10)
);

INSERT INTO temp_surnames (surname) VALUES
('김'), ('이'), ('박'), ('최'), ('정'),
('강'), ('조'), ('윤'), ('장'), ('임'),
('오'), ('한'), ('신'), ('서'), ('권'),
('황'), ('안'), ('송'), ('류'), ('전');

-- 이름 목록 테이블 생성
CREATE  TABLE temp_names (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(10)
);

INSERT INTO temp_names (name) VALUES
('민준'), ('서준'), ('도윤'), ('예준'), ('시우'),
('하준'), ('주원'), ('지호'), ('지훈'), ('준우'),
('지우'), ('준서'), ('서진'), ('지원'), ('예은'),
('지민'), ('수현'), ('은서'), ('수빈'), ('서윤'),
('윤서'), ('하은'), ('민서'), ('지현'), ('예린'),
('소연'), ('은주'), ('현서'), ('채원'), ('서연'),
('현우'), ('도현'), ('은우'), ('승현'), ('유진'),
('태윤'), ('재윤'), ('하린'), ('연우'), ('세현'),
('우진'), ('현진'), ('정우'), ('은호'), ('성현'),
('지환'), ('동현'), ('수현'), ('태현'), ('민호'),
('찬우'), ('수혁'), ('정민'), ('지율'), ('하영'),
('지안'), ('서율'), ('정원'), ('지혁'), ('유빈'),
('지성'), ('서현'), ('시현'), ('태민'), ('하온'),
('승우'), ('유하'), ('하늘'), ('우빈'), ('동민'),
('유민'), ('다인'), ('정훈'), ('지훈'), ('민성'),
('태양'), ('한결'), ('예진'), ('채윤'), ('진우'),
('현석'), ('현우'), ('태준'), ('수영'), ('승민'),
('다온'), ('수호'), ('하람'), ('서연'), ('도훈'),
('세빈'), ('현아'), ('서호'), ('채민'), ('승원');

프로시저 생성

DELIMITER //

CREATE PROCEDURE update_random_names()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE random_surname VARCHAR(10);
    DECLARE random_name VARCHAR(10);

    WHILE i <= 10539522 DO
        -- 랜덤 성 추출
        SELECT surname INTO random_surname
        FROM temp_surnames
        ORDER BY RAND()
        LIMIT 1;

        -- 랜덤 이름 추출
        SELECT name INTO random_name
        FROM temp_names
        ORDER BY RAND()
        LIMIT 1;

        -- 업데이트 실행
        UPDATE users
        SET name = CONCAT(random_surname, random_name)
        WHERE id = i;

        -- 다음 사용자로 이동
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

-- 프로시저 실행
CALL update_random_names();

임시 테이블 삭제