mysql

mysql

服务器突然挂掉了,mysql无法启动

后端开发网站管理员 Published the article • 0 comments • 99 views • 2019-08-24 00:01 • 来自相关话题

刚才打算在服务器上跑个项目,因为要记录很多的日志,所有我打算删掉点东西,然后节省空间,方便脚本一直跑下去,不了脚本处理数据多,内存占满了,服务器卡的不行,我只好`killall python`来结束这个脚本。

随后悲剧出现,msyql挂掉了,怎么也启动不了,我使用`mysqld --initialize`来初始化mysql,结果出现如下错误:
```
190823 23:53:07 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
190823 23:53:07 [Note] mysqld (mysqld 5.5.62-0+deb8u1-log) starting as process 3731 ...
mysqld: File '/var/log/mysql/mysql-bin.index' not found (Errcode: 2)
190823 23:53:07 [ERROR] Aborting

190823 23:53:07 [Note] mysqld: Shutdown complete
```

大概看了一下就因为`/var/log/mysql/mysql-bin.index`这个文件找不到,原来是我干才误删的文件,那该怎么办呢?创建一下目录`mkdip -p /var/log/mysql`,然后继续执行上面的命令,果然成功,我的mysql服务终于起来了。

所以还是奉劝大家,删除数据的时候一定要谨慎点,不然半天还摸不着头脑。 查看全部

刚才打算在服务器上跑个项目,因为要记录很多的日志,所有我打算删掉点东西,然后节省空间,方便脚本一直跑下去,不了脚本处理数据多,内存占满了,服务器卡的不行,我只好`killall python`来结束这个脚本。

随后悲剧出现,msyql挂掉了,怎么也启动不了,我使用`mysqld --initialize`来初始化mysql,结果出现如下错误:
```
190823 23:53:07 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
190823 23:53:07 [Note] mysqld (mysqld 5.5.62-0+deb8u1-log) starting as process 3731 ...
mysqld: File '/var/log/mysql/mysql-bin.index' not found (Errcode: 2)
190823 23:53:07 [ERROR] Aborting

190823 23:53:07 [Note] mysqld: Shutdown complete
```

大概看了一下就因为`/var/log/mysql/mysql-bin.index`这个文件找不到,原来是我干才误删的文件,那该怎么办呢?创建一下目录`mkdip -p /var/log/mysql`,然后继续执行上面的命令,果然成功,我的mysql服务终于起来了。

所以还是奉劝大家,删除数据的时候一定要谨慎点,不然半天还摸不着头脑。


mysql 数据导入导出

后端开发网站管理员 Published the article • 0 comments • 319 views • 2018-08-06 16:32 • 来自相关话题

## mysql 如何导入txt

### mysql 远程执行
```
mysql -hwww.xxx.com -uroot -p -e 'select * from detail_kd' > /tmp/detail_kd.txt
```

### mysql 输出的数据为
```
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
| detail_integral_id | user_id | integral | total_integral | target_id | target_type | create_time |
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
| 74618432 | 12963299 | 30 | 11991729 | 0 | room | 1533163756829 |
| 74619240 | 12963299 | 210 | 11991939 | 0 | room | 1533165021613 |
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
```

### 导入sql语句
```sql
load data infile '/tmp/detail_kd.txt' into table detail_kd fields terminated by '\t';
```

infile 后面是导入```txt```的路径,
into table detail_kd kd详情表
terminated byb '\t' 分隔符号

### ```mysqldump```导入导出

--where 可以指定过滤条件

```
mysqldump -uroot -p test detail_integral --where="integral > 330 and user_id > 1" > /tmp/detail_kd.sql
```

```
source /tmp/detail_kd.sql
``` 查看全部

## mysql 如何导入txt

### mysql 远程执行
```
mysql -hwww.xxx.com -uroot -p -e 'select * from detail_kd' > /tmp/detail_kd.txt
```

### mysql 输出的数据为
```
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
| detail_integral_id | user_id | integral | total_integral | target_id | target_type | create_time |
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
| 74618432 | 12963299 | 30 | 11991729 | 0 | room | 1533163756829 |
| 74619240 | 12963299 | 210 | 11991939 | 0 | room | 1533165021613 |
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
```

