<div id="readability-page-1" class="page"><div><pre id="code_1676512988903" data-ke-language="sql" data-ke-type="codeblock"><code>create table issue  
(  
    id          bigint auto_increment,  
    amount      decimal(19, 2) not null,  
    created_at  timestamp      not null,  
    modified_at timestamp      not null,  
    constraint issue_pk  
        primary key (id)  
);</code></pre>
<p data-ke-size="size16">위와 같이 테이블을 구성하고 테스트를 해본다.</p>
<p data-ke-size="size16">created_at 컬럼에 빈값으로 넣어도 현재 시간을 저장하고 싶어졌을때, default 값을 넣어주어야 한다.&nbsp;</p>
<figure data-ke-mobilestyle="widthOrigin" data-filename="alter column timestamp type error.png" data-origin-width="1890" data-origin-height="1696"><span data-url="https://blog.kakaocdn.net/dn/bVLfsS/btrZsGVROda/aW3KFrAiWn0CHXSeTUyHZ0/img.png" data-lightbox="lightbox"><img src="https://blog.kakaocdn.net/dn/bVLfsS/btrZsGVROda/aW3KFrAiWn0CHXSeTUyHZ0/img.png" onerror="this.onerror=null; this.src='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png'; this.srcset='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png';" data-filename="alter column timestamp type error.png" data-origin-width="1890" data-origin-height="1696"></span></figure>

<p data-ke-size="size16">그런데, timestamp 타입의 컬럼에 default 값을 넣을때 문제가 생긴다!&nbsp;</p>
<pre id="code_1676513330999" data-ke-language="sql" data-ke-type="codeblock"><code>[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP' at line 2.</code></pre>
<p data-ke-size="size16">사실 아래처럼 sql이 작성 되어야 맞는데, timestamp 타입만 좀 이상하다.&nbsp;</p>
<pre id="code_1676513149333" data-ke-language="sql" data-ke-type="codeblock"><code>alter table issue
    MODIFY `created_at` timestamp DEFAULT CURRENT_TIMESTAMP;</code></pre>
<figure data-ke-mobilestyle="widthOrigin" data-filename="result.png" data-origin-width="1962" data-origin-height="326"><span data-url="https://blog.kakaocdn.net/dn/bbq4Dn/btrZsGO8L67/5P3FQHhSTu2TRautBqHWB1/img.png" data-lightbox="lightbox"><img src="https://blog.kakaocdn.net/dn/bbq4Dn/btrZsGO8L67/5P3FQHhSTu2TRautBqHWB1/img.png" onerror="this.onerror=null; this.src='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png'; this.srcset='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png';" data-filename="result.png" data-origin-width="1962" data-origin-height="326"></span></figure>

<p data-ke-size="size16">직접 입력 하게 되면, 정상적으로 변경 된다.</p>
<p data-ke-size="size16">ref. <a href="https://youtrack.jetbrains.com/issue/DBE-17447/Alter-syntax-error-of-MySQL-table-in-DataGrip-2022.3.1" target="_blank" rel="noopener">https://youtrack.jetbrains.com/issue/DBE-17447/Alter-syntax-error-of-MySQL-table-in-DataGrip-2022.3.1</a></p>
<p data-ke-size="size16">datagrip쪽 issue에 등록 했는데 설명을 잘 했는지 모르겠다.</p></div></div>

Seungdols Company

seungdols

create table issue ( id bigint auto_increment, amount decimal(19, 2) not null, created_at timestamp not null, modified_at timestamp not null, constraint issue_pk primary key (id) ); 위와 같이 테이블을 구성하고 테스트를 해본다. created_at 컬럼에 빈값으로 넣어도 현재 시간을 저장하고 싶어졌을때, de..

CURRENT_TIMESTAMP를 default로 셋팅시 datagrip 오류

댓글