mysql
科学的N次方
IT菜鸟,啥也不懂
展开
-
在mysql中索引字段类型是设置为bigint?还是varchar好?
综合来看,如果索引字段是用来存储数值型标识符,尤其是类似自增ID那样有序增长的数据,bigint通常是更好的选择。而如果字段是用来存储非数值的文本或字符串,且需要对其创建索引以提升查询性能,varchar是可以选择的,但要注意合理控制字段长度,尽量保持索引紧凑,以优化索引效率。•大整数类型,适合存储整数或长整数类型的标识符、时间戳等数值型数据。•存储空间固定,对于查询性能较为友好,尤其是在做JOIN、GROUP BY、ORDER BY以及WHERE子句过滤时,由于整数比较速度快,索引效率较高。原创 2024-03-20 23:39:40 · 490 阅读 · 0 评论 -
centos7 mysql8.0.23卸载
yum remove mysql-community-*rm -rf /var/lib/mysqlrm -rf /var/lib/mysql/mysqlrm -rf /usr/lib64/mysqlrm -rf /usr/share/mysql*rm -rf /etc/my.cnfrm -rf /var/log/mysqld.log原创 2021-03-22 17:12:48 · 392 阅读 · 0 评论 -
centos7 mysql8.0.23 shell 一键安装
rpm -ivh mysql-community-common-8.0.23-1.el7.x86_64.rpmrpm -ivh mysql-community-client-plugins-8.0.23-1.el7.x86_64.rpmrpm -ivh mysql-community-libs-8.0.23-1.el7.x86_64.rpmrpm -ivh mysql-community-client-8.0.23-1.el7.x86_64.rpmrpm -ivh mysql-community-s原创 2021-03-19 11:35:33 · 181 阅读 · 0 评论 -
canalDemo实时同步mysql数据库数据介绍
一、环境介绍canal是阿里开源的中间件,主要用于同步mysql数据库变更。具体参见:https://github.com/alibaba/canal/releases搭建环境:Centos6.5 64位二、部署canal server(参考:https://github.com/alibaba/canal/wiki/QuickStart)1.下载canal serverhttps://github.com/alibaba/canal/releases/download/can...原创 2020-12-31 11:50:57 · 236 阅读 · 0 评论 -
mysql 锁表与解锁
(一) 解锁方法1连接mysql ,直接执行UNLOCK TABLES,细节如下:– 查询是否锁表show OPEN TABLES ;– 查询进程show processlist ;– 查询到相对应的进程,然后杀死进程kill id; – 一般到这一步就解锁了– 查看正在锁的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;– 查看等待锁的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAIT原创 2020-12-22 15:36:59 · 905 阅读 · 0 评论 -
PYTHON ETL TEMPLATE
#!/usr/bin/python2import MySQLdbimport sysdef create_source_conn(): conn = MySQLdb.connect('XXXX', 'XXX', 'XXXX', 'XXX', 3306, charset='utf8') cursor = conn.cursor() return conn, cursordef create_target_conn(): conn = MySQLdb.conne原创 2020-09-17 18:10:18 · 91 阅读 · 0 评论 -
Mysql字段默认值设为当前时间
5.5以上版本:alter table test add column createtime datetime DEFAULT NOW() comment ‘创建时间’或者alter table test add column createtime datetime DEFAULT CURRENT_TIMESTAMP comment ‘创建时间’5.5及以下版本:alter table...原创 2019-12-18 13:45:53 · 2052 阅读 · 0 评论 -
mysql数据库迁移
mysql数据库迁移1.创建用户,并授权2.搭建主从,做到主从同步,数据目前先保持一致,在切换主从同步切换ipMysql5.1已有数据量了,为保证数据的一致性。故需要锁库,等数据导入后再解锁3.需要注意从库和主库的引擎,字符集show variables like ‘character_set%’; --查看当前环境的character_setshow variables like ‘...原创 2019-12-17 17:06:10 · 260 阅读 · 0 评论 -
mysql 报com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
早上看日志发现连接超时报错: Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was48477 seconds ago.The last packet sent successfully to the s原创 2018-01-11 10:11:10 · 3475 阅读 · 0 评论 -
MySQL配置文件my.cnf参数优化和中文详解
[client]port = 3306socket = /tmp/mysql.sock[mysqld]port = 3306socket = /tmp/mysql.sockbasedir = /usr/local/mysqldatadir = /data/mysqlpid-file = /data/mysql/mysql.piduser = mysqlbind-address =转载 2017-11-24 14:07:35 · 186 阅读 · 0 评论 -
null, message from server: “Host ‘xxx‘ is blocked because of many connection errors; unblock
Host is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’ 解决方法 环境:centos6.5,mysql5.1.17错误:Host is blocked because of many connection errors; unblock with ‘mysqladmin flu原创 2017-11-24 13:59:28 · 12135 阅读 · 0 评论