解决mysql命令行无法输入中文

https://blog.csdn.net/Just_learn_more/article/details/91385467?depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-7&utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-7

尝试了各种操作后,甚至参考 https://www.lagou.com/lgeduarticle/23074.html 更改了各种系统变量,均无果。最后发现是my.cnf中设置过度了:

  • 当我注释掉[mysql]和[client]下的default-character-set=utf8并重新启动mysql后,登陆mysql数据库可以正常使用中文,终于被我找到了这个终极解决方案!

很明显,命令行对于中文的兼容不是服务器的问题,因为我服务器兼容中文没有问题

mysql简单查询优化

最近爬Boss直聘数据,因为没有使用scrapy这种框架,自建轮子写的整个框架,构造的url和数据存储都在阿里云国际的vps上(1G内存,CPU也是弱鸡),到了40多万时mysql查询就成了瓶颈。

研究了下,主要从数据库和查询语句两方面下手优化:

  1. innodb_buffer_pool_size = 256M (这个最重要,但是我内存很小,设置过高后容易把机器卡死,没办法)
  2. max_allowed_packet = 512M(这里对我没有意义,因为内存很小)
  3. 开启慢查询,针对性的优化了查询语句, 加索引 参考 https://www.mysqltutorial.org/mysql-index/mysql-create-index/ ,还有就是避免 like %s, NULL,所有查询都要加字段where避免select *:

slow_query_log = ON
slow_query_log_file = /var/lib/mysql/tmp_slow.log
long_query_time = 10

(boss) [[email protected] boss]$ sudo mysqldumpslow -t 10  /var/lib/mysql/tmp_slow.log

Reading mysql slow query log from /var/lib/mysql/tmp_slow.log
Count: 3  Time=20.94s (62s)  Lock=0.00s (0s)  Rows=590255.0 (1770765), root[root]@[47.56.250.177]
  SELECT `job_url` FROM jobs

Count: 68  Time=16.41s (1116s)  Lock=0.00s (0s)  Rows=589155.1 (40062550), root[root]@25hosts
  SELECT `job_url`, `city_code` FROM jobs

Count: 11  Time=16.38s (180s)  Lock=0.00s (0s)  Rows=110793.7 (1218731), root[root]@10hosts
  select `main_url` from start_url_cp LIMIT N, N

Died at /bin/mysqldumpslow line 167, <> chunk 82.

因为脚本框架的原因,需要查询已经存在的url防止重复爬取,所以数据量大时进行全表查询确实费时,加上网络延迟,各个爬虫host都在谷歌GCE上,这个效果目前能接受:

mysql> select count(job_url) from jobs;
+—————-+
| count(job_url) |
+—————-+
| 592501 |
+—————-+
1 row in set (2.35 sec)

之前这个查询非常慢了,效率指数级下降,几十秒甚至十几分钟。参考:

https://blog.csdn.net/u011277123/article/details/72627011

查询历史命令: cat  ~/.mysql_history

自从前几天爬了boss直聘7000+职位信息后,一直停滞不前,遇到了几个坑,今天算是重新启动了,感谢亚马逊!
https://geoseis.cn/CoV_tab.html
自适应各种平台显示,新版脚本也格式化成了标签形式。
根据我的爬虫脚本实时监控数据可视化,美国新增病历继续高歌猛进,闭着眼睛都能想到美股或者原油又崩盘了。目前为止161872累计确诊病例,但是不得不说,这个数据应该经得住敲打。
今天我的boss直聘脚本已经突破了1000行,主要还是因为脚本考虑的太周全,超时处理,没有找到相关职位的空页面处理,遇到机器人反爬处理,各种代理设置,反爬策略,多种日志并进(sql和csv),不知不觉就几百上千行了。如果用scrapy这种框架会少很多,不用自己造轮子。

疫情监控脚本可视化
一小部分boss直聘数据

废寝忘食地搞了两天,终于搞明白了几个服务。最近天天想方设法寻找合适的ip代理了,现在发现脚本写的越来越长不仅不方便维护,效率也下来了,有时间得整改下,套用下成熟的框架试试。
昨天发现新大陆,昨天晚上连夜研究,今天终于把脚本写好了,利用亚马逊服务器可以虽然更换ip的功能,调用它的api,用python写了个脚本每个两分钟更换一次ip,其实挺简单,就是release, allocate, attach,核心就三步,扔掉原来ip,分配新ip,新ip分配给服务器。当然,其中还有从远程服务器删除旧ip等过程。
美国服务器的ip更新后自动存到远在日本的服务器(上面架设了redis服务),笔记本配置好redis客户端后python脚本连接日本的服务器redis,获取ip。
看似简单,实际操作中还是挺繁琐,很多坑要踩。当然,最重要的是过程,国外的服务器还是不适合,gfw的过滤明显减慢速度。
这两天还配置了squid服务器,但是不知怎么不太好使,可能跟阿布云代理有关。数据没采集多少,💰 没少花,时间更是大把浪费了。