### 导入sql语句
```sql
load data infile '/tmp/detail_kd.txt' into table detail_kd fields terminated by '\t';
```

infile 后面是导入```txt```的路径,
into table detail_kd kd详情表
terminated byb '\t' 分隔符号

### ```mysqldump```导入导出

--where 可以指定过滤条件

```
mysqldump -uroot -p test detail_integral --where="integral > 330 and user_id > 1" > /tmp/detail_kd.sql
```

```
source /tmp/detail_kd.sql
```

mysql5.7Invalid datetime format: 1292 Incorrect date value: '0000-00-00' for column 'day' at row 1

PHP Published the article • 0 comments • 2249 views • 2018-03-26 17:46 • 来自相关话题

reports表中day字段数据类型为date 默认值为 `0000-00-00`,在mysql5.7中这个是会报错,不允许的,且导致无法修改其他字段的数据类型。

现修改day默认值为`1970-01-01`,且批量更新day为null值的数据,修改为`1970-01-01`

### 同理

DATETIME等字段也需要修改为`1970-01-01 00:00:00`

```mysql
alter table reports modify column birthday DATETIME not null default '1970-01-01 00:00:00';
alter table reports modify column day DATE not null default '1970-01-01';
``` 查看全部
reports表中day字段数据类型为date 默认值为 `0000-00-00`,在mysql5.7中这个是会报错,不允许的,且导致无法修改其他字段的数据类型。

现修改day默认值为`1970-01-01`,且批量更新day为null值的数据,修改为`1970-01-01`

### 同理

DATETIME等字段也需要修改为`1970-01-01 00:00:00`

```mysql
alter table reports modify column birthday DATETIME not null default '1970-01-01 00:00:00';
alter table reports modify column day DATE not null default '1970-01-01';
```

如何快速找出文件中创建数据库表的数据库名

Linux网站管理员 Published the article • 0 comments • 390 views • 2018-03-06 19:01 • 来自相关话题

如何快速找出文件中创建数据库表的数据库名,

### linux命令

```bash
grep -r "CREATE TABLE \`[^\`]*\`" . | awk -F\` '{print $2}' | sort | uniq
```
如何快速找出文件中创建数据库表的数据库名,

### linux命令

```bash
grep -r "CREATE TABLE \`[^\`]*\`" . | awk -F\` '{print $2}' | sort | uniq
```

mysql通过插入数据来查看tinyint的大小

PHP网站管理员 Published the article • 0 comments • 372 views • 2018-02-23 11:09 • 来自相关话题

### 查看表结构
```
MariaDB [film]> show create table test;
+-------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

### 插入int类型的id,大于`tinyint`的数值

```bash
MariaDB [film]> insert into test set id = 9999;
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [film]> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'id' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
```

### 查看写入的数据
数据写进去了,但是被截断了,如果在MySQL数据库严格模式下,这条数据将无法写入
```
MariaDB [film]> select *from test;
+-----+
| id |
+-----+
| 2 |
| 127 |
+-----+
2 rows in set (0.00 sec)
```
### 插入一个负数

我们得到了同样的结果,

```
MariaDB [film]> insert into test set id = -9999;
Query OK, 1 row affected, 1 warning (0.02 sec)
```
```
MariaDB [film]> select *from test;
+------+
| id |
+------+
| 2 |
| 127 |
| -128 |
+------+
3 rows in set (0.00 sec)
```

### 总结

MySQL数据类型`tinyint`的大小为`-128 ~ 127`,那么如果是无符号的`tinyint`呢?数值范围是:`0 ~ 255`。 查看全部
### 查看表结构
```
MariaDB [film]> show create table test;
+-------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

### 插入int类型的id,大于`tinyint`的数值

```bash
MariaDB [film]> insert into test set id = 9999;
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [film]> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'id' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
```

### 查看写入的数据
数据写进去了,但是被截断了,如果在MySQL数据库严格模式下,这条数据将无法写入
```
MariaDB [film]> select *from test;
+-----+
| id |
+-----+
| 2 |
| 127 |
+-----+
2 rows in set (0.00 sec)
```
### 插入一个负数

