SELECT * FROM #TempHuDong

字符串截断函数是指:Stuff
和 SubString,字符串查找函数是:CharIndex 和 PatIndex

 

图片 1

一,SubString
截取子串

Preface

 

最常用的字符串函数,用于截取特定长度的子串。

 

SELECT * FROM #TempHuDong
SELECT TOP 1 ABS(a.num -b.num) ‘差’
FROM (select row_number()over(order by getdate()) as id,* from
#TempHuDong) as a
LEFT JOIN (select row_number()over(order by getdate()) as id,* from
#TempHuDong) as b
ON a.id = b.id – 1

SUBSTRING ( expression ,start , length )

    I’ve been demonstrated how to
implement a master-slave structure using mysqldump in my previous
blog.I’m gonna use Xtrabackup to do it again now.

图片 2

参数表达:

 

 

  • start
    参数:整数,表示开首地方;字符的序号(index)从1起来,即首先个字符的序号是1;
  • length参数:整数,表示截取字符的最大数目;假使start+Length
    大于字符串的总委员长度,那么再次回到从Start开端的全体字符;
  • 归来data
    type:若是expression是char或varchar,那么再次来到varchar;即便expression是nchar或nvarchar,那么再次来到nvarchar;

Framework

诸如:字符串 abcdef,截取从第4个字符早先,共2个字符的子串是:bc

 

select substring('abcdef',2,2)
Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm2 192.168.1.101/3306 master CentOS 7.0 5.7.21 on row
zlm3 192.168.1.102/3306 slave CentOS 7.0 5.7.21 on row

二,Stuff
删除字符串的钦定部分,并插入相应的字符,将要字符串中内定部分替换为新的字符串

 

Stuff函数从字符串钦赐的起来位置,删除钦点长度的字符串,并从该职分插入新的字符串。

Procedure

It deletes a specified
length of characters in the first string at the start position and then
inserts the second string into the first string at the start
position.

 

STUFF ( character_expression , start , length , replaceWith_expression )

Generate a
physical backup on master.

参数表明:

  1 [root@zlm2 03:59:24 ~]
  2 #innobackupex --defaults-file-/data/mysql/mysql3306/my3306.cnf /data/backup
  3 xtrabackup: recognized server arguments: 
  4 xtrabackup: recognized client arguments: 
  5 180725 04:00:42 innobackupex: Starting the backup operation
  6 
  7 IMPORTANT: Please check that the backup run completes successfully.
  8            At the end of a successful backup run innobackupex
  9            prints "completed OK!".
 10 
 11 180725 04:00:42  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.1.101;mysql_socket=/tmp/mysql3306.sock' as 'zlm'  (using password: YES).
 12 180725 04:00:42  version_check Connected to MySQL server
 13 180725 04:00:42  version_check Executing a version check against the server...
 14 180725 04:00:42  version_check Done.
 15 180725 04:00:42 Connecting to MySQL server host: 192.168.1.101, user: zlm, password: set, port: not set, socket: /tmp/mysql3306.sock
 16 Using server version 5.7.21-log
 17 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
 18 xtrabackup: uses posix_fadvise().
 19 xtrabackup: cd to /data/mysql/mysql3306/data/ //Change directory to the datadir of current instance.
 20 xtrabackup: open files limit requested 0, set to 1024
 21 xtrabackup: using the following InnoDB configuration:
 22 xtrabackup:   innodb_data_home_dir = .
 23 xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
 24 xtrabackup:   innodb_log_group_home_dir = ./
 25 xtrabackup:   innodb_log_files_in_group = 3
 26 xtrabackup:   innodb_log_file_size = 104857600
 27 InnoDB: Number of pools: 1
 28 180725 04:00:43 >> log scanned up to (10055645489)
 29 xtrabackup: Generating a list of tablespaces
 30 InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
 31 180725 04:00:43 [01] Copying ./ibdata1 to /data/backup/2018-07-25_04-00-42/ibdata1 //Copy shared tablespace file to the target of backup directory.
 32 180725 04:00:44 >> log scanned up to (10055645489) //The redo logfiles are continuously written before copy .ibd files.
 33 180725 04:00:45 >> log scanned up to (10055645489)
 34 180725 04:00:45 [01]        ...done
 35 180725 04:00:46 >> log scanned up to (10055645489)
 36 180725 04:00:47 >> log scanned up to (10055645489)
 37 180725 04:00:47 [01] Copying ./mysql/plugin.ibd to /data/backup/2018-07-25_04-00-42/mysql/plugin.ibd //begins to copy data of innodb table(.ibd).
 38 180725 04:00:47 [01]        ...done
 39 180725 04:00:47 [01] Copying ./mysql/servers.ibd to /data/backup/2018-07-25_04-00-42/mysql/servers.ibd
 40 180725 04:00:47 [01]        ...done
 41 180725 04:00:47 [01] Copying ./mysql/help_topic.ibd to /data/backup/2018-07-25_04-00-42/mysql/help_topic.ibd
 42 180725 04:00:47 [01]        ...done
 43 180725 04:00:48 >> log scanned up to (10055645489)
 44 180725 04:00:48 [01] Copying ./mysql/help_category.ibd to /data/backup/2018-07-25_04-00-42/mysql/help_category.ibd
 45 180725 04:00:48 [01]        ...done
 46 180725 04:00:48 [01] Copying ./mysql/help_relation.ibd to /data/backup/2018-07-25_04-00-42/mysql/help_relation.ibd
 47 180725 04:00:48 [01]        ...done
 48 180725 04:00:48 [01] Copying ./mysql/help_keyword.ibd to /data/backup/2018-07-25_04-00-42/mysql/help_keyword.ibd
 49 180725 04:00:48 [01]        ...done
 50 180725 04:00:48 [01] Copying ./mysql/time_zone_name.ibd to /data/backup/2018-07-25_04-00-42/mysql/time_zone_name.ibd
 51 180725 04:00:48 [01]        ...done
 52 180725 04:00:48 [01] Copying ./mysql/time_zone.ibd to /data/backup/2018-07-25_04-00-42/mysql/time_zone.ibd
 53 180725 04:00:48 [01]        ...done
 54 180725 04:00:48 [01] Copying ./mysql/time_zone_transition.ibd to /data/backup/2018-07-25_04-00-42/mysql/time_zone_transition.ibd
 55 180725 04:00:48 [01]        ...done
 56 180725 04:00:48 [01] Copying ./mysql/time_zone_transition_type.ibd to /data/backup/2018-07-25_04-00-42/mysql/time_zone_transition_type.ibd
 57 180725 04:00:48 [01]        ...done
 58 180725 04:00:48 [01] Copying ./mysql/time_zone_leap_second.ibd to /data/backup/2018-07-25_04-00-42/mysql/time_zone_leap_second.ibd
 59 180725 04:00:48 [01]        ...done
 60 180725 04:00:48 [01] Copying ./mysql/innodb_table_stats.ibd to /data/backup/2018-07-25_04-00-42/mysql/innodb_table_stats.ibd
 61 180725 04:00:48 [01]        ...done
 62 180725 04:00:48 [01] Copying ./mysql/innodb_index_stats.ibd to /data/backup/2018-07-25_04-00-42/mysql/innodb_index_stats.ibd
 63 180725 04:00:48 [01]        ...done
 64 180725 04:00:48 [01] Copying ./mysql/slave_relay_log_info.ibd to /data/backup/2018-07-25_04-00-42/mysql/slave_relay_log_info.ibd
 65 180725 04:00:48 [01]        ...done
 66 180725 04:00:48 [01] Copying ./mysql/slave_master_info.ibd to /data/backup/2018-07-25_04-00-42/mysql/slave_master_info.ibd
 67 180725 04:00:48 [01]        ...done
 68 180725 04:00:48 [01] Copying ./mysql/slave_worker_info.ibd to /data/backup/2018-07-25_04-00-42/mysql/slave_worker_info.ibd
 69 180725 04:00:48 [01]        ...done
 70 180725 04:00:48 [01] Copying ./mysql/gtid_executed.ibd to /data/backup/2018-07-25_04-00-42/mysql/gtid_executed.ibd
 71 180725 04:00:48 [01]        ...done
 72 180725 04:00:48 [01] Copying ./mysql/server_cost.ibd to /data/backup/2018-07-25_04-00-42/mysql/server_cost.ibd
 73 180725 04:00:48 [01]        ...done
 74 180725 04:00:48 [01] Copying ./mysql/engine_cost.ibd to /data/backup/2018-07-25_04-00-42/mysql/engine_cost.ibd
 75 180725 04:00:48 [01]        ...done
 76 180725 04:00:48 [01] Copying ./sys/sys_config.ibd to /data/backup/2018-07-25_04-00-42/sys/sys_config.ibd
 77 180725 04:00:48 [01]        ...done
 78 180725 04:00:48 [01] Copying ./zlm/test_innodb.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_innodb.ibd
 79 180725 04:00:48 [01]        ...done
 80 180725 04:00:48 [01] Copying ./zlm/test_ddl.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_ddl.ibd
 81 180725 04:00:48 [01]        ...done
 82 180725 04:00:48 [01] Copying ./zlm/test_ddl_no_pk.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_ddl_no_pk.ibd
 83 180725 04:00:48 [01]        ...done
 84 180725 04:00:48 [01] Copying ./zlm/test_index_usage.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_index_usage.ibd
 85 180725 04:00:48 [01]        ...done
 86 180725 04:00:49 >> log scanned up to (10055645489)
 87 180725 04:00:49 [01] Copying ./zlm/test_charset.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_charset.ibd
 88 180725 04:00:49 [01]        ...done
 89 180725 04:00:49 [01] Copying ./zlm/test_null.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_null.ibd
 90 180725 04:00:49 [01]        ...done
 91 180725 04:00:49 [01] Copying ./zlm/test_convert.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_convert.ibd
 92 180725 04:00:49 [01]        ...done
 93 180725 04:00:50 >> log scanned up to (10055645489)
 94 180725 04:00:50 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... //In order to avoid writting binlog,new version will use "NO_WRITE_TO_BINLOG" option.
 95 180725 04:00:50 Executing FLUSH TABLES WITH READ LOCK... //Generate a FTWRL.
 96 180725 04:00:50 Starting to backup non-InnoDB tables and files
 97 180725 04:00:50 [01] Copying ./mysql/db.opt to /data/backup/2018-07-25_04-00-42/mysql/db.opt
 98 180725 04:00:50 [01]        ...done
 99 180725 04:00:50 [01] Copying ./mysql/db.frm to /data/backup/2018-07-25_04-00-42/mysql/db.frm
100 180725 04:00:50 [01]        ...done
101 180725 04:00:50 [01] Copying ./mysql/db.MYI to /data/backup/2018-07-25_04-00-42/mysql/db.MYI
102 180725 04:00:50 [01]        ...done
103 180725 04:00:50 [01] Copying ./mysql/db.MYD to /data/backup/2018-07-25_04-00-42/mysql/db.MYD
104 180725 04:00:50 [01]        ...done
105 180725 04:00:50 [01] Copying ./mysql/user.frm to /data/backup/2018-07-25_04-00-42/mysql/user.frm
106 180725 04:00:50 [01]        ...done
107 180725 04:00:50 [01] Copying ./mysql/user.MYI to /data/backup/2018-07-25_04-00-42/mysql/user.MYI
108 180725 04:00:50 [01]        ...done
109 180725 04:00:50 [01] Copying ./mysql/user.MYD to /data/backup/2018-07-25_04-00-42/mysql/user.MYD
110 
111 ... //A bundle of MyISAM tables(.MYI,.MYD,.frm) and innodb table structure(.frm) copied here,omitted.
112 
113 180725 04:00:51 [01] Copying ./zlm/db.opt to /data/backup/2018-07-25_04-00-42/zlm/db.opt
114 180725 04:00:51 [01]        ...done
115 180725 04:00:51 [01] Copying ./zlm/test_innodb.frm to /data/backup/2018-07-25_04-00-42/zlm/test_innodb.frm
116 180725 04:00:51 [01]        ...done
117 180725 04:00:51 [01] Copying ./zlm/test_myisam.frm to /data/backup/2018-07-25_04-00-42/zlm/test_myisam.frm
118 180725 04:00:51 [01]        ...done
119 180725 04:00:51 [01] Copying ./zlm/test_myisam.MYI to /data/backup/2018-07-25_04-00-42/zlm/test_myisam.MYI
120 180725 04:00:51 [01]        ...done
121 180725 04:00:51 [01] Copying ./zlm/test_myisam.MYD to /data/backup/2018-07-25_04-00-42/zlm/test_myisam.MYD
122 180725 04:00:51 [01]        ...done
123 180725 04:00:51 [01] Copying ./zlm/test_ddl.frm to /data/backup/2018-07-25_04-00-42/zlm/test_ddl.frm
124 180725 04:00:51 [01]        ...done
125 180725 04:00:51 [01] Copying ./zlm/test_ddl_no_pk.frm to /data/backup/2018-07-25_04-00-42/zlm/test_ddl_no_pk.frm
126 180725 04:00:51 [01]        ...done
127 180725 04:00:51 [01] Copying ./zlm/test_index_usage.frm to /data/backup/2018-07-25_04-00-42/zlm/test_index_usage.frm
128 180725 04:00:51 [01]        ...done
129 180725 04:00:51 [01] Copying ./zlm/test_charset.frm to /data/backup/2018-07-25_04-00-42/zlm/test_charset.frm
130 180725 04:00:51 [01]        ...done
131 180725 04:00:51 [01] Copying ./zlm/test_null.frm to /data/backup/2018-07-25_04-00-42/zlm/test_null.frm
132 180725 04:00:51 [01]        ...done
133 180725 04:00:51 [01] Copying ./zlm/test_convert.frm to /data/backup/2018-07-25_04-00-42/zlm/test_convert.frm
134 180725 04:00:51 [01]        ...done
135 180725 04:00:51 Finished backing up non-InnoDB tables and files
136 180725 04:00:51 [00] Writing /data/backup/2018-07-25_04-00-42/xtrabackup_binlog_info
137 180725 04:00:51 [00]        ...done
138 180725 04:00:51 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... //Flushes redo logfiles whith writting them into binlog.
139 xtrabackup: The latest check point (for incremental): '10055645480' //Prints the last checkpoint.
140 xtrabackup: Stopping log copying thread.
141 .180725 04:00:51 >> log scanned up to (10055645489)
142 
143 180725 04:00:51 Executing UNLOCK TABLES
144 180725 04:00:51 All tables unlocked
145 180725 04:00:51 [00] Copying ib_buffer_pool to /data/backup/2018-07-25_04-00-42/ib_buffer_pool
146 180725 04:00:51 [00]        ...done
147 180725 04:00:51 Backup created in directory '/data/backup/2018-07-25_04-00-42/'
148 MySQL binlog position: filename 'mysql-bin.000027', position '190', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021'
149 180725 04:00:51 [00] Writing /data/backup/2018-07-25_04-00-42/backup-my.cnf
150 180725 04:00:51 [00]        ...done
151 180725 04:00:51 [00] Writing /data/backup/2018-07-25_04-00-42/xtrabackup_info 
152 180725 04:00:51 [00]        ...done
153 xtrabackup: Transaction log of lsn (10055645480) to (10055645489) was copied. //Prints the backuped lsn information.
154 180725 04:00:51 completed OK!
  • start:整数,表示删除和插入的最早地方;倘使start是0,或抢先字符串的总市长度,那么该函数重临NULL;
  • length:整数,表示删除字符的最大数据;借使Length+Start大于字符串的总院长度,表示删除从Start最早的有所字符;
  • replaceWith_expression
    :字符类型,表示从开端地方(start)插入的字符串;

 

举个例子:从分裂岗位截断字符串abcdef,查看再次回到结果

**Check the outputfile of
Xtrabackup.**

declare @str varchar(6)
set @str='abcdef'

select  stuff(@str,7,1,''),
        stuff(@str,0,1,''),
        stuff(@str,3,7,''),
        stuff(@str,3,7,'12345')
 1 [root@zlm2 04:00:51 /data/backup]
 2 #cd /data/backup/
 3 
 4 [root@zlm2 04:03:53 /data/backup]
 5 #ls -l
 6 total 4
 7 drwxr-x--- 6 root root 4096 Jul 25 04:00 2018-07-25_04-00-42
 8 
 9 [root@zlm2 04:03:57 /data/backup]
10 #cd 2018-07-25_04-00-42/
11 
12 [root@zlm2 04:04:04 /data/backup/2018-07-25_04-00-42]
13 #ls -l
14 total 102456
15 -rw-r----- 1 root root       489 Jul 25 04:00 backup-my.cnf
16 -rw-r----- 1 root root      1078 Jul 25 04:00 ib_buffer_pool
17 -rw-r----- 1 root root 104857600 Jul 25 04:00 ibdata1
18 drwxr-x--- 2 root root      4096 Jul 25 04:00 mysql
19 drwxr-x--- 2 root root      8192 Jul 25 04:00 performance_schema
20 drwxr-x--- 2 root root      8192 Jul 25 04:00 sys
21 -rw-r----- 1 root root        68 Jul 25 04:00 xtrabackup_binlog_info
22 -rw-r----- 1 root root       121 Jul 25 04:00 xtrabackup_checkpoints
23 -rw-r----- 1 root root       570 Jul 25 04:00 xtrabackup_info
24 -rw-r----- 1 root root      2560 Jul 25 04:00 xtrabackup_logfile
25 drwxr-x--- 2 root root      4096 Jul 25 04:00 zlm
26 
27 [root@zlm2 04:04:07 /data/backup/2018-07-25_04-00-42]
28 #cat xtrabackup_binlog_info
29 mysql-bin.000027    190    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021
30 
31 [root@zlm2 04:04:22 /data/backup/2018-07-25_04-00-42]
32 #cat xtrabackup_checkpoints
33 backup_type = full-backuped
34 from_lsn = 0
35 to_lsn = 10055645480
36 last_lsn = 10055645489
37 compact = 0
38 recover_binlog_info = 0
39 
40 [root@zlm2 04:04:27 /data/backup/2018-07-25_04-00-42]
41 #cat xtrabackup_info
42 uuid = 8e8d59a6-8fae-11e8-87d6-080027de0e0e
43 name = 
44 tool_name = innobackupex
45 tool_command = --defaults-file-/data/mysql/mysql3306/my3306.cnf /data/backup
46 tool_version = 2.4.12
47 ibbackup_version = 2.4.12
48 server_version = 5.7.21-log
49 start_time = 2018-07-25 04:00:42
50 end_time = 2018-07-25 04:00:51
51 lock_time = 0
52 binlog_pos = filename 'mysql-bin.000027', position '190', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021'
53 innodb_from_lsn = 0
54 innodb_to_lsn = 10055645480
55 partial = N
56 incremental = N
57 format = file
58 compact = N
59 compressed = N
60 encrypted = N

图片 3

 

应用stuff函数,必得小心两点:

**Package
the backup set and transfer it to slave server.**

  1. 即使Length+Start大于字符串的总院长度,删除从Start起先的有所字符;
  2. 假若Start是0,或高于字符串的总省长度,重临Null;
 1 [root@zlm2 04:06:06 /data/backup]
 2 #ls -l
 3 total 4
 4 drwxr-x--- 6 root root 4096 Jul 25 04:00 2018-07-25_04-00-42
 5 
 6 [root@zlm2 04:06:10 /data/backup]
 7 #tar cf Xtrabk_full.tar 2018-07-25_04-00-42/
 8 
 9 [root@zlm2 04:06:27 /data/backup]
10 #ls -l
11 total 140856
12 drwxr-x--- 6 root root      4096 Jul 25 04:00 2018-07-25_04-00-42
13 -rw-r--r-- 1 root root 144230400 Jul 25 04:06 Xtrabk_full.tar
14 
15 [root@zlm2 04:06:28 /data/backup]
16 #scp Xtrabk_full.tar zlm3:/data/backup
17 root@zlm3's password: 
18 Xtrabk_full.tar                           

三,CharIndex 从字符串中追寻字符

 

从字符串search中检索另三个字符串find,倘若find存在于search中,重回find在search中率先次相配的开端地方;假若find官样文章于search中,重临0;

**Unpack
the backup set.**

CHARINDEX ( find ,search [ , start ] )
 1 [root@zlm3 04:07:33 ~]
 2 #cd /data/backup
 3 
 4 [root@zlm3 04:07:40 /data/backup]
 5 #ls -l
 6 total 140852
 7 -rw-r--r-- 1 root root 144230400 Jul 25 04:06 Xtrabk_full.tar
 8 
 9 [root@zlm3 04:07:41 /data/backup]
10 #tar xf Xtrabk_full.tar 
11 
12 [root@zlm3 04:07:49 /data/backup]
13 #ls -l
14 total 140856
15 drwxr-x--- 6 root root      4096 Jul 25 04:00 2018-07-25_04-00-42
16 -rw-r--r-- 1 root root 144230400 Jul 25 04:06 Xtrabk_full.tar
17 
18 [root@zlm3 04:07:51 /data/backup]
19 #chown -R mysql.mysql 2018-07-25_04-00-42/
20 
21 [root@zlm3 04:08:16 /data/backup]
22 #ls -l
23 total 140856
24 drwxr-x--- 6 mysql mysql      4096 Jul 25 04:00 2018-07-25_04-00-42
25 -rw-r--r-- 1 root  root  144230400 Jul 25 04:06 Xtrabk_full.tar

发表评论

电子邮件地址不会被公开。 必填项已用*标注