SQL处理数据-字符串和重复数据处理

mysql重复数据和字符串处理

因为爬取的wikipedia数据不规范,写入MySQL数据库时没能定义好Primary Key和字符类型,后来发现主要是有几个小问题:

  • 地区人口一栏中有不少非数字(县级市比如保定市),城市一栏因为这个原因也是有重复值(后来删掉了县级市,不需要)
  • 香港澳门城镇人口一栏缺失值,城市一栏有中括号

NULL值更新成0

mysql> UPDATE cities_population
-> SET
-> urban_population=0
-> WHERE urban_population IS null;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

删除县级市

mysql> DELETE FROM cities_population WHERE class='县级市';
Query OK, 772 rows affected (0.01 sec)

删除个别城市名称后的一个字符

mysql> UPDATE cities_population
-> SET city=CONCAT(LEFT(city, CHAR_LENGTH(city) -1), '')
-> WHERE city NOT LIKE '%市' AND city NOT LIKE '香港%' AND city NOT LIKE '澳门%';
Query OK, 12 rows affected (0.00 sec)
Rows matched: 12 Changed: 12 Warnings: 0

可以看到香港澳门城市名称里的中括号

mysql> SELECT * FROM cities_population WHERE city LIKE '%[%';
+-------+----------+-----------+------------+------------------+------------------+------------------+-------------+
| index | province | city | class | total_population | admin_population | urban_population | report_time |
+-------+----------+-----------+------------+------------------+------------------+------------------+-------------+
| 378 | 香港 | 香港 [2] | 特别行政区 | 7071576 | 7071576 | NULL | 2011-06-30 |
| 379 | 澳门 | 澳门 [3] | 特别行政区 | 552503 | 552503 | NULL | 2011-08-12 |
| 468 | 香港 | 香港 [5] | 特别行政区 | 6708389 | 6708389 | NULL | 2001-03-14 |
| 469 | 澳门 | 澳门 [6] | 特别行政区 | 435235 | 435235 | NULL | 2001-08-23 |
+-------+----------+-----------+------------+------------------+------------------+------------------+-------------+
4 rows in set (0.00 sec)

去掉中括号:

mysql> UPDATE cities_population
-> SET
-> city=province
-> WHERE city LIKE '%[%';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

检查城市名称重复次数:

mysql> SELECT COUNT(*) as repetitions,city
-> FROM cities_population
-> GROUP BY city
-> HAVING repetitions > 1;

mysql> SELECT * FROM cities_population
-> WHERE 城市 LIKE '%香港%';
+------+-----------+------------+-----------------+-------------------+-----------------+------------+
| 省市 | 城市 | 层级 | 地区 人口 [1] | 市辖区 人口 [1] | 城镇 人口 [1] | 普查年月日 |
+------+-----------+------------+-----------------+-------------------+-----------------+------------+
| 香港 | 香港 [2] | 特别行政区 | 7071576 | 7071576 | NULL | 2011-06-30 |
+------+-----------+------------+-----------------+-------------------+-----------------+------------+

可以看到县级市:

mysql> SELECT * FROM cities_population LIMIT 10;
+------+----------+--------+-----------------+-------------------+-----------------+------------+
| 省市 | 城市 | 层级 | 地区 人口 [1] | 市辖区 人口 [1] | 城镇 人口 [1] | 普查年月日 |
+------+----------+--------+-----------------+-------------------+-----------------+------------+
| 北京 | 北京市 | 直辖市 | 19612368 | 18827262 | 16446857 | 2010-11-01 |
| 天津 | 天津市 | 直辖市 | 12938693 | 11090783 | 9562255 | 2010-11-01 |
| 河北 | 石家庄市 | 地级市 | 10163788 | 2834942 | 2770344 | 2010-11-01 |
| 河北 | 唐山市 | 地级市 | 7577289 | 3187171 | 2128191 | 2010-11-01 |
| 河北 | 秦皇岛市 | 地级市 | 2987605 | 1029670 | 967877 | 2010-11-01 |
| 河北 | 邯郸市 | 地级市 | 9174683 | 1445338 | 1316674 | 2010-11-01 |
| 河北 | 邢台市 | 地级市 | 7104103 | 670154 | 668765 | 2010-11-01 |
| 河北 | 保定市 | 地级市 | 11194382 | 1138521 | 1038195 | 2010-11-01 |
| 河北 | 定州市 | 县级市 | 保定市 | 1165182 | 482121 | 2010-11-01 |
| 河北 | 张家口市 | 地级市 | 4345485 | 1060605 | 924628 | 2010-11-01 |
+------+----------+--------+-----------------+-------------------+-----------------+------------+

