[2021.07.19] How to solve ERROR 1265 : Data truncated for column ‘컬럼명’ at row 1 in MySQL?

[2021.07.19] How to solve ERROR 1265 : Data truncated for column ‘컬럼명’ at row 1 in MySQL?

 

 

해당 게시글은 MySQL에서 csv파일을 업로드 할 때

해당 에러가 발생 했을 경우 해결하는 방법에 대해 알려주도록 하겠다.

 

 

 

 

ERROR 1265 : Data truncated for column ‘컬럼명’ at row 1

-> 위의 에러는 type을 type -> MEDIUMTEXT 으로 변경해줘야 한다.

 

 

 

 

* 해결 방법

ALTER TABLE 테이블명 MODIFY 해당테이블 컬럼명 MEDIUMTEXT;

 

 

To change all types in Mysql (using Python)

arr = ["index",
"waist",
"total",
"all_men", "all_women",
"1900_1939yearago_men","1900_1939yearago_women",
"1940_41yearago_men","1940_41yearago_women",
"1942_43yearago_men","1942_43yearago_women",
"1944_45yearago_men","1944_45yearago_women",
"1946_47yearago_men","1946_47yearago_women",
"1948_49yearago_men","1948_49yearago_women",
"1950_51yearago_men","1950_51yearago_women",
"1952_53yearago_men","1952_53yearago_women",
"1954_55yearago_men","1954_55yearago_women",
"1956_57yearago_men","1956_57yearago_women",
"1958_59yearago_men","1958_59yearago_women",
"1960_61yearago_men","1960_61yearago_women",
"1962_63yearago_men","1962_63yearago_women",
"1964_65yearago_men","1964_65yearago_women",
"1966_67yearago_men","1966_67yearago_women",
"1968_69yearago_men","1968_69yearago_women",
"1970_71yearago_men","1970_71yearago_women",
"1972_73yearago_men","1972_73yearago_women",
"1974_75yearago_men","1974_75yearago_women",
"1976_77yearago_men","1976_77yearago_women",
"1978_79yearago_men","1978_79yearago_women",
"1980_81yearago_men","1980_81yearago_women",
"1982_83yearago_men","1982_83yearago_women",
"1984_85yearago_men","1984_85yearago_women",
"1986_87yearago_men","1986_87yearago_women",
"1988_89yearago_men","1988_89yearago_women",
"1990_94yearago_men","1990_94yearago_women"]

for i in arr:
    print(f"ALTER TABLE waist_nationwide MODIFY {i} MEDIUMTEXT;")

 

출력 결과

ALTER TABLE waist_nationwide MODIFY index MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY waist MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY total MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY all_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY all_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1900_1939yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1900_1939yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1940_41yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1940_41yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1942_43yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1942_43yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1944_45yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1944_45yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1946_47yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1946_47yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1948_49yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1948_49yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1950_51yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1950_51yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1952_53yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1952_53yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1954_55yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1954_55yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1956_57yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1956_57yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1958_59yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1958_59yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1960_61yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1960_61yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1962_63yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1962_63yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1964_65yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1964_65yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1966_67yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1966_67yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1968_69yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1968_69yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1970_71yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1970_71yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1972_73yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1972_73yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1974_75yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1974_75yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1976_77yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1976_77yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1978_79yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1978_79yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1980_81yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1980_81yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1982_83yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1982_83yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1984_85yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1984_85yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1986_87yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1986_87yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1988_89yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1988_89yearago_women MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1990_94yearago_men MEDIUMTEXT;
ALTER TABLE waist_nationwide MODIFY 1990_94yearago_women MEDIUMTEXT;

-> 해당 출력결과를 MySQL에 붙어서 쓰면 된다.

 

MySQL에서 모든 type 변경하는 법.ipynb

0.01MB

 

-> 1번 : 해당 출력결과를 복사붙여넣은 후, 컨트롤 + A

-> 2번 : 해당 번개 모양 클릭(명령어 수행)

-> 3번을 보면, 결과가 제대로 수행된지 알 수 있다.

 

 

 

type -> MEDIUMTEXT 전체 변경 완료