我们得到了同样的结果,

```
MariaDB [film]> insert into test set id = -9999;
Query OK, 1 row affected, 1 warning (0.02 sec)
```
```
MariaDB [film]> select *from test;
+------+
| id |
+------+
| 2 |
| 127 |
| -128 |
+------+
3 rows in set (0.00 sec)
```

### 总结

MySQL数据类型`tinyint`的大小为`-128 ~ 127`,那么如果是无符号的`tinyint`呢?数值范围是:`0 ~ 255`。

【问题解决】Integrity constraint violation: 1022 Can't write; duplicate key in table '#sql-1d6_6'

Reply

后端开发网站管理员 Replyed • 1 person concerned • 1 replies • 1173 views • 2017-12-09 15:00 • 来自相关话题

mysql数据库400W如何处理数据去重

Linux网站管理员 Published the article • 0 comments • 940 views • 2017-07-22 21:08 • 来自相关话题

使用```select count```查看一下数据库数据量

```mysql
mysql> select count(*) from zyads_integral ;
+----------+
| count(*) |
+----------+
| 4130473 |
+----------+
1 row in set (0.01 sec)
```
``desc`查看一下数据表结构

```mysql
mysql> desc zyads_integral;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hash | text | YES | | NULL | |
| sha1 | text | NO | | NULL | |
| name | text | NO | | NULL | |
| index | text | YES | | NULL | |
| size | text | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
```

样例数据

```mysql
mysql> select * from zyads_integral limit 1\G
*************************** 1. row ***************************
id: 6721212
hash: 0FA565EEFA9E688B1F87640815EE090C7326725D
sha1: 8c907b045bb7905cf2a63f0b1208eeb3bca857d6
name: 【无效链接】xxxxxx.html
index: 107
size: 78110108
1 row in set (0.01 sec)
```

接下来开始去掉重复数据
```mysql
mysql> select id, sha1, count(*) from zyads_integral group by sha1 limit 10;
+---------+------------------------------------------+----------+
| id | sha1 | count(*) |
+---------+------------------------------------------+----------+
| 7696 | | 1 |
| 5137851 | 0000000000000000000000000000000005325911 | 2 |
| 5363699 | 00000000000000000000000000000000097ecf88 | 5 |
| 4826139 | 000000000000000000000000000000000fd81983 | 1 |
| 6250586 | 000000000000000000000000000000001b41f909 | 1 |
| 5597063 | 000000000000000000000000000000001d385b7c | 2 |
| 5281295 | 000000000000000000000000000000002a91e078 | 2 |
| 6331972 | 000000000000000000000000000000003488380d | 2 |
| 4774906 | 00000000000000000000000000000000397db43d | 1 |
| 4550736 | 00000000000000000000000000000000494ec71f | 1 |
+---------+------------------------------------------+----------+
10 rows in set (24.71 sec)

mysql> select count(*) from zyads_integral where sha1= '0000000000000000000000000000000005325911';
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (1.03 sec)

mysql> select id, count(*) from zyads_integral group by sha1 having count(*) > 1;
+---------+----------+
| id | count(*) |
+---------+----------+
| 5137851 | 2 |
| 5363699 | 5 |
| 5597063 | 2 |
| 5281295 | 2 |
...
| 4712249 | 6 |
| 1581236 | 3 |
| 5126827 | 2 |
| 1872277 | 7 |
+---------+----------+
836343 rows in set (33.77 sec)
mysql> select id from zyads_integral group by sha1 having count(*) >= 1;
+---------+
| id |
+---------+
| 7696 |
| 5137851 |
| 5363699 |
| 4826139 |
| 6250586 |
...
| 5126827 |
| 570573 |
| 1872277 |
| 4514446 |
+---------+
2466076 rows in set (3 min 36.80 sec)
```

### 删除数据

```mysql
mysql> delete from zyads_integral where id in (select a.id from (select id from zyads_integral group by sha1 having count(*) > 1) a);
CREATE TABLE `zyads_integral_tmp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hash` varchar(100),
`sha1` varchar(100) NOT NULL,
`name` varchar(1000) NOT NULL,
`index` varchar(10),
`size` varchar(10),
UNIQUE KEY `sha1` (`sha1`),
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6756155 DEFAULT CHARSET=gbk