可以看到数据类型问题,即使命令行先创建也不行(可能是因为脚本里写入SQL我用了replace)

mysql> DESC cities_population;
+------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| province | text | YES | | NULL | |
| city | text | YES | | NULL | |
| class | text | YES | | NULL | |
| total_population | text | YES | | NULL | |
| admin_population | bigint(20) | YES | | NULL | |
| urban_population | text | YES | | NULL | |
| report_time | text | YES | | NULL | |
+------------------+------------+------+-----+---------+-------+

处理香港澳门城市名称问题:

mysql> UPDATE cities_population
-> SET city=CONCAT(LEFT(city, CHAR_LENGTH(city) -1), '')
-> WHERE city NOT LIKE '%市' AND city NOT LIKE '香港%' AND city NOT LIKE '澳门%';
Query OK, 12 rows affected (0.00 sec)
Rows matched: 12 Changed: 12 Warnings: 0

修改字符类型:

mysql> DESC cities_population;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| index            | bigint(20)  | YES  | MUL | NULL    |       |
| province         | text        | YES  |     | NULL    |       |
| city             | varchar(20) | NO   |     | NULL    |       |
| class            | text        | YES  |     | NULL    |       |
| total_population | text        | YES  |     | NULL    |       |
| admin_population | bigint(20)  | YES  |     | NULL    |       |
| urban_population | text        | YES  |     | NULL    |       |
| report_time      | text        | YES  |     | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> ALTER TABLE cities_population
    -> MODIFY province VARCHAR(20) NOT NULL,
    -> MODIFY class VARCHAR(20) NOT NULL,
    -> MODIFY total_population FLOAT NOT NULL,
    -> MODIFY urban_population FLOAT NOT NULL,
    -> MODIFY report_time DATE;
Query OK, 878 rows affected, 2 warnings (0.03 sec)
Records: 878  Duplicates: 0  Warnings: 2

mysql> DESC cities_population;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| index            | bigint(20)  | YES  | MUL | NULL    |       |
| province         | varchar(20) | NO   |     | NULL    |       |
| city             | varchar(20) | NO   |     | NULL    |       |
| class            | varchar(20) | NO   |     | NULL    |       |
| total_population | float       | NO   |     | NULL    |       |
| admin_population | bigint(20)  | YES  |     | NULL    |       |
| urban_population | float       | NO   |     | NULL    |       |
| report_time      | date        | YES  |     | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> CREATE TABLE tmp SELECT DISTINCT *  FROM  cities_population;
Query OK, 691 rows affected (0.02 sec)
Records: 691  Duplicates: 0  Warnings: 0

mysql> DROP TABLE cities_population;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE tmp RENAME TO  cities_population;
Query OK, 0 rows affected (0.01 sec)
这样就删除了重复数据:
mysql> ALTER TABLE cities_population
    -> ADD PRIMARY KEY(city,report_time);
Query OK, 691 rows affected (0.02 sec)
Records: 691  Duplicates: 0  Warnings: 0

mysql> DESC cities_population;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| index            | bigint(20)  | YES  |     | NULL    |       |
| province         | varchar(20) | NO   |     | NULL    |       |
| city             | varchar(20) | NO   | PRI | NULL    |       |
| class            | varchar(20) | NO   |     | NULL    |       |
| total_population | float       | NO   |     | NULL    |       |
| admin_population | bigint(20)  | YES  |     | NULL    |       |
| urban_population | float       | NO   |     | NULL    |       |
| report_time      | date        | NO   | PRI | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

肺炎疫情-用数据分析的角度来看我们的医疗

领英也分享过。

能用数据说话咱就不BB!
通过这次数据分析,能清晰地看到我们平时的有些认知和事实是有差距的。三甲医院总数辽宁95,广东89,湖北88,山东82,江苏74,黑龙江70,河南北京60,天津55。按照人均医院,天津稳居第一,北京上海辽宁排名也很靠前,青海这个黑马(西藏因为人少也很有优势)我没去核实数据。
从数据上看,天津北上辽是养老居家圣地,辽宁作为老工业基地的底子现在看来仍旧不可小觑。数据上看东北养老还是不错的,冬天就去海南嘛。如果不差钱,广州应该是个养老的好地方,医疗水平高,环境也不错,要知道老人都是怕冷的。所以说,好好赚钱,争取在广东养老才是正道。这次疫情的焦点湖北省医疗资源一点不差,昨晚(其实昨晚没睡)没来得及做完城市数据可视化,武汉的三甲医院(46家)也是第一排的,所以不是武汉撑不住,任何一个正常的医疗系统都不可能承担这么严重的疫情。最牛的天津也是23万人才有一家三甲医院好嘛!