标签 存储过程 下的文章

MySQL使用存储过程插入100万条记录

业务上为了测试DB写入性能,需测试大量数据写入速度,数据库部署在aws上,4核8G内存100G硬盘,MySQL5.7.27
mysql.png

--创建测试数据库
create database a1 default charset utf8mb4;
--创建测试表
CREATE TABLE `test110` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `comp` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `aid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--创建插入存储过程
DELIMITER $$
CREATE PROCEDURE test_insert()
BEGIN
declare i int;
set i=0;
while i<1000000 do
INSERT INTO `test110` VALUES (null,'TMP01','user11','会员中台','2019-08-15 00:00:02','11','22');
set i=i+1;
end while;
end $$
DELIMITER ;

测试如下,速度很快:

mysql> call test_insert();
Query OK, 1 row affected (44.71 sec)

mysql> call test_insert();
Query OK, 1 row affected (44.74 sec)

mysql> call test_insert();
Query OK, 1 row affected (44.71 sec)

mysql> select count(1) from test110;
+----------+
| count(1) |
+----------+
|  3000000 |
+----------+
1 row in set (0.44 sec)

MySQL配置如下my.cnf

[client]
default-character-set = utf8mb4
port        = 3306
socket        = /data/mysql/mysql.sock

[mysql]
default-character-set = utf8mb4
prompt="\u@oatestdb \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]

secure_file_priv = ''
#validate-password = off
slow_query_log = On
log_output = FILE
slow-query-log-file=/data/mysql/slow.log
long_query_time = 30 
log_queries_not_using_indexes = ON

lower_case_table_names=1
auto-increment-increment = 1
auto-increment-offset = 1

log_warnings=1
log_error_verbosity=1

default-storage-engine=INNODB
group_concat_max_len = 18446744073709551615
# generic configuration options
port        = 3306
socket        = /data/mysql/mysql.sock 
pid-file        = /data/mysql/mysqld.pid
basedir        = /usr/local/mysql
datadir         = /data/mysql/db
language    = /usr/local/mysql/share/english 
user        = mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip-external-locking
skip-name-resolve
skip-character-set-client-handshake
character-set-server = utf8mb4
explicit_defaults_for_timestamp = true
init_connect='SET NAMES utf8mb4'
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
explicit_defaults_for_timestamp

#replication
expire_logs_days = 30
sync-binlog = 1000
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=/data/mysql/binlog/binlog
binlog_format=ROW


relay_log_recovery = 1
skip_slave_start = 0
slave-skip-errors = all
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G

#group replication
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="e4668cea-d7ca-11e6-86b5-18a99b76310d"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "db1:24901"
loose-group_replication_group_seeds= "db1:24901,db2:24902,db3:24903"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE

skip_name_resolve = 1
open_files_limit    = 65535
back_log = 1024
max_connections = 5000
max_connect_errors = 100000
max_prepared_stmt_count=1048576
table_open_cache = 10240
table_definition_cache = 10240
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 7680
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 900
wait_timeout = 900
tmp_table_size = 256M
max_heap_table_size = 256M

# innodb
innodb_buffer_pool_size = 12G 
innodb_data_file_path = IBdata1:1024M;IBdata2:1024M:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DSYNC

innodb_io_capacity = 50000
innodb_io_capacity_max = 80000
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0

[mysqld_safe]
open-files-limit = 8192
log-error=/data/mysql/error.log
pid-file=/data/mysql/mysqld.pid

[mysqldump]
quick
max_allowed_packet = 32M

MySQL创建函数-存储过程报“ERROR 1418 ”错误 解决方法

MySQL创建函数或存储过程的时候报error 1418错误:

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

是因为log_bin_trust_function_creators参数在起作用:
当二进制日志启用后,这个变量就会启用。它控制是否可以信任存储函数创建者,不会创建写入二进制日志引起不安全事件的存储函数。
如果设置为0(默认值),用户不得创建或修改存储函数,除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1,MySQL不会对创建存储函数实施这些限制,此变量也适用于触发器的创建。

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>  show variables like '%log_bin_trust_function_creators%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

如果数据库没有使用主从复制,那么就可以将参数log_bin_trust_function_creators设置为1。

mysql> set global log_bin_trust_function_creators=1;

这个动态设置的方式会在服务重启后失效,所以我们还必须在my.cnf中设置,加上

log_bin_trust_function_creators=1

这样就会永久生效。
注意:如果开启了主从复制,同时又打开了log_bin_trust_function_creators参数,可以创建函数、存储过程,可能会引起主从复制故障·

MySQL存储过程创建、查看、调用及参数、存储过程权限介绍

存储过程(Stored Procedure):
一组可编程的函数,是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

优点(为什么要用存储过程?):
  将重复性很高的一些操作,封装到一个存储过程中,简化了对这些SQL的调用
  批量处理:SQL+循环,减少流量,也就是“跑批”
  统一接口,确保数据的安全

相对于oracle数据库来说,MySQL的存储过程相对功能较弱,使用较少。
一、存储过程的创建和调用
  >存储过程就是具有名字的一段代码,用来完成一个特定的功能。
  >创建的存储过程保存在数据库的数据字典中。
创建存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }]
 PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
  Valid SQL routine statement

[begin_label:] BEGIN
  [statement_list]
    ……
END [end_label]

存储过程权限相关:

CREATE ROUTINE : 创建存储过程的权限 
ALTER ROUTINE : 修改存储过程的权限 
EXECUTE :执行存储过程的权限
GRANT SELECT ON `mysql`.`proc` TO 'bis_enrolment_u'@'10.200.%' identified by 'xxxxx'; ; 普通账号没有查看存储过程或函数的权限,增加后可以看到系统有多少个函数或存储过程·









---阅读剩余部分---

Oracle查询所有序列/触发器、存储过程、视图、表

方法一:
Select object_name From user_objects Where object_type='TRIGGER'; --所有触发器

Select object_name From user_objects Where object_type='PROCEDURE'; --所有存储过程

Select object_name From user_objects Where object_type='VIEW'; --所有视图

Select object_name From user_objects Where object_type='TABLE'; --所有表

SELECT text FROM ALL_SOURCE where TYPE='TRIGGER' AND NAME ='TRIG_LOGOFF_AUDIT'; --查询TRIG_LOGOFF_AUDIT触发器内容


---阅读剩余部分---

Oracle查询所有用户表、视图、存储函数、过程

视图(view),也称虚表, 不占用物理空间,这个也是相对概念,因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候, 只是重新执行SQL.
视图是从一个或多个实际表中获得的,这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
视图的定义存在数据库中,与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。
视图看上去非常象数据库的物理表,对它的操作同任何其它的表一样。当通过视图修改数据时,实际上是在改变基表中的数据;相反地,基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因,有些Oracle视图可以修改对应的基表,有些则不能(仅仅能查询)。
select * from user_views;

查询用户下的所有表

select  table_name from all_tables where owner = upper('TANAME');




---阅读剩余部分---

最新

分类

归档

评论

其它