INSERT INTO zyads_integral_tmp (`hash`,`sha1`,`name`,`index`,`size`) SELECT `hash`,`sha1`,`name`,`index`,`size` from zyads_integral group by sha1 having count(*)>=1;

mysql> rename zyads_integral zyads_integral_tmp_1 ;

``` 查看全部

使用```select count```查看一下数据库数据量

```mysql
mysql> select count(*) from zyads_integral ;
+----------+
| count(*) |
+----------+
| 4130473 |
+----------+
1 row in set (0.01 sec)
```
``desc`查看一下数据表结构

```mysql
mysql> desc zyads_integral;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hash | text | YES | | NULL | |
| sha1 | text | NO | | NULL | |
| name | text | NO | | NULL | |
| index | text | YES | | NULL | |
| size | text | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
```

样例数据

```mysql
mysql> select * from zyads_integral limit 1\G
*************************** 1. row ***************************
id: 6721212
hash: 0FA565EEFA9E688B1F87640815EE090C7326725D
sha1: 8c907b045bb7905cf2a63f0b1208eeb3bca857d6
name: 【无效链接】xxxxxx.html
index: 107
size: 78110108
1 row in set (0.01 sec)
```

接下来开始去掉重复数据
```mysql
mysql> select id, sha1, count(*) from zyads_integral group by sha1 limit 10;
+---------+------------------------------------------+----------+
| id | sha1 | count(*) |
+---------+------------------------------------------+----------+
| 7696 | | 1 |
| 5137851 | 0000000000000000000000000000000005325911 | 2 |
| 5363699 | 00000000000000000000000000000000097ecf88 | 5 |
| 4826139 | 000000000000000000000000000000000fd81983 | 1 |
| 6250586 | 000000000000000000000000000000001b41f909 | 1 |
| 5597063 | 000000000000000000000000000000001d385b7c | 2 |
| 5281295 | 000000000000000000000000000000002a91e078 | 2 |
| 6331972 | 000000000000000000000000000000003488380d | 2 |
| 4774906 | 00000000000000000000000000000000397db43d | 1 |
| 4550736 | 00000000000000000000000000000000494ec71f | 1 |
+---------+------------------------------------------+----------+
10 rows in set (24.71 sec)

mysql> select count(*) from zyads_integral where sha1= '0000000000000000000000000000000005325911';
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (1.03 sec)

mysql> select id, count(*) from zyads_integral group by sha1 having count(*) > 1;
+---------+----------+
| id | count(*) |
+---------+----------+
| 5137851 | 2 |
| 5363699 | 5 |
| 5597063 | 2 |
| 5281295 | 2 |
...
| 4712249 | 6 |
| 1581236 | 3 |
| 5126827 | 2 |
| 1872277 | 7 |
+---------+----------+
836343 rows in set (33.77 sec)
mysql> select id from zyads_integral group by sha1 having count(*) >= 1;
+---------+
| id |
+---------+
| 7696 |
| 5137851 |
| 5363699 |
| 4826139 |
| 6250586 |
...
| 5126827 |
| 570573 |
| 1872277 |
| 4514446 |
+---------+
2466076 rows in set (3 min 36.80 sec)
```

### 删除数据

```mysql
mysql> delete from zyads_integral where id in (select a.id from (select id from zyads_integral group by sha1 having count(*) > 1) a);
CREATE TABLE `zyads_integral_tmp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hash` varchar(100),
`sha1` varchar(100) NOT NULL,
`name` varchar(1000) NOT NULL,
`index` varchar(10),
`size` varchar(10),
UNIQUE KEY `sha1` (`sha1`),
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6756155 DEFAULT CHARSET=gbk

INSERT INTO zyads_integral_tmp (`hash`,`sha1`,`name`,`index`,`size`) SELECT `hash`,`sha1`,`name`,`index`,`size` from zyads_integral group by sha1 having count(*)>=1;

mysql> rename zyads_integral zyads_integral_tmp_1 ;

```

