mysql,jdbc、连接池

时间：2023-09-18 17:04:50

在UTF-8编码中，一个英文字母字符存储需要1个字节，一个汉字字符储存需要3到4个字节。

1byte=8bit 1byte就是1B
1KB=1024B

free -m Mem”后的total列就是内存大小。3500实际就是4GB

show processlist;     select * from information_schema.processlist;

Command：

　　   The type of command the thread is executing. 例如上面的例子中，Sleep，或者Query

时间戳timestamp
create table t_var (id VARCHAR(10),name VARCHAR(255),descs VARCHAR(500)
,create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
,update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
create_time 在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新它
update_time 在创建新记录和修改现有记录的时候都对这个数据列刷新

一个字节8位
byte 8位
short 16位
int 32位
long 64位
float 32位
double 64位
char 16位

一个char能够存储16bit大小的数值，即2个字节。就常用的UTF-8编码来说，我们都听说过他是用3或者4个字节来表示一个汉字的
mysql varchar(10) 10个字符或汉字

4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个，最大大小是65532字节 ；varchar(20)在Mysql4中最大也不过是20个字节,但是Mysql5根据编码不同,存储大小也不同，具体有以下规则：
a) 存储限制
varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535。
b) 编码长度限制
字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766;
字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845。
若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。
c) 行长度限制
导致实际应用中varchar长度限制的是一个行定义的长度。 MySQL要求一个行的定义长度不能超过65535。

mysql varchar 自动截取问题

explain select * from ttt where id = 1;

varchar(3) 汉字也是3个 字符长度 最大255个字符（汉字）
int(32) 21亿 2119010118 10位数字   unsigned 无符号  范围增加一倍 
不论是int(3)还是int(11)，它在数据库里面存储的都是4个字节的长度，在使用int(3)的时候如果你输入的是10，会默认给你存储位010
创建类型的时候加 zerofill这个值，表示用0填充，否则看不出效果
double(12,2) 总共12位，小数点后两位

数字型

