Skip to content

Instantly share code, notes, and snippets.

@gladson

gladson/load_data_local_infile_users.sql

Last active December 19, 2023 04:21
Show Gist options
  • Save gladson/6b1007e2ddd5eec90eaf1810132be7bf to your computer and use it in GitHub Desktop.
Save gladson/6b1007e2ddd5eec90eaf1810132be7bf to your computer and use it in GitHub Desktop.
Download ZIP
Dbeaver - LOAD DATA LOCAL INFILE
Raw
load_data_local_infile_users.sql
LOAD DATA LOCAL INFILE 'C:/Users/Gladson/Downloads/users.csv'
INTO TABLE test_db.users_tb
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@name,@email,@password,@age,@gender,@created_at,@updated_at,@deleted_at)
SET name = @name, email = @email, password = @password, age = @age,
gender = @gender, created_at = @created_at, updated_at = @updated_at,
deleted_at = @deleted_at;
SELECT COUNT(*) FROM test_db.users_tb ut;
TRUNCATE TABLE test_db.users_tb;
Raw
users.sql
CREATE TABLE `users_tb` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(255) NOT NULL,
`age` int(11) DEFAULT NULL,
`gender` enum('male','female','other') NOT NULL,
`role_id` bigint(20) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`updated_at` timestamp NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
`deleted_at` timestamp NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `users_tb_role_id_roles_tb_id_fk` (`role_id`),
KEY `users_tb_email_idx` (`email`) USING BTREE,
KEY `users_tb_name_idx` (`name`) USING BTREE,
CONSTRAINT `users_tb_role_id_roles_tb_id_fk` FOREIGN KEY (`role_id`) REFERENCES `roles_tb` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
@gladson
Copy link
Author

gladson commented Dec 19, 2023

Solution Error

Erro SQL [HY000]: (conn=256) LOAD DATA LOCAL INFILE asked for file 'C:/Users/Gladson/Downloads/users.csv' that doesn't correspond to initial query SET STATEMENT SQL_SELECT_LIMIT=200 FOR LOAD DATA LOCAL INFILE 'C:/Users/Gladson/Downloads/users.csv'
INTO TABLE test_db.users_tb

Config Dbeaver

image

Driver:

image
image

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment