Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

第 6 章 DML

目錄

6.1. INSERT
6.1.1. 自動截取字元串
6.1.2. INSERT IGNORE INTO
6.2. copy
6.2.1. wget

6.1. INSERT

6.1.1. 自動截取字元串

CREATE TABLE test (c varchar(5));
			

現在開始插入資料庫,每次增加一個長度

			
test=> INSERT INTO test VALUES ('1');
INSERT 0 1
test=> INSERT INTO test VALUES ('12');
INSERT 0 1
test=> INSERT INTO test VALUES ('123');
INSERT 0 1
test=> INSERT INTO test VALUES ('1234');
INSERT 0 1
test=> INSERT INTO test VALUES ('12345');
INSERT 0 1
test=> INSERT INTO test VALUES ('123456');
ERROR:  value too long for type character varying(5)
test=> INSERT INTO test VALUES ('1234567');
ERROR:  value too long for type character varying(5)
test=>
			
			

超出長度會提示 ERROR: value too long for type character varying(5)

通過 ::varchar(5) 截取5前五個字元,後面拋棄

			
test=> INSERT INTO test VALUES ('123456'::varchar(5));
INSERT 0 1
test=> INSERT INTO test VALUES ('1234567'::varchar(5));
INSERT 0 1
test=> INSERT INTO test VALUES ('12345678'::varchar(5));
INSERT 0 1
			
			

超過的部分被自動截取

			
test=> select * from test;
   c
-------
 1
 12
 123
 1234
 12345
 12345
 12345
 12345
(8 rows)
			
			

6.1.2. INSERT IGNORE INTO

PostgreSQL 沒有 MySQL INSERT IGNORE INTO 用法,可以使用下面方法替代

insert into profile(wechat,username,name) select wechat,username,name from member where description like '%5%' and NOT EXISTS (select 1 from profile where profile.wechat = member.wechat);