【问题解决】Integrity constraint violation: 1022 Can't write; duplicate key in table '#sql-1d6_6'

Reply

后端开发网站管理员 Replyed • 1 person concerned • 1 replies • 1173 views • 2017-12-09 15:00 • 来自相关话题

服务器突然挂掉了,mysql无法启动

后端开发网站管理员 Published the article • 0 comments • 99 views • 2019-08-24 00:01 • 来自相关话题

刚才打算在服务器上跑个项目,因为要记录很多的日志,所有我打算删掉点东西,然后节省空间,方便脚本一直跑下去,不了脚本处理数据多,内存占满了,服务器卡的不行,我只好`killall python`来结束这个脚本。

随后悲剧出现,msyql挂掉了,怎么也启动不了,我使用`mysqld --initialize`来初始化mysql,结果出现如下错误:
```
190823 23:53:07 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
190823 23:53:07 [Note] mysqld (mysqld 5.5.62-0+deb8u1-log) starting as process 3731 ...
mysqld: File '/var/log/mysql/mysql-bin.index' not found (Errcode: 2)
190823 23:53:07 [ERROR] Aborting

190823 23:53:07 [Note] mysqld: Shutdown complete
```

大概看了一下就因为`/var/log/mysql/mysql-bin.index`这个文件找不到,原来是我干才误删的文件,那该怎么办呢?创建一下目录`mkdip -p /var/log/mysql`,然后继续执行上面的命令,果然成功,我的mysql服务终于起来了。

所以还是奉劝大家,删除数据的时候一定要谨慎点,不然半天还摸不着头脑。 查看全部

刚才打算在服务器上跑个项目,因为要记录很多的日志,所有我打算删掉点东西,然后节省空间,方便脚本一直跑下去,不了脚本处理数据多,内存占满了,服务器卡的不行,我只好`killall python`来结束这个脚本。

随后悲剧出现,msyql挂掉了,怎么也启动不了,我使用`mysqld --initialize`来初始化mysql,结果出现如下错误:
```
190823 23:53:07 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
190823 23:53:07 [Note] mysqld (mysqld 5.5.62-0+deb8u1-log) starting as process 3731 ...
mysqld: File '/var/log/mysql/mysql-bin.index' not found (Errcode: 2)
190823 23:53:07 [ERROR] Aborting

190823 23:53:07 [Note] mysqld: Shutdown complete
```

大概看了一下就因为`/var/log/mysql/mysql-bin.index`这个文件找不到,原来是我干才误删的文件,那该怎么办呢?创建一下目录`mkdip -p /var/log/mysql`,然后继续执行上面的命令,果然成功,我的mysql服务终于起来了。

所以还是奉劝大家,删除数据的时候一定要谨慎点,不然半天还摸不着头脑。


mysql 数据导入导出

后端开发网站管理员 Published the article • 0 comments • 319 views • 2018-08-06 16:32 • 来自相关话题

## mysql 如何导入txt

### mysql 远程执行
```
mysql -hwww.xxx.com -uroot -p -e 'select * from detail_kd' > /tmp/detail_kd.txt
```

### mysql 输出的数据为
```
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
| detail_integral_id | user_id | integral | total_integral | target_id | target_type | create_time |
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
| 74618432 | 12963299 | 30 | 11991729 | 0 | room | 1533163756829 |
| 74619240 | 12963299 | 210 | 11991939 | 0 | room | 1533165021613 |
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
```

### 导入sql语句
```sql
load data infile '/tmp/detail_kd.txt' into table detail_kd fields terminated by '\t';
```

infile 后面是导入```txt```的路径,
into table detail_kd kd详情表
terminated byb '\t' 分隔符号

### ```mysqldump```导入导出

--where 可以指定过滤条件

```
mysqldump -uroot -p test detail_integral --where="integral > 330 and user_id > 1" > /tmp/detail_kd.sql
```

```
source /tmp/detail_kd.sql
``` 查看全部

## mysql 如何导入txt

### mysql 远程执行
```
mysql -hwww.xxx.com -uroot -p -e 'select * from detail_kd' > /tmp/detail_kd.txt
```