类型
大小
范围（有符号）
范围（无符号）
用途
TINYINT
1 字节
(-128，127)
(0，255)
小整数值
SMALLINT
2 字节
(-32 768，32 767)
(0，65 535)
大整数值
MEDIUMINT
3 字节
(-8 388 608，8 388 607)
(0，16 777 215)
大整数值
INT或INTEGER
4 字节
(-2 147 483 648，2 147 483 647)
(0，4 294 967 295)
大整数值
BIGINT
8 字节
(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)
(0，18 446 744 073 709 551 615)
极大整数值
FLOAT
4 字节
(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)
0，(1.175 494 351 E-38，3.402 823 466 E+38)
单精度
浮点数值
DOUBLE
8 字节
(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
双精度
浮点数值
DECIMAL
对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2
依赖于M和D的值
依赖于M和D的值
小数值

字符类型

CHAR
0-255字节
定长字符串
VARCHAR
0-255字节
变长字符串
TINYBLOB
0-255字节
不超过 255 个字符的二进制字符串
TINYTEXT
0-255字节
短文本字符串
BLOB
0-65 535字节
二进制形式的长文本数据
TEXT
0-65 535字节
长文本数据
MEDIUMBLOB
0-16 777 215字节
二进制形式的中等长度文本数据
MEDIUMTEXT
0-16 777 215字节
中等长度文本数据
LOGNGBLOB
0-4 294 967 295字节
二进制形式的极大文本数据
LONGTEXT
0-4 294 967 295字节
极大文本数据

枚举集合
ENUM （最多65535个成员） 64KB
SET （最多64个成员） 64KB

时间类型

类型
大小
(字节)
范围
格式
用途
DATE
3
1000-01-01/9999-12-31
YYYY-MM-DD
日期值
TIME
3
'-838:59:59'/'838:59:59'
HH:MM:SS
时间值或持续时间
YEAR
1
1901/2155
YYYY
年份值
DATETIME
8
1000-01-01 00:00:00/9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS
混合日期和时间值
TIMESTAMP
8
1970-01-01 00:00:00/2037 年某时
YYYYMMDD HHMMSS
混合日期和时间值，时间戳

mysql连接情况
show processlist; show full processlist;
select * from information_schema.processlist;

唯一键 UNIQUE KEY 索引使用，字符串必须加''才能使用索引    创建唯一约束的时候就创建了唯一索引

查询效率
const 主键
const unique唯一约束 联合唯一，全部使用为const，只使用第一个为ref
ref 索引

1、type
这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
说明：不同连接类型的解释（按照效率高低的顺序排序）
system：表只有一行：system表。这是const连接类型的特殊情况。
const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。
eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。
ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

const
在WHERE子句中包含条件column = constant，并且这些列是primary key，或者这些列是UNIQUE（假设该UNIQUE同时被定义为NOT NULL）。这样生成的查询结果也可以成为常量表
eq_ref
对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。
ref
对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。
ref_or_null
该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化

2、key_len
使用的索引的长度。在不损失精确性的情况下，长度越短越好
3、Extra
Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。

/**
     * The minimum number of active connections that can remain idle in the
     * pool, without extra ones being created, or 0 to create none.
     */
    protected int minIdle = GenericObjectPool.DEFAULT_MIN_IDLE;

/**
     * The maximum number of connections that can remain idle in the
     * pool, without extra ones being released, or negative for no limit.
     */
    protected int maxIdle = GenericObjectPool.DEFAULT_MAX_IDLE;

initialSize 10 初始化连接:连接池启动时创建的初始化连接数量,1.2版本后支持
maxActive 100 最大活动连接:连接池在同一时间能够分配的最大活动连接的数量,
如果设置为非正数则表示不限制
maxIdle 10 最大空闲连接:连接池中容许保持空闲状态的最大连接数量,超过的空闲连接将被释放,
如果设置为负数表示不限制
minIdle 5 最小空闲连接:连接池中容许保持空闲状态的最小连接数量,低于这个数量将创建新的连接,
如果设置为0则不创建
maxWait 无限 最大等待时间:当没有可用连接时,连接池等待连接被归还的最大时间(以毫秒计数),
超过时间则抛出异常,如果设置为-1表示无限等待

package service;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.mysql.jdbc.PreparedStatement;

public class TestConnect {

	static{

		try {

			Class.forName("com.mysql.jdbc.Driver");

		} catch (ClassNotFoundException e) {

			System.out.println("找不到驱动程序类 ，加载驱动失败！");

			e.printStackTrace();

		}

	}

    private static Connection getConnect(){

		String url = "jdbc:mysql://localhost:3306/xmh?useUnicode=true&characterEncoding=gbk";

		String username = "root";

		String password = "admin";

		Connection con = null;

		try {

			con = DriverManager.getConnection(url, username,password);

		} catch (SQLException se) {

			System.out.println("数据库连接失败！");

			se.printStackTrace();

		}

		return con;

    }

	public static void sqlTest(){

		Connection con = null;

		PreparedStatement pstmt = null;

		ResultSet rs = null;

		try {

			con = getConnect();

			String sql = "select * from tbl_user where id = ?;";

			if (con != null) {

				// Statement stmt = (Statement) con.createStatement() ; //静�?sql

				pstmt = (PreparedStatement) con.prepareStatement(sql);// 动�?sql

				// CallableStatement cstmt = (CallableStatement)

				// con.prepareCall("{CALL demoSp(? , ?)}") ; //存储过程

//				ResultSet rs = pstmt.executeQuery("SELECT * FROM ...") ; //查询数据库的SQL语句，返回一个结果集（ResultSet）对�?

//			    int rows = pstmt.executeUpdate("INSERT INTO ...") ;//用于执行INSERT、UPDATE�?DELETE语句以及SQL DDL语句

//			    boolean flag = pstmt.execute( sql) ; //:用于执行返回多个结果集�?多个更新计数或二者组合的 语句

				pstmt.setObject(1, "id1");

				rs = pstmt.executeQuery();

				while(rs.next()){

					String name1 = rs.getString("id") ;

			         String name = rs.getString(1) ;

			         String pass = rs.getString(2) ; // 此方法比较

			         System.out.println(name1+" "+name+" "+pass);

			    }

			}

		} catch (SQLException e) {

			e.printStackTrace();

		} finally{

			try {

				if (pstmt != null) {

					pstmt.close();

				}

				if (rs != null) {

					rs.close();

				}

				if (con != null) {

					con.close();

				}

			} catch (SQLException e) {

				e.printStackTrace();

			}

		}

	}

	public static void main(String[] args) {

		sqlTest();

	}

}

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@yqy-oracle-master-dev.caiwu.corp:1562:O11G1562";

                     //BLOB clob = (BLOB) rs.getBlob(5);

                    //FileUtils.writeStringToFile(file,result, "utf-8");

File file_clob = new File("F:\\data\\"+rs.getString(4)+"_"+rs.getString(3)+".ZIP");
char[] buff2 = new char[1024];
OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(file_clob));
Reader reader = rs.getCharacterStream(5);
for (int i = 0; (i = reader.read(buff2)) > 0;) {
writer.write(buff2, 0, i);
}
writer.flush();
writer.close();
reader.close();

　　

mysql_install_db  --defaults-file=/app/mysql/mysql/my.cnf  初始化mysql
FLUSH TABLES WITH READ LOCK;
./bin/mysqladmin -uroot -S /app/mysql/mysql_3307/mysql.sock password 123456 设置制定socket数据库root密码
 ./mysql -uroot -S /app/mysql/mysql_3307/mysql.sock  -p123456   链接制定Scoke的数据库
 
my.cnf  中
log-bin=mysql-bin   开启日志
binlog-do-db=gcoin   制定同步的数据库，不指定同步所有库
netstat -ntlp
nohup ./bin/mysqld_safe --defaults-file=/app/mysql/mysql/my.cnf   >> /dev/null  &  后台制定文件启动mysql
mysqld_multi --defaults-file=/app/mysql/mysql/my.cnf start 3306

