MySQL创建数据表
##删除表
drop table h_data;
##创建表
CREATE TABLE IF NOT EXISTS h_data(
hotel VARCHAR(100) NOT NULL,
is_canceled INT(30) NOT NULL,
lead_time INT(100) NOT NULL,
arrival_date_year YEAR NOT NULL,
arrival_date_month VARCHAR(50) NOT NULL,
arrival_date_week_number INT(30) NOT NULL,
arrival_date_day_of_month INT(30) NOT NULL,
stays_in_weekend_nights INT(30) NOT NULL,
stays_in_week_nights INT(30) NOT NULL,
adults INT(30) NOT NULL,
children INT(30) NOT NULL,
babies INT(30) NOT NULL,
meal VARCHAR(30) NOT NULL,
country VARCHAR(30) NOT NULL,
market_segment VARCHAR(100) NOT NULL,
distribution_channel VARCHAR(100) NOT NULL,
is_repeated_guest INT(30) NOT NULL,
previous_cancellations INT(30) NOT NULL,
previous_bookings_not_canceled INT(30) NOT NULL,
reserved_room_type VARCHAR(30) NOT NULL,
assigned_room_type VARCHAR(30) NOT NULL,
booking_changes INT(30) NOT NULL,
deposit_type VARCHAR(100) NOT NULL,
agent INT(100) NULL,
company INT(100) NULL,
days_in_waiting_list INT(30) NOT NULL,
customer_type VARCHAR(100) NOT NULL,
adr DOUBLE NOT NULL,
required_car_parking_spaces INT(30) NOT NULL,
total_of_special_requests INT(30) NOT NULL,
reservation_status VARCHAR(100) NOT NULL,
reservation_status_date DATE,
PRIMARY KEY (hotel)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
##导入表
load data infile '/var/lib/mysql-files/hotel_bookings.csv'
into table h_data
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';
##重启mysql服务
sudo service mysql restart
错误排查
出现报错：
ERROR 1366 (HY000): Incorrect integer value: ‘is_canceled’ for column ‘is_canceled’ at row 1
百度的解决思路：
官方解释：在5.x版本的mysql对空值插入有”bug”,要在安装mysql的时候去除默认勾选的enable strict SQL mode,如果我们安装好了mysql，解决办法是更改mysql中的配置 my.ini
my.ini中查找sql-mode，
默认为:sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”，将其修改为
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
重启mysql后即可
实际操作：
由于运行的环境是在ubuntu18.06系统下，在terminal终端进行的操作，需要找到配置my.cnf文件;
my.cnf文件内只有
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
找到mysql.conf.d内的mysqld.cnf，在文段末尾加上
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
并将整段mysqld.cnf复制添加到一开始的my.cnf文件内，问题解决！