### mysql 输出的数据为
```
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
| detail_integral_id | user_id | integral | total_integral | target_id | target_type | create_time |
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
| 74618432 | 12963299 | 30 | 11991729 | 0 | room | 1533163756829 |
| 74619240 | 12963299 | 210 | 11991939 | 0 | room | 1533165021613 |
+--------------------+----------+----------+----------------+-----------+-------------+---------------+
```

### 导入sql语句
```sql
load data infile '/tmp/detail_kd.txt' into table detail_kd fields terminated by '\t';
```

infile 后面是导入```txt```的路径,
into table detail_kd kd详情表
terminated byb '\t' 分隔符号

### ```mysqldump```导入导出

--where 可以指定过滤条件

```
mysqldump -uroot -p test detail_integral --where="integral > 330 and user_id > 1" > /tmp/detail_kd.sql
```

```
source /tmp/detail_kd.sql
```

mysql5.7Invalid datetime format: 1292 Incorrect date value: '0000-00-00' for column 'day' at row 1

PHP Published the article • 0 comments • 2249 views • 2018-03-26 17:46 • 来自相关话题

reports表中day字段数据类型为date 默认值为 `0000-00-00`,在mysql5.7中这个是会报错,不允许的,且导致无法修改其他字段的数据类型。

现修改day默认值为`1970-01-01`,且批量更新day为null值的数据,修改为`1970-01-01`

### 同理

DATETIME等字段也需要修改为`1970-01-01 00:00:00`

```mysql
alter table reports modify column birthday DATETIME not null default '1970-01-01 00:00:00';
alter table reports modify column day DATE not null default '1970-01-01';
``` 查看全部
reports表中day字段数据类型为date 默认值为 `0000-00-00`,在mysql5.7中这个是会报错,不允许的,且导致无法修改其他字段的数据类型。

现修改day默认值为`1970-01-01`,且批量更新day为null值的数据,修改为`1970-01-01`

### 同理

DATETIME等字段也需要修改为`1970-01-01 00:00:00`

```mysql
alter table reports modify column birthday DATETIME not null default '1970-01-01 00:00:00';
alter table reports modify column day DATE not null default '1970-01-01';
```

如何快速找出文件中创建数据库表的数据库名

Linux网站管理员 Published the article • 0 comments • 390 views • 2018-03-06 19:01 • 来自相关话题

如何快速找出文件中创建数据库表的数据库名,

### linux命令

```bash
grep -r "CREATE TABLE \`[^\`]*\`" . | awk -F\` '{print $2}' | sort | uniq
```
如何快速找出文件中创建数据库表的数据库名,

### linux命令

```bash
grep -r "CREATE TABLE \`[^\`]*\`" . | awk -F\` '{print $2}' | sort | uniq
```

mysql通过插入数据来查看tinyint的大小

PHP网站管理员 Published the article • 0 comments • 372 views • 2018-02-23 11:09 • 来自相关话题

### 查看表结构
```
MariaDB [film]> show create table test;
+-------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

### 插入int类型的id,大于`tinyint`的数值

```bash
MariaDB [film]> insert into test set id = 9999;
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [film]> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'id' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
```

### 查看写入的数据
数据写进去了,但是被截断了,如果在MySQL数据库严格模式下,这条数据将无法写入
```
MariaDB [film]> select *from test;
+-----+
| id |
+-----+
| 2 |
| 127 |
+-----+
2 rows in set (0.00 sec)
```
### 插入一个负数

我们得到了同样的结果,

```
MariaDB [film]> insert into test set id = -9999;
Query OK, 1 row affected, 1 warning (0.02 sec)
```
```
MariaDB [film]> select *from test;
+------+
| id |
+------+
| 2 |
| 127 |
| -128 |
+------+
3 rows in set (0.00 sec)
```

### 总结

MySQL数据类型`tinyint`的大小为`-128 ~ 127`,那么如果是无符号的`tinyint`呢?数值范围是:`0 ~ 255`。 查看全部
### 查看表结构
```
MariaDB [film]> show create table test;
+-------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

### 插入int类型的id,大于`tinyint`的数值

```bash
MariaDB [film]> insert into test set id = 9999;
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [film]> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'id' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
```