mysqladmin -hlocalhost -uroot -pxxx -P3306 shutdown  关闭指定端口的mysql
./bin/mysqladmin  -hlocalhost -uroot -p123456 -S /app/mysql/mysql_3307/mysql.sock shutdown

./mysql -uroot -S /app/mysql/mysql/mysql.sock  -p123456
./mysql -uroot -S /app/mysql/mysql_3307/mysql.sock  -p123456  登录3307

环境：linux主机上已经yum安装了mysql，而且数据库正在运行。
在不关闭/重启mysql的情况下，重新启动一个新的mysql实例，使用3307端口，实现一台机器上同时运行两个数据库实例。
1、创建新的mysql实例数据存储目录：
mkdir -p /data/mysql_3307
2、把my.cnf配置文件复制一份，开几个端口要复制几份。
cp /etc/my.cnf /data/mysql_3307/my.cnf
3、修改/data/mysql_3307/my.cnf文件，把默认的3306端口改成 3307，根据实际情况修改socket，basedir，datadir 。
[client]
port = 3307
socket = /tmp/mysql_3307.sock
default-character-set = utf8
# The MySQL server
[mysqld]
port = 3307
user = mysql
socket = /tmp/mysql_3307.sock
basedir = /usr
datadir = /data/mysql_3307
character-set-server = utf8
log-error = /data/mysql_3307/error.log
pid-file = /data/mysql_3307/localhost.localdomain.pid
4、初始化数据库:
mysql_install_db --basedir=/usr --datadir=/data/mysql_3307 --user=mysql
mysql是yum安装的，所以basedir=/usr就可以，它会自动在/usr下找mysql的安装目录。
如果是源码包编译安装的mysql，假如安装路径是在/usr/local/mysql
cd /usr/local/mysql/scripts/
./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3307 --user=mysql
5、启动mysql，要指定.cnf文件启动
mysqld_safe --defaults-file=/data/mysql_3307/my.cnf --user=mysql &
6、停止MYSQL，需要指定对应的sock文件。
mysqladmin -uroot -S /tmp/mysql_3307.sock shutdown
7、登陆3307端口的mysql
mysql -S /tmp/mysql_3307.sock -P 3307

mysqladmin -hlocalhost -uroot -pxxx -P3306 shutdown  关闭指定端口的mysql

./bin/mysqladmin  -hlocalhost -uroot -p123456 -S /app/mysql/mysql_3307/mysql.sock shutdown 

mysql_install_db  --defaults-file=/app/mysql/mysql/my.cnf  初始化mysql

FLUSH TABLES WITH READ LOCK;

./bin/mysqladmin -uroot -S /app/mysql/mysql_3307/mysql.sock password 123456 设置制定socket数据库root密码

 ./mysql -uroot -S /app/mysql/mysql_3307/mysql.sock  -p123456   链接制定Scoke的数据库

 my.cnf  中

log-bin=mysql-bin   开启日志

binlog-do-db=gcoin   制定同步的数据库，不指定同步所有库

netstat -ntlp

nohup ./bin/mysqld_safe --defaults-file=/app/mysql/mysql/my.cnf   >> /dev/null  &  后台制定文件启动mysql

mysqld_multi --defaults-file=/app/mysql/mysql/my.cnf start 3306

1、create schema [数据库名称] default character set utf8 collate utf8_general_ci;--创建数据库

　　采用create schema和create database创建数据库的效果一样。

2、create user '[用户名称]'@'%' identified by '[用户密码]';--创建用户

　　密码8位以上，包括：大写字母、小写字母、数字、特殊字符

　　%：匹配所有主机，该地方还可以设置成‘localhost’，代表只能本地访问，例如root账户默认为‘localhost‘

3、grant select,insert,update,delete,create on [数据库名称].* to [用户名称];--用户授权数据库

　　*代表整个数据库

4、flush  privileges ;--立即启用修改

5、revoke all on *.* from tester;--取消用户所有数据库（表）的所有权限

6、delete from mysql.user where user='tester';--删除用户

7、drop database [schema名称|数据库名称];--删除数据库

grant select,insert,update,delete,create on [gcoin] to [gcoin]

create user 'salve'@'10.112.180.165' identified by '123456';

GRANT REPLICATION SLAVE ON *.* TO 'salve'@'10.112.180.165';

 SHOW MASTER STATUS;查看master状态，记录二进制文件名和位置：

 +------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000016 |   714748 | gcoin        |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

CHANGE MASTER TO

MASTER_HOST='10.112.180.165',

MASTER_USER='salve',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000027',

MASTER_LOG_POS=687;

  start slave

  show slave status\G ；//查看从库状态

  show variables like '%binlog_format%';   日志模式

  FLUSH TABLES WITH READ LOCK;  阻塞所有写操作

  UNLOCK TABLES;  解锁

  ./bin/mysqldump   --port 3306  --user root -p gcoin >./gcoin.sql   导出数据 -p 后跟的是库名，不是密码

相关文章