mysql

[dayoung9650]

mysql bulk query

[dayoung9650]

LOAD DATA LOCAL INFILE '/Users/dayoung/downloads/food_detail_for_server.txt' INTO TABLE posts FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (posts_title, posts_img, posts_time, posts_places, posts_info, posts_expire, posts_lat, posts_lon, posts_isSell, user_id, available);

select * from posts;
select * from users;

drop table posts;
drop table users;

CREATE TABLE users (
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(100) DEFAULT NULL,
user_profile varchar(200) DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE posts (
posts_id int(11) NOT NULL AUTO_INCREMENT,
posts_title varchar(200) DEFAULT NULL,
posts_img varchar(100) DEFAULT NULL,
posts_time datetime DEFAULT NULL,
posts_places varchar(200) DEFAULT NULL,
posts_info varchar(500) DEFAULT NULL,
posts_expire varchar(45) DEFAULT NULL,
posts_lat decimal(13,10) DEFAULT NULL,
posts_isSell int(11) DEFAULT NULL,
user_id int(11) DEFAULT NULL,
posts_lon decimal(13,10) DEFAULT NULL,
available varchar(200) DEFAULT NULL,
PRIMARY KEY (posts_id),
KEY user_Id_idx (user_id),
CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

LOAD DATA LOCAL INFILE '/Users/dayoung/downloads/users_profile_for_server.txt' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (user_name, user_profile);

ALTER TABLE posts AUTO_INCREMENT = 1;
ALTER TABLE users AUTO_INCREMENT = 1
;

[dayoung9650]

LOAD DATA LOCAL INFILE '/Users/dayoung/downloads/food_detail_for_server.txt' INTO TABLE posts CHARACTER SET utf8 character set UTF8 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (posts_title, posts_img, posts_time, posts_places, posts_info, posts_expire, posts_lat, posts_lon, posts_isSell, user_id, available);

select * from posts;
select * from users;

drop table posts;
drop table users;

CREATE TABLE users (
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(100) DEFAULT NULL,
user_profile varchar(200) DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE posts (
posts_id int(11) NOT NULL AUTO_INCREMENT,
posts_title varchar(200) DEFAULT NULL,
posts_img varchar(100) DEFAULT NULL,
posts_time datetime DEFAULT NULL,
posts_places varchar(200) DEFAULT NULL,
posts_info varchar(500) DEFAULT NULL,
posts_expire varchar(45) DEFAULT NULL,
posts_lat decimal(13,10) DEFAULT NULL,
posts_isSell int(11) DEFAULT NULL,
user_id int(11) DEFAULT NULL,
posts_lon decimal(13,10) DEFAULT NULL,
available varchar(200) DEFAULT NULL,
PRIMARY KEY (posts_id),
KEY user_Id_idx (user_id),
CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

LOAD DATA LOCAL INFILE '/Users/dayoung/downloads/users_profile_for_server.txt' INTO TABLE users CHARACTER SET utf8 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (user_name, user_profile);

ALTER TABLE posts AUTO_INCREMENT = 1;
ALTER TABLE users AUTO_INCREMENT = 1
;

show variables like 'character%';

set character_set_database=euckr;
SET character_set_server = euckr;
show variables like 'character%';
SET character_set_client = euckr;
SET character_set_results = euckr;
SET character_set_connection = euckr;
SET character_set_filesystem= binary;
SET character_set_system=euckr;

insert into users(user_name, user_profile) values('은주섭', 'https://s3.ap-northeast-2.amazonaws.com/cjimage2/posts/users/me.png'),
('세영', 'https://s3.ap-northeast-2.amazonaws.com/cjimage2/posts/users/user1.png'),
('관섭', 'https://s3.ap-northeast-2.amazonaws.com/cjimage2/posts/users/user2.png'),
('주영', 'https://s3.ap-northeast-2.amazonaws.com/cjimage2/posts/users/user3.png'),
('세은', 'https://s3.ap-northeast-2.amazonaws.com/cjimage2/posts/users/user7.png'),
('행복하자', 'https://s3.ap-northeast-2.amazonaws.com/cjimage2/posts/users/user4.png'),
('입벌려먹을거들어간다', 'https://s3.ap-northeast-2.amazonaws.com/cjimage2/posts/users/user5.png') ,
('내가먹을게넌줘', 'https://s3.ap-northeast-2.amazonaws.com/cjimage2/posts/users/user6.png');