### 查看写入的数据
数据写进去了,但是被截断了,如果在MySQL数据库严格模式下,这条数据将无法写入
```
MariaDB [film]> select *from test;
+-----+
| id |
+-----+
| 2 |
| 127 |
+-----+
2 rows in set (0.00 sec)
```
### 插入一个负数

我们得到了同样的结果,

```
MariaDB [film]> insert into test set id = -9999;
Query OK, 1 row affected, 1 warning (0.02 sec)
```
```
MariaDB [film]> select *from test;
+------+
| id |
+------+
| 2 |
| 127 |
| -128 |
+------+
3 rows in set (0.00 sec)
```

### 总结

MySQL数据类型`tinyint`的大小为`-128 ~ 127`,那么如果是无符号的`tinyint`呢?数值范围是:`0 ~ 255`。

mysql数据库400W如何处理数据去重

Linux网站管理员 Published the article • 0 comments • 940 views • 2017-07-22 21:08 • 来自相关话题

使用```select count```查看一下数据库数据量

```mysql
mysql> select count(*) from zyads_integral ;
+----------+
| count(*) |
+----------+
| 4130473 |
+----------+
1 row in set (0.01 sec)
```
``desc`查看一下数据表结构

```mysql
mysql> desc zyads_integral;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hash | text | YES | | NULL | |
| sha1 | text | NO | | NULL | |
| name | text | NO | | NULL | |
| index | text | YES | | NULL | |
| size | text | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
```

样例数据

```mysql
mysql> select * from zyads_integral limit 1\G
*************************** 1. row ***************************
id: 6721212
hash: 0FA565EEFA9E688B1F87640815EE090C7326725D
sha1: 8c907b045bb7905cf2a63f0b1208eeb3bca857d6
name: 【无效链接】xxxxxx.html
index: 107
size: 78110108
1 row in set (0.01 sec)
```

接下来开始去掉重复数据
```mysql
mysql> select id, sha1, count(*) from zyads_integral group by sha1 limit 10;
+---------+------------------------------------------+----------+
| id | sha1 | count(*) |
+---------+------------------------------------------+----------+
| 7696 | | 1 |
| 5137851 | 0000000000000000000000000000000005325911 | 2 |
| 5363699 | 00000000000000000000000000000000097ecf88 | 5 |
| 4826139 | 000000000000000000000000000000000fd81983 | 1 |
| 6250586 | 000000000000000000000000000000001b41f909 | 1 |
| 5597063 | 000000000000000000000000000000001d385b7c | 2 |
| 5281295 | 000000000000000000000000000000002a91e078 | 2 |
| 6331972 | 000000000000000000000000000000003488380d | 2 |
| 4774906 | 00000000000000000000000000000000397db43d | 1 |
| 4550736 | 00000000000000000000000000000000494ec71f | 1 |
+---------+------------------------------------------+----------+
10 rows in set (24.71 sec)

mysql> select count(*) from zyads_integral where sha1= '0000000000000000000000000000000005325911';
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (1.03 sec)

mysql> select id, count(*) from zyads_integral group by sha1 having count(*) > 1;
+---------+----------+
| id | count(*) |
+---------+----------+
| 5137851 | 2 |
| 5363699 | 5 |
| 5597063 | 2 |
| 5281295 | 2 |
...
| 4712249 | 6 |
| 1581236 | 3 |
| 5126827 | 2 |
| 1872277 | 7 |
+---------+----------+
836343 rows in set (33.77 sec)
mysql> select id from zyads_integral group by sha1 having count(*) >= 1;
+---------+
| id |
+---------+
| 7696 |
| 5137851 |
| 5363699 |
| 4826139 |
| 6250586 |
...
| 5126827 |
| 570573 |
| 1872277 |
| 4514446 |
+---------+
2466076 rows in set (3 min 36.80 sec)
```

### 删除数据

```mysql
mysql> delete from zyads_integral where id in (select a.id from (select id from zyads_integral group by sha1 having count(*) > 1) a);
CREATE TABLE `zyads_integral_tmp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hash` varchar(100),
`sha1` varchar(100) NOT NULL,
`name` varchar(1000) NOT NULL,
`index` varchar(10),
`size` varchar(10),
UNIQUE KEY `sha1` (`sha1`),
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6756155 DEFAULT CHARSET=gbk

