分类 数据库 下的文章

mysql load data infile 导入 csv 文件

load data infile '/home/user/OK3a/WW.txt' into table dmpa_test fields terminated by ',';

ERROR 1045 (28000): Access denied for user 'pguser'@'%' (using password: YES)
ERROR 1146 (42S02): Table 'QGDB.dmpa_test' doesn't exist
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
show variables like '%secure%';
sudo mv /home/user/OK3a/WW.txt /var/lib/mysql-files/
load data infile '/var/lib/mysql-files/WW.txt' into table dmpa_test fields terminated by ',';
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns
load data infile '/var/lib/mysql-files/00.txt' into table dmpa_test fields terminated by ',';

show variables like 'sql_mode';
load data infile '/var/lib/mysql-files/00.txt' into table dmpa_test fields terminated by ',' enclosed by '"';
[Err] 1262 - Row 1 was truncated; it contained more data than there were input columns
load data infile '/var/lib/mysql-files/00.txt' into table dmpa_test fields terminated by ',' optionally enclosed by "'" lines terminated by '\r\n' IGNORE 1 LINES ;
[Err] 1262 - Row 371126 was truncated; it contained more data than there were input columns

参考:
https://www.cnblogs.com/waynechou/p/7794939.html

Linux下软件环境安装(5)-mysql安装与配置(续)

https://dev.mysql.com/downloads/

MySQL Community Server 社区版本,开源免费
MySQL Cluster 集群版,开源免费
MySQL Router
MySQL Shell
MySQL Workbench 专为MySQL设计的ER/数据库建模工具

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-server_8.0.20-2ubuntu20.04_amd64.deb-bundle.tar

mkdir mysql
cd mysql
tar xf ../mysql-server_8.0.22-1ubuntu20.04_amd64.deb-bundle.tar
sudo dpkg -i *

解决问题
Error mysql-community-server-core depends on libmecab2 (>= 0.996);
https://www.ubuntuupdates.org/package/core/xenial/universe/base/libmecab2
wget http://security.ubuntu.com/ubuntu/pool/universe/m/mecab/libmecab2_0.996-1.2ubuntu1_amd64.deb
sudo dpkg -i libmecab2_0.996-1.2ubuntu1_amd64.deb

安装时选择5.x可以直接进
mysql -u root -p

CREATE USER 'user' IDENTIFIED BY '******';
create database userdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
grant all privileges on userdb.* to user@"%";

修改端口
vi /etc/mysql/mysql.conf.d/mysqld.cnf
末尾添加port=43306
sudo service mysql restart
sudo service mysql status

use mysql;
select host,user from user;
update user set host='%' where user='root';
flush privileges;

Navicat for MySQL 连接 Mysql 8.0.11 出现1251- Client does not support authentication protocol 错误
由于mysql 8.x登录验证方式改变,mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password,

查看验证方式

select user,host,plugin,authentication_string from user;
alter user 'root'@'%' identified with mysql_native_password by 'bob.1234';

MySql数据库也会睡觉

过一段时间不用,mysql就休眠了,是多久呢?

show global variables like 'wait_timeout';
show session variables like 'wait_timeout';

默认是28800,60608也就是8小时,如果想久一些,比如3天

set global wait_timeout = 259200;
set session wait_timeout = 259200;

看看效果吧

参考:http://wbw3wbw.iteye.com/blog/2244963

Linux下软件环境安装(5)-mysql安装与配置

说起linux下的数据库,最常用的就是mysql了吧,也要装一个,安装过程中需要设置一个root密码

sudo apt-get update
sudo apt-get install mysql-server

下面开始配置,比较老的版本配置文件是/etc/mysql/my.cnf,比较新的版本配置文件是/etc/mysql/mysql.conf.d/mysqld.cnf
端口默认是3306可以在配置文件里修改,有两个地方,应该一个是服务,一个是客户端吧
另外为了其它机器可以访问,需要注释掉bind-address = 127.0.0.1
然后重起数据库服务

sudo service mysql restart

然后通过客户端登录,提示符是mysql>

mysql -u root -p

看一下时间,竟然是对的,意外,如果不对参考这篇吧《mysql的时区设置

select now();

- 阅读剩余部分 -

mysql纵表变横表

数据以流水存放

CREATE TABLE `tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `n` varchar(3) DEFAULT NULL,
  `t` varchar(1) DEFAULT NULL,
  `v` float DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `tab` VALUES ('1', '101', 'A', '1');
INSERT INTO `tab` VALUES ('2', '102', 'A', '2');
INSERT INTO `tab` VALUES ('3', '102', 'B', '3');
INSERT INTO `tab` VALUES ('4', '103', 'B', '4');

上SQL

select n,
sum(case t when 'A' then v else null end) as A,
sum(case t when 'B' then v else null end) as B
from tab group by n order by n

结果

n   A   B
101 1   
102 2   3
103     4