INSERT INTO zyads_integral_tmp (`hash`,`sha1`,`name`,`index`,`size`) SELECT `hash`,`sha1`,`name`,`index`,`size` from zyads_integral group by sha1 having count(*)>=1;

mysql> rename zyads_integral zyads_integral_tmp_1 ;

``` 查看全部

使用```select count```查看一下数据库数据量

```mysql
mysql> select count(*) from zyads_integral ;
+----------+
| count(*) |
+----------+
| 4130473 |
+----------+
1 row in set (0.01 sec)
```
``desc`查看一下数据表结构

```mysql
mysql> desc zyads_integral;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hash | text | YES | | NULL | |
| sha1 | text | NO | | NULL | |
| name | text | NO | | NULL | |
| index | text | YES | | NULL | |
| size | text | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
```

样例数据

```mysql
mysql> select * from zyads_integral limit 1\G
*************************** 1. row ***************************
id: 6721212
hash: 0FA565EEFA9E688B1F87640815EE090C7326725D
sha1: 8c907b045bb7905cf2a63f0b1208eeb3bca857d6
name: 【无效链接】xxxxxx.html
index: 107
size: 78110108
1 row in set (0.01 sec)
```

接下来开始去掉重复数据
```mysql
mysql> select id, sha1, count(*) from zyads_integral group by sha1 limit 10;
+---------+------------------------------------------+----------+
| id | sha1 | count(*) |
+---------+------------------------------------------+----------+
| 7696 | | 1 |
| 5137851 | 0000000000000000000000000000000005325911 | 2 |
| 5363699 | 00000000000000000000000000000000097ecf88 | 5 |
| 4826139 | 000000000000000000000000000000000fd81983 | 1 |
| 6250586 | 000000000000000000000000000000001b41f909 | 1 |
| 5597063 | 000000000000000000000000000000001d385b7c | 2 |
| 5281295 | 000000000000000000000000000000002a91e078 | 2 |
| 6331972 | 000000000000000000000000000000003488380d | 2 |
| 4774906 | 00000000000000000000000000000000397db43d | 1 |
| 4550736 | 00000000000000000000000000000000494ec71f | 1 |
+---------+------------------------------------------+----------+
10 rows in set (24.71 sec)

mysql> select count(*) from zyads_integral where sha1= '0000000000000000000000000000000005325911';
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (1.03 sec)

mysql> select id, count(*) from zyads_integral group by sha1 having count(*) > 1;
+---------+----------+
| id | count(*) |
+---------+----------+
| 5137851 | 2 |
| 5363699 | 5 |
| 5597063 | 2 |
| 5281295 | 2 |
...
| 4712249 | 6 |
| 1581236 | 3 |
| 5126827 | 2 |
| 1872277 | 7 |
+---------+----------+
836343 rows in set (33.77 sec)
mysql> select id from zyads_integral group by sha1 having count(*) >= 1;
+---------+
| id |
+---------+
| 7696 |
| 5137851 |
| 5363699 |
| 4826139 |
| 6250586 |
...
| 5126827 |
| 570573 |
| 1872277 |
| 4514446 |
+---------+
2466076 rows in set (3 min 36.80 sec)
```

### 删除数据

```mysql
mysql> delete from zyads_integral where id in (select a.id from (select id from zyads_integral group by sha1 having count(*) > 1) a);
CREATE TABLE `zyads_integral_tmp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hash` varchar(100),
`sha1` varchar(100) NOT NULL,
`name` varchar(1000) NOT NULL,
`index` varchar(10),
`size` varchar(10),
UNIQUE KEY `sha1` (`sha1`),
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6756155 DEFAULT CHARSET=gbk

INSERT INTO zyads_integral_tmp (`hash`,`sha1`,`name`,`index`,`size`) SELECT `hash`,`sha1`,`name`,`index`,`size` from zyads_integral group by sha1 having count(*)>=1;

mysql> rename zyads_integral zyads_integral_tmp_1 ;

```