首页 文章 精选 留言 我的

精选列表

搜索[智能检索],共10005篇文章
优秀的个人博客,低调大师

re检索和替换sub

re.sub用于替换字符串中的匹配项。 语法: re.sub(pattern, repl, string, count=0) 参数: pattern : 正则中的模式字符串。 repl : 替换的字符串,也可为一个函数。 string : 要被查找替换的原始字符串。 count : 模式匹配后替换的最大次数,默认 0 表示替换所有的匹配。 实例 #!/usr/bin/python3 import re phone = " 2004-959-559 # 这是一个电话号码 " # 删除注释 num = re . sub ( r ' #.*$ ' , " " , phone ) print ( " 电话号码 : " , num ) # 移除非数字的内容 num = re . sub ( r ' \D ' , " " , phone ) print ( " 电话号码 : " , num ) 以上实例执行结果如下: 电话号码 : 2004-959-559 电话号码 : 2004959559 repl 参数是一个函数 以下实例中将字符串中的匹配的数字乘于 2: 实例 #!/usr/bin/python import re # 将匹配的数字乘于 2 def double ( matched ) : value = int ( matched . group ( ' value ' ) ) return str ( value * 2 ) s = ' A23G4HFD567 ' print ( re . sub ( ' (?P<value> \d +) ' , double , s ) ) 执行输出结果为: A46G8HFD1134 from:http://www.runoob.com/python3/python3-reg-expressions.html

优秀的个人博客,低调大师

cassandra的全文检索插件

https://github.com/Stratio/cassandra-lucene-index Stratio’s Cassandra Lucene Index Stratio’s Cassandra Lucene Index, derived fromStratio Cassandra, is a plugin forApache Cassandrathat extends its index functionality to provide near real time search such as ElasticSearch or Solr, includingfull text searchcapabilities and free multivariable, geospatial and bitemporal search. It is achieved through anApache Lucenebased implementation of Cassandra secondary indexes, where each node of the cluster indexes its own data. Stratio’s Cassandra indexes are one of the core modules on whichStratio’s BigData platformis based. Indexrelevance searchesallow you to retrieve thenmore relevant results satisfying a search. The coordinator node sends the search to each node in the cluster, each node returns itsnbest results and then the coordinator combines these partial results and gives you thenbest of them, avoiding full scan. You can also base the sorting in a combination of fields. Any cell in the tables can be indexed, including those in the primary key as well as collections. Wide rows are also supported. You can scan token/key ranges, apply additional CQL3 clauses and page on the filtered results. Index filtered searches are a powerful help when analyzing the data stored in Cassandra withMapReduceframeworks asApache Hadoopor, even better,Apache Spark. Adding Lucene filters in the jobs input can dramatically reduce the amount of data to be processed, avoiding full scan. The following benchmark result can give you an idea about the expected performance when combining Lucene indexes with Spark. We do successive queries requesting from the 1% to 100% of the stored data. We can see a high performance for the index for the queries requesting strongly filtered data. However, the performance decays in less restrictive queries. As the number of records returned by the query increases, we reach a point where the index becomes slower than the full scan. So, the decision to use indexes in your Spark jobs depends on the query selectivity. The trade-off between both approaches depends on the particular use case. Generally, combining Lucene indexes with Spark is recommended for jobs retrieving no more than the 25% of the stored data. This project is not intended to replace Apache Cassandra denormalized tables, inverted indexes, and/or secondary indexes. It is just a tool to perform some kind of queries which are really hard to be addressed using Apache Cassandra out of the box features, filling the gap between real-time and analytics. More detailed information is available atStratio’s Cassandra Lucene Index documentation. Features Lucene search technology integration into Cassandra provides: Stratio’s Cassandra Lucene Index and its integration with Lucene search technology provides: Full text search (language-aware analysis, wildcard, fuzzy, regexp) Boolean search (and, or, not) Sorting by relevance, column value, and distance Geospatial indexing (points, lines, polygons and their multiparts) Geospatial transformations (bounding box, buffer, centroid, convex hull, union, difference, intersection) Geospatial operations (intersects, contains, is within) Bitemporal search (valid and transaction time durations) CQL complex types (list, set, map, tuple and UDT) CQL user defined functions (UDF) CQL paging, even with sorted searches Columns with TTL Third-party CQL-based drivers compatibility Spark and Hadoop compatibility Not yet supported: Thrift API Legacy compact storage option Indexingcountercolumns Indexing static columns Other partitioners than Murmur3 Requirements Cassandra (identified by the three first numbers of the plugin version) Java >= 1.8 (OpenJDK and Sun have been tested) Maven >= 3.0 本文转自张昺华-sky博客园博客,原文链接:http://www.cnblogs.com/bonelee/p/6757830.html,如需转载请自行联系原作者

优秀的个人博客,低调大师

sql优化-检索及定位

一、执行频率早知道 >show status; 使用showstatus了解各种sql的执行频率 可搭配参数global全局查看,或者session当前连接开始计数默认不加参数是指查看当前连接 showstatus; +-----------------------------------------------+----------------+ |Variable_name|Value| +-----------------------------------------------+----------------+ |Aborted_clients|65| |Aborted_connects|589| |Binlog_cache_disk_use|0| |Binlog_cache_use|0| |Binlog_stmt_cache_disk_use|0| |Binlog_stmt_cache_use|0| |Bytes_received|1649| |Bytes_sent|11767| |Com_admin_commands|0| |Com_assign_to_keycache|0| |Com_alter_db|0| |Com_alter_db_upgrade|0| ... >常见的统计参数 Com_select:执行select操作的次数,一次只累加1 Com_insert:执行insert操作的次数,一次也只累加1 Com_update:执行update操作的次数 Com_delete:执行delete的次数 Innodb_rows_readselect查询的返回的行数 Innodb_rows_inserted执行INSERT操作插入的行数 Innodb_rows_updated执行update操作更新的行数 Innodb_rows_deleted执行deleted操作删除的行数 Connections:试图连接MYSQL服务器的次数。 Uptime服务器工作时间。 Slow_queries慢查询的次数。 >查看慢查询次数 mysql>showglobalstatuslike'Slow_queries'; +---------------+-------+ |Variable_name|Value| +---------------+-------+ |Slow_queries|4| +---------------+-------+ 1rowinset(0.00sec) 二、效率较低早查询 >开启慢查询日志 MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间通过long_query_time值来设定,超过这个值则会被记录到慢查询日志中。long_query_time的默认值为10,默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。 1>查看是否开启忙日志查询 showvariableslike'%slow_query_log%'; 2>开启满日志查询 1.配置文件修改 log-slow-queries=/usr/local/mysql/var/slowquery.log long_query_time=3#单位是秒 log-queries-not-using-indexes 2.使用sql语句修改 setgloballog_slow_queries=ON; setglobalslow_query_log=ON; setgloballong_query_time=0.01#单位是秒 3>慢日志格式 select*fromauction1.userwhereuser_name='liaoxz'; 执行的慢查询语句 #Time:1704274:41:48 日志记录的时间 #User@Host:root[root]@localhost[] 服务器用户/主机名 #Query_time:10.007816Lock_time:0.000000Rows_sent:1Rows_examined:0 总的查询时间、锁定时间、"发送"或者返回的行数、查询过程中所检查的行数 三、分析语句早实行 >show profiles profiling在mysql中默认是关闭的,在数据库会话级别可以打开,开启之后会收集在执行语句时候所使用的资源 在会话打开profilinges setprofiling=1; >使用show profiles查看执行的语句的持续时间和ID MariaDB[auction1]>showprofiles; +----------+------------+------------------------------------------------------------------------------------------------------------------+ |Query_ID|Duration|Query| +----------+------------+------------------------------------------------------------------------------------------------------------------+ |6|0.00038804|select*fromorderslimit1| |7|0.00019379|select*fromorderslimit1| |8|0.00028797|select*fromuserlimit1| |9|0.00006671|select*fromordersrightuserwhereorders.ID=user.id| |10|0.00336970|selectorders*.user.idfromwhereorders.ID=user.id| |11|0.00005291|selectorders*,user.idfromwhereorders.ID=user.id| |12|0.00004725|selectorders*,user.idfromorders,userwhereorders.ID=user.id| |13|0.00003863|selectorders*,user.idfromorders,userwhereorders.ID=user.id| |14|0.00024284|select*fromorderslimit1| |15|0.00027788|select*fromorderslimit10| |16|0.00007312|select*fromuserwhereuser='liaoxz'; select*fromuserwhereuser='liaoxz'| |17|0.00031860|select*fromuserlimit1| |18|0.02771239|select*fromuserwhereuser_name='liaoxz'| |19|0.03121868|select*fromuserwhereuser_name='liaoxz'| |20|0.02219678|select*fromuserwhereuser_name='liaoxz'| +----------+------------+------------------------------------------------------------------------------------------------------------------+ 15rowsinset(0.00sec) >通过指定ID查询执行时的持续时间 MariaDB[auction1]>showprofileforquery20; +----------------------+----------+ |Status|Duration| +----------------------+----------+ |starting|0.000038| |checkingpermissions|0.000005| |Openingtables|0.000010| |Afteropeningtables|0.000004| |Systemlock|0.000002| |Tablelock|0.000002| |Aftertablelock|0.000004| |init|0.000035| |optimizing|0.000011| |statistics|0.000013| |preparing|0.000008| |executing|0.000002| |Sendingdata|0.021981| |end|0.000012| |queryend|0.000004| |closingtables|0.000007| |freeingitems|0.000006| |updatingstatus|0.000019| |loggingslowquery|0.000032| |cleaningup|0.000002| +----------------------+----------+ 20rowsinset(0.00sec) >具体资源耗费查询(all,cpu,block io,context switch,page faults) MariaDB[auction1_1]>showprofilecpuforquery20; +----------------------+----------+----------+------------+ |Status|Duration|CPU_user|CPU_system| +----------------------+----------+----------+------------+ |starting|0.000038|0.000000|0.000000| |checkingpermissions|0.000005|0.000000|0.000000| |Openingtables|0.000010|0.000000|0.000000| |Afteropeningtables|0.000004|0.000000|0.000000| |Systemlock|0.000002|0.000000|0.000000| |Tablelock|0.000002|0.000000|0.000000| |Aftertablelock|0.000004|0.000000|0.000000| |init|0.000035|0.000000|0.000000| |optimizing|0.000011|0.000000|0.000000| |statistics|0.000013|0.000000|0.000000| |preparing|0.000008|0.000000|0.000000| |executing|0.000002|0.000000|0.000000| |Sendingdata|0.021981|0.026852|0.000000| |end|0.000012|0.000000|0.000000| |queryend|0.000004|0.000000|0.000000| |closingtables|0.000007|0.000000|0.000000| |freeingitems|0.000006|0.000000|0.000000| |updatingstatus|0.000019|0.000000|0.000000| |loggingslowquery|0.000032|0.000000|0.000000| |cleaningup|0.000002|0.000000|0.000000| +----------------------+----------+----------+------------+ 20rowsinset(0.00sec) 四、常见show命令大全 >sql show大全 >1. show tables/show tables from database_name; -- 显示当前数据库中所有表的名称。 >2. show databases; -- 显示mysql中所有数据库的名称。 >3. show columns from table_name from database_name; 或show columns from database_name.table_name; -- 显示表中列名称。 >4. show grants for user_name; -- 显示一个用户的权限,显示结果类似于grant 命令。 >5. show index from table_name; -- 显示表的索引。 >6. show variables; -- 显示系统变量的名称和值。 >7. show processlist; -- 显示系统中正在运行的所有进程,也就是当前正在执行的查询。大多数用户可以查看他们自己的进程,但是如果他们拥有process权限,就可以查看所有人的进程,包括密码。 >8. show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。 >9. show privileges; -- 显示服务器所支持的不同权限。 >10. show create database database_name; -- 显示create database 语句是否能够创建指定的数据库。 >11. show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。 >12. show engines; -- 显示安装以后可用的存储引擎和默认引擎。 >13. show innodb status; -- 显示innoDB存储引擎的状态。 >14. show logs; -- 显示BDB存储引擎的日志。 >15. show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。 >16. show errors; -- 只显示最后一个执行语句所产生的错误。 >17. show [storage] engines; --显示安装后的可用存储引擎和默认引擎。 五、mysql配置文件来一套 >配置文件/etc/my.cnf [client] port=3306 socket=/tmp/mysql.sock default-character-set=utf8 [mysqld] default-time-zone='+8:00' server-id=9527 user=mysql #bin-log日志相关优化参数 log-bin=mysql-master-bin #混合模式复制 binlog_format=mixed #存储二进制缓存 binlog_cache_size=16M #bin-log日志保存多长时间 expire_logs_days=7 #最大连接数 max_connections=1000 #事务提交或事务外的指令把日志写入(flush)硬盘 innodb_flush_log_at_trx_commit=1 #文件系统自动识别缓存大小是否写入磁盘 sync_binlog=0 datadir=/data/mysqldb port=3306 socket=/tmp/mysql.sock #table_cache=5000 #缓存大小设置 query_cache_size=256M #单个查询使用多大缓冲区 query_cache_limit=16M #内存表大小 max_heap_table_size=128M #线程池大小 thread_cache_size=128 #慢查询超过多长时间写入日志 long_query_time=3 #临时表大小 tmp_table_size=128M #设置索引缓冲区大小 key_buffer_size=256M #数据包大小接受设置 max_allowed_packet=64M #索引扫描,范围索引扫描,无索引全表扫描的表连接缓存的大小 join_buffer_size=16M #排序会话的缓存大小 sort_buffer_size=16M #顺序读表缓存 read_buffer_size=16M #随机读缓冲区大小 read_rnd_buffer_size=64M #缓存innodb表的索引,数据,插入数据时的缓冲 innodb_buffer_pool_size=6G sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #timestamp类型的列明确的注明default值 explicit_defaults_for_timestamp=true #禁止域名解析 skip-name-resolve #数据库是否支持符号链接存储,1为允许 symbolic-links=0 #数据库级的字符集,对之前数据不产生影响 character-set-server=utf8mb4 #大小写铭感 lower_case_table_names=1 服务器关闭非交互连接之前等待活动的秒数 wait_timeout=180 #闭一个交互的连接之前所要等待的秒数 interactive_timeout=180

优秀的个人博客,低调大师

elasticsearch 拼音检索能力研究

gitchennan/elasticsearch-analysis-lc-pinyin 配置参数少,功能满足需求。 对应版本 elasticsearch2.3.2 对应 elasticsearch-analysis-lc-pinyin 分支 2.4.2.1 或者 tag 2.2.2.1 创建一个类型 elasticsearch-analysis-lc-pinyin 的 README 是根据 elasticsearch5.0 编写的,给出的创建一个类型的语法如下 curl -XPOST http://localhost:9200/index/_mapping/brand -d' { "brand": { "properties": { "name": { "type": "text", "analyzer": "lc_index", "search_analyzer": "lc_search", "term_vector": "with_positions_offsets" } } } }' type=text 是 elasticsearch5.0 之后的类型,所以无法创建成功,稍作修改 type=text,使用如下语法创建一个类型 curl -XPOST http://localhost:9200/index/_mapping/brand -d' { "brand": { "properties": { "name": { "type": "string", "analyzer": "lc_index", "search_analyzer": "lc_search", "term_vector": "with_positions_offsets" } } } }' index 索引结构如下 { "index": { "aliases": {}, "mappings": { "brand": { "properties": { "name": { "type": "string", "term_vector": "with_positions_offsets", "analyzer": "lc_index", "search_analyzer": "lc_search" } } } }, "settings": { "index": { "creation_date": "1490152096129", "number_of_shards": "5", "number_of_replicas": "1", "uuid": "Lp1sSHGhQZyZ57LKO5KwRQ", "version": { "created": "2030299" } } }, "warmers": {} } } 存入几条数据 curl -XPOST http://localhost:9200/index/brand/1 -d'{"name":"百度"}' curl -XPOST http://localhost:9200/index/brand/8 -d'{"name":"百度糯米"}' curl -XPOST http://localhost:9200/index/brand/2 -d'{"name":"阿里巴巴"}' curl -XPOST http://localhost:9200/index/brand/3 -d'{"name":"腾讯科技"}' curl -XPOST http://localhost:9200/index/brand/4 -d'{"name":"网易游戏"}' curl -XPOST http://localhost:9200/index/brand/9 -d'{"name":"大众点评"}' curl -XPOST http://localhost:9200/index/brand/10 -d'{"name":"携程旅行网"}' 查出目前的所有数据 http://localhost:9200/index/_search { "took": 70, "timed_out": false, "_shards": { "total": 5, "successful": 5, "failed": 0 }, "hits": { "total": 7, "max_score": 1, "hits": [ { "_index": "index", "_type": "brand", "_id": "8", "_score": 1, "_source": { "name": "百度糯米" } }, { "_index": "index", "_type": "brand", "_id": "9", "_score": 1, "_source": { "name": "大众点评" } }, { "_index": "index", "_type": "brand", "_id": "10", "_score": 1, "_source": { "name": "携程旅行网" } }, { "_index": "index", "_type": "brand", "_id": "2", "_score": 1, "_source": { "name": "阿里巴巴" } }, { "_index": "index", "_type": "brand", "_id": "4", "_score": 1, "_source": { "name": "网易游戏" } }, { "_index": "index", "_type": "brand", "_id": "1", "_score": 1, "_source": { "name": "百度" } }, { "_index": "index", "_type": "brand", "_id": "3", "_score": 1, "_source": { "name": "腾讯科技" } } ] } } 插件自带分词器 lc_index 原文:lc_index : 该分词器用于索引数据时指定,将中文转换为全拼和首字,同时保留中文 分词器分词效果 curl -X POST -d '{ "analyzer" : "lc_index", "text" : ["刘德华"] }' "http://localhost:9200/lc/_analyze" { "tokens": [ { "token": "刘", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "liu", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "l", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "德", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "de", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "d", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "华", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 }, { "token": "hua", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 }, { "token": "h", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 } ] } 插件自带分词器 lc_search 原文:lc_search: 该分词器用于拼音搜索时指定,按最小拼音分词个数拆分拼音,优先拆分全拼 curl -X POST -d '{ "analyzer" : "lc_search", "text" : ["刘德华"] }' "http://localhost:9200/index/_analyze" { "tokens": [ { "token": "刘", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "德", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "华", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 } ] } 拼音全拼 搜索 baidu,结果正确 curl -X POST -d '{ "query": { "match": { "name": { "query": "baidu", "analyzer": "lc_search", "type": "phrase" } } }, "highlight" : { "pre_tags" : ["<tag1>"], "post_tags" : ["</tag1>"], "fields" : { "name" : {} } } }' "http://localhost:9200/index/brand/_search" { "took": 4, "timed_out": false, "_shards": { "total": 5, "successful": 5, "failed": 0 }, "hits": { "total": 2, "max_score": 1.4054651, "hits": [ { "_index": "index", "_type": "brand", "_id": "8", "_score": 1.4054651, "_source": { "name": "百度糯米" }, "highlight": { "name": [ "<tag1>百度</tag1>糯米" ] } }, { "_index": "index", "_type": "brand", "_id": "1", "_score": 0.38356602, "_source": { "name": "百度" }, "highlight": { "name": [ "<tag1>百度</tag1>" ] } } ] } } 单字拼音全拼与中文混合 搜索 xie程lu行,结果正确 { "took": 11, "timed_out": false, "_shards": { "total": 5, "successful": 5, "failed": 0 }, "hits": { "total": 1, "max_score": 2.459564, "hits": [ { "_index": "index", "_type": "brand", "_id": "10", "_score": 2.459564, "_source": { "name": "携程旅行网" }, "highlight": { "name": [ "<tag1>携程旅行</tag1>网" ] } } ] } } 单字拼音首字母与中文混合 搜索 携cl行,结果正确 curl -X POST -d '{ "query": { "match": { "name": { "query": "携cl行", "analyzer": "lc_search", "type": "phrase" } } }, "highlight" : { "pre_tags" : ["<tag1>"], "post_tags" : ["</tag1>"], "fields" : { "name" : {} } } }' "http://localhost:9200/index/brand/_search" { "took": 6, "timed_out": false, "_shards": { "total": 5, "successful": 5, "failed": 0 }, "hits": { "total": 1, "max_score": 2.459564, "hits": [ { "_index": "index", "_type": "brand", "_id": "10", "_score": 2.459564, "_source": { "name": "携程旅行网" }, "highlight": { "name": [ "<tag1>携程旅行</tag1>网" ] } } ] } } 拼音首字母 搜索 albb,结果正确 curl -X POST -d '{ "query": { "match": { "name": { "query": "albb", "analyzer": "lc_search", "type": "phrase" } } }, "highlight" : { "pre_tags" : ["<tag1>"], "post_tags" : ["</tag1>"], "fields" : { "name" : {} } } }' "http://localhost:9200/index/brand/_search" { "took": 4, "timed_out": false, "_shards": { "total": 5, "successful": 5, "failed": 0 }, "hits": { "total": 1, "max_score": 2.828427, "hits": [ { "_index": "index", "_type": "brand", "_id": "2", "_score": 2.828427, "_source": { "name": "阿里巴巴" }, "highlight": { "name": [ "<tag1>阿里巴巴</tag1>" ] } } ] } } 结论 elasticsearch-analysis-lc-pinyin 按照全拼、首字母,拼音中文混合搜索 elasticsearch-analysis-pinyin v1.7.2 github 项目 elasticsearch-analysis-pinyin v1.7.2 完全是为 elasticsearch 2.3.2 服务 first_letter 改变 first_letter=prefix padding_char=" " curl -X POST -d '{ "mappings": { "folk": { "properties": { "text": { "type": "string", "analyzer": "pinyin_analyzer" } } } }, "settings": { "index" : { "analysis" : { "analyzer" : { "pinyin_analyzer" : { "tokenizer" : "my_pinyin", "filter" : ["word_delimiter"] } }, "tokenizer" : { "my_pinyin" : { "type" : "pinyin", "first_letter" : "prefix", "padding_char" : " " } } } } } }' "http://localhost:9200/medcl" 拼音效果如下 curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl/_analyze" { "tokens": [ { "token": "ldh", "start_offset": 0, "end_offset": 3, "type": "word", "position": 0 }, { "token": "liu", "start_offset": 0, "end_offset": 3, "type": "word", "position": 1 }, { "token": "de", "start_offset": 0, "end_offset": 3, "type": "word", "position": 2 }, { "token": "hua", "start_offset": 0, "end_offset": 3, "type": "word", "position": 3 } ] } first_letter=append padding_char=" " curl -X POST -d '{ "mappings": { "folk": { "properties": { "text": { "type": "string", "analyzer": "pinyin_analyzer" } } } }, "settings": { "index" : { "analysis" : { "analyzer" : { "pinyin_analyzer" : { "tokenizer" : "my_pinyin", "filter" : ["word_delimiter"] } }, "tokenizer" : { "my_pinyin" : { "type" : "pinyin", "first_letter" : "append", "padding_char" : " " } } } } } }' "http://localhost:9200/medcl2" 拼音效果如下 curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl2/_analyze" { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 3, "type": "word", "position": 0 }, { "token": "de", "start_offset": 0, "end_offset": 3, "type": "word", "position": 1 }, { "token": "hua", "start_offset": 0, "end_offset": 3, "type": "word", "position": 2 }, { "token": "ldh", "start_offset": 0, "end_offset": 3, "type": "word", "position": 3 } ] } first_letter=only padding_char=" " curl -X POST -d '{ "mappings": { "folk": { "properties": { "text": { "type": "string", "analyzer": "pinyin_analyzer" } } } }, "settings": { "index" : { "analysis" : { "analyzer" : { "pinyin_analyzer" : { "tokenizer" : "my_pinyin", "filter" : ["word_delimiter"] } }, "tokenizer" : { "my_pinyin" : { "type" : "pinyin", "first_letter" : "only", "padding_char" : " " } } } } } }' "http://localhost:9200/medcl3" 拼音效果如下 curl -X POST -H "Cache-Control: no-cache" -H "Postman-Token: 67015c0d-cd07-961b-4c46-da90f7d558d8" -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl3/_analyze" { "tokens": [ { "token": "ldh", "start_offset": 0, "end_offset": 3, "type": "word", "position": 0 } ] } first_letter=none padding_char=" " curl -X POST -d '{ "mappings": { "folk": { "properties": { "text": { "type": "string", "analyzer": "pinyin_analyzer" } } } }, "settings": { "index" : { "analysis" : { "analyzer" : { "pinyin_analyzer" : { "tokenizer" : "my_pinyin", "filter" : ["word_delimiter"] } }, "tokenizer" : { "my_pinyin" : { "type" : "pinyin", "first_letter" : "none", "padding_char" : " " } } } } } }' "http://localhost:9200/medcl4" 拼音效果如下 curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl4/_analyze" { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 3, "type": "word", "position": 0 }, { "token": "de", "start_offset": 0, "end_offset": 3, "type": "word", "position": 1 }, { "token": "hua", "start_offset": 0, "end_offset": 3, "type": "word", "position": 2 } ] } padding_char 改变 first_letter=prefix padding_char="" curl -X POST -d '{ "mappings": { "folk": { "properties": { "text": { "type": "string", "analyzer": "pinyin_analyzer" } } } }, "settings": { "index" : { "analysis" : { "analyzer" : { "pinyin_analyzer" : { "tokenizer" : "my_pinyin", "filter" : ["word_delimiter"] } }, "tokenizer" : { "my_pinyin" : { "type" : "pinyin", "first_letter" : "prefix", "padding_char" : "" } } } } } }' "http://localhost:9200/medcl5" 拼音效果如下 curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl5/_analyze" { "tokens": [ { "token": "ldhliudehua", "start_offset": 0, "end_offset": 3, "type": "word", "position": 0 } ] } first_letter=append padding_char="" curl -X PUT -d '{ "mappings": { "folk": { "properties": { "text": { "type": "string", "analyzer": "pinyin_analyzer" } } } }, "settings": { "index" : { "analysis" : { "analyzer" : { "pinyin_analyzer" : { "tokenizer" : "my_pinyin", "filter" : ["word_delimiter"] } }, "tokenizer" : { "my_pinyin" : { "type" : "pinyin", "first_letter" : "append", "padding_char" : "" } } } } } }' "http://localhost:9200/medcl7" 拼音效果如下 curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl7/_analyze" { "tokens": [ { "token": "liudehualdh", "start_offset": 0, "end_offset": 3, "type": "word", "position": 0 } ] } first_letter=only padding_char="" curl -X PUT -d '{ "mappings": { "folk": { "properties": { "text": { "type": "string", "analyzer": "pinyin_analyzer" } } } }, "settings": { "index" : { "analysis" : { "analyzer" : { "pinyin_analyzer" : { "tokenizer" : "my_pinyin", "filter" : ["word_delimiter"] } }, "tokenizer" : { "my_pinyin" : { "type" : "pinyin", "first_letter" : "only", "padding_char" : "" } } } } } }' "http://localhost:9200/medcl8" 拼音效果如下 curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl8/_analyze" { "tokens": [ { "token": "ldh", "start_offset": 0, "end_offset": 3, "type": "word", "position": 0 } ] } first_letter=none padding_char="" curl -X PUT -d '{ "mappings": { "folk": { "properties": { "text": { "type": "string", "analyzer": "pinyin_analyzer" } } } }, "settings": { "index" : { "analysis" : { "analyzer" : { "pinyin_analyzer" : { "tokenizer" : "my_pinyin", "filter" : ["word_delimiter"] } }, "tokenizer" : { "my_pinyin" : { "type" : "pinyin", "first_letter" : "none", "padding_char" : "" } } } } } }' "http://localhost:9200/medcl9" 拼音效果如下 curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl9/_analyze" { "tokens": [ { "token": "liudehua", "start_offset": 0, "end_offset": 3, "type": "word", "position": 0 } ] } 结论 elasticsearch 2.3.2 对应 elasticsearch-analysis-pinyin 1.7.2,pinyin 1.7.2 可配置参数有:first_letter 和 padding_char。 padding_char 的作用是将字符串按照什么字符分隔,比如 padding_char = " ",那么 刘德华 将先被分隔为 刘,德,华;如果 padding_char = "",那么 刘德华 将不会被分隔 first_letter 取值:prefix,append,only,none。 padding_char 与 first_letter 的组合会影响拼音输出的结果 elasticsearch-analysis-pinyin 2.x 分支 github 项目 elasticsearch-analysis-pinyin 2.x 分支 是为 elasticsearch 2.x 服务,经过测试 elasticsearch 2.3.2 也可以使用该插件。 官方文档中的说明 remove_duplicated_term when this option enabled, duplicated term will be removed to save index, eg: de的>de, default: false, NOTE: position related query maybe influenced keep_first_letter when this option enabled, eg: 刘德华>ldh, default: true keep_separate_first_letter when this option enabled, will keep first letters separately, eg: 刘德华>l,d,h, default: false, NOTE: query result maybe too fuzziness due to term too frequency limit_first_letter_length set max length of the first_letter result, default: 16 keep_full_pinyin when this option enabled, eg: 刘德华> [liu,de,hua], default: true keep_joined_full_pinyin when this option enabled, eg: 刘德华> [liudehua], default: false keep_none_chinese keep non chinese letter or number in result, default: true keep_none_chinese_together keep non chinese letter together, default: true, eg: DJ音乐家 -> DJ,yin,yue,jia, when set to false, eg: DJ音乐家 -> D,J,yin,yue,jia, NOTE: keep_none_chinese should be enabled first keep_none_chinese_in_first_letter keep non Chinese letters in first letter, eg: 刘德华AT2016->ldhat2016, default: true none_chinese_pinyin_tokenize break non chinese letters into separate pinyin term if they are pinyin, default: true, eg: liudehuaalibaba13zhuanghan -> liu,de,hua,a,li,ba,ba,13,zhuang,han, NOTE: keep_none_chinese and keep_none_chinese_together should be enabled first keep_original when this option enabled, will keep original input as well, default: false lowercase lowercase non Chinese letters, default: true trim_whitespace default: true 基准配置 基准配置参数 "keep_joined_full_pinyin": "false", "lowercase": "true", "keep_original": "false", "keep_none_chinese_together": "true", "remove_duplicated_term": "false", "keep_first_letter": "true", "keep_separate_first_letter": "false", "trim_whitespace": "true", "keep_none_chinese": "true", "limit_first_letter_length": "16", "keep_full_pinyin": "true" 创建索引与分词器 curl -X POST -d '{ "mappings": { "folk": { "properties": { "text": { "type": "string", "analyzer": "pinyin_analyzer" } } } }, "settings": { "index" : { "analysis" : { "analyzer" : { "pinyin_analyzer" : { "tokenizer" : "my_pinyin" } }, "tokenizer" : { "my_pinyin" : { "type" : "pinyin", "remove_duplicated_term" : false, "keep_joined_full_pinyin" : false, "keep_separate_first_letter" : false, "keep_first_letter" : true, "limit_first_letter_length" : 16, "keep_full_pinyin" : true, "keep_original" : true, "keep_none_chinese" : true, "keep_none_chinese_together" : true, "lowercase" : true, "trim_whitespace" : true } } } } } }' "http://localhost:9200/medcl20" 生成索引结构 curl -X GET "http://localhost:9200/medcl20" { "medcl20": { "aliases": {}, "mappings": { "folk": { "properties": { "text": { "type": "string", "analyzer": "pinyin_analyzer" } } } }, "settings": { "index": { "creation_date": "1490170676090", "analysis": { "analyzer": { "pinyin_analyzer": { "tokenizer": "my_pinyin" } }, "tokenizer": { "my_pinyin": { "keep_joined_full_pinyin": "false", "lowercase": "true", "keep_original": "true", "keep_none_chinese_together": "true", "remove_duplicated_term": "false", "keep_first_letter": "true", "keep_separate_first_letter": "false", "trim_whitespace": "true", "type": "pinyin", "keep_none_chinese": "true", "limit_first_letter_length": "16", "keep_full_pinyin": "true" } } }, "number_of_shards": "5", "number_of_replicas": "1", "uuid": "31Y9PizQQ2KQn_Fl6bpPNw", "version": { "created": "2030299" } } }, "warmers": {} } } 分词器分词效果 curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl20/_analyze" { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "de", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "hua", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 }, { "token": "刘德华", "start_offset": 0, "end_offset": 3, "type": "word", "position": 3 }, { "token": "ldh", "start_offset": 0, "end_offset": 3, "type": "word", "position": 4 } ] } keep_original keep_original = true curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl20/_analyze" { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "de", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "hua", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 }, { "token": "刘德华", "start_offset": 0, "end_offset": 3, "type": "word", "position": 3 }, { "token": "ldh", "start_offset": 0, "end_offset": 3, "type": "word", "position": 4 } ] } keep_original = false curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl20/_analyze" { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "de", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "hua", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 }, { "token": "ldh", "start_offset": 0, "end_offset": 3, "type": "word", "position": 3 } ] } keep_original 功能 keep_original=true 将保留原字符串,比如存入索引的数据为 刘德华 那么 刘德华 将也会被保存到索引中。keep_original=false 则不保存原字符串到索引 trim_whitespace trim_whitespace=true curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : [" 最爱 刘德华 的帅气帅气的 "] }' "http://localhost:9200/medcl20/_analyze" { "tokens": [ { "token": "zui", "start_offset": 3, "end_offset": 4, "type": "word", "position": 0 }, { "token": "ai", "start_offset": 4, "end_offset": 5, "type": "word", "position": 1 }, { "token": "liu", "start_offset": 8, "end_offset": 9, "type": "word", "position": 2 }, { "token": "de", "start_offset": 9, "end_offset": 10, "type": "word", "position": 3 }, { "token": "hua", "start_offset": 10, "end_offset": 11, "type": "word", "position": 4 }, { "token": "de", "start_offset": 14, "end_offset": 15, "type": "word", "position": 5 }, { "token": "shuai", "start_offset": 15, "end_offset": 16, "type": "word", "position": 6 }, { "token": "qi", "start_offset": 16, "end_offset": 17, "type": "word", "position": 7 }, { "token": "shuai", "start_offset": 17, "end_offset": 18, "type": "word", "position": 8 }, { "token": "qi", "start_offset": 18, "end_offset": 19, "type": "word", "position": 9 }, { "token": "de", "start_offset": 19, "end_offset": 20, "type": "word", "position": 10 }, { "token": "最爱 刘德华 的帅气帅气的", "start_offset": 0, "end_offset": 23, "type": "word", "position": 11 }, { "token": "zaldhdsqsqd", "start_offset": 0, "end_offset": 11, "type": "word", "position": 12 } ] } trim_whitespace=false curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : [" 最爱 刘德华 的帅气帅气的 "] }' "http://localhost:9200/medcl20/_analyze" { "tokens": [ { "token": "zui", "start_offset": 3, "end_offset": 4, "type": "word", "position": 0 }, { "token": "ai", "start_offset": 4, "end_offset": 5, "type": "word", "position": 1 }, { "token": "liu", "start_offset": 8, "end_offset": 9, "type": "word", "position": 2 }, { "token": "de", "start_offset": 9, "end_offset": 10, "type": "word", "position": 3 }, { "token": "hua", "start_offset": 10, "end_offset": 11, "type": "word", "position": 4 }, { "token": "de", "start_offset": 14, "end_offset": 15, "type": "word", "position": 5 }, { "token": "shuai", "start_offset": 15, "end_offset": 16, "type": "word", "position": 6 }, { "token": "qi", "start_offset": 16, "end_offset": 17, "type": "word", "position": 7 }, { "token": "shuai", "start_offset": 17, "end_offset": 18, "type": "word", "position": 8 }, { "token": "qi", "start_offset": 18, "end_offset": 19, "type": "word", "position": 9 }, { "token": "de", "start_offset": 19, "end_offset": 20, "type": "word", "position": 10 }, { "token": " 最爱 刘德华 的帅气帅气的 ", "start_offset": 0, "end_offset": 23, "type": "word", "position": 11 }, { "token": "zaldhdsqsqd", "start_offset": 0, "end_offset": 11, "type": "word", "position": 12 } ] } trim_whitespace 功能 去除字符串首尾空格字符,不去除字符串中间的空格。这个参数只有当 keep_original=true 时才能够看到效果。 例如当字符串为:最爱 刘德华 的帅气帅气的,trim_whitespace=true 则原字符串将被保存为 最爱 刘德华 的帅气帅气的,如果 trim_whitespace=false 则原字符串将被保存为 最爱 刘德华 的帅气帅气的。如果 keep_original=false,那么原字符串没有被保存,也将看不到效果。 keep_joined_full_pinyin keep_joined_full_pinyin = false curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl21/_analyze" { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "de", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "hua", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 }, { "token": "刘德华", "start_offset": 0, "end_offset": 3, "type": "word", "position": 3 }, { "token": "ldh", "start_offset": 0, "end_offset": 3, "type": "word", "position": 4 } ] } keep_joined_full_pinyin = true curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华"] }' "http://localhost:9200/medcl22/_analyze" { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "de", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "hua", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 }, { "token": "刘德华", "start_offset": 0, "end_offset": 3, "type": "word", "position": 3 }, { "token": "liudehua", "start_offset": 0, "end_offset": 8, "type": "word", "position": 4 }, { "token": "ldh", "start_offset": 0, "end_offset": 3, "type": "word", "position": 5 } ] } keep_joined_full_pinyin 功能 keep_joined_full_pinyin=true 将保存字符串拼音全拼,false 则不保存。例如,当 kepp_joined_full_pinyin=true 时,文本 刘德华 的拼音全拼 liudehua 将会被保留;当 keep_joined_full_pinyin=false 则 全拼liudehua remove_duplicated_term remove_duplicated_term = false curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华刘德华帅帅帅,帅帅帅"] }' "http://localhost:9200/medcl20/_analyze" { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "de", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "hua", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 }, { "token": "liu", "start_offset": 3, "end_offset": 4, "type": "word", "position": 3 }, { "token": "de", "start_offset": 4, "end_offset": 5, "type": "word", "position": 4 }, { "token": "hua", "start_offset": 5, "end_offset": 6, "type": "word", "position": 5 }, { "token": "shuai", "start_offset": 6, "end_offset": 7, "type": "word", "position": 6 }, { "token": "shuai", "start_offset": 7, "end_offset": 8, "type": "word", "position": 7 }, { "token": "shuai", "start_offset": 8, "end_offset": 9, "type": "word", "position": 8 }, { "token": "shuai", "start_offset": 10, "end_offset": 11, "type": "word", "position": 9 }, { "token": "shuai", "start_offset": 11, "end_offset": 12, "type": "word", "position": 10 }, { "token": "shuai", "start_offset": 12, "end_offset": 13, "type": "word", "position": 11 }, { "token": "刘德华刘德华帅帅帅,帅帅帅", "start_offset": 0, "end_offset": 13, "type": "word", "position": 12 }, { "token": "ldhldhssssss", "start_offset": 0, "end_offset": 12, "type": "word", "position": 13 } ] } remove_duplicated_term = true curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华刘德华帅帅帅,帅帅帅"] }' "http://localhost:9200/medcl26/_analyze" { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "de", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "hua", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 }, { "token": "shuai", "start_offset": 6, "end_offset": 7, "type": "word", "position": 3 }, { "token": "刘德华刘德华帅帅帅,帅帅帅", "start_offset": 0, "end_offset": 13, "type": "word", "position": 4 }, { "token": "ldhldhssssss", "start_offset": 0, "end_offset": 12, "type": "word", "position": 5 } ] } remove_duplicated_term 功能 remove_duplicated_term=true 则会将文本中相同的拼音只保存一份,比如 刘德华刘德华 只会保留一份拼音 liu,de,hua;相对的 remove_duplicated_term=false 则会保留两份 liu,de,hua。注意:remove_duplicated_term 并不会影响文本首字母的文本,刘德华刘德华 生成的首字母拼音始终都为 ldhldh remove_duplicated_term = true 并且 keep_joined_full_pinyin = true curl -X POST -d '{ "analyzer" : "pinyin_analyzer", "text" : ["刘德华刘德华帅帅帅,帅帅帅"] }' "http://localhost:9200/medcl27/_analyze" { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "de", "start_offset": 1, "end_offset": 2, "type": "word", "position": 1 }, { "token": "hua", "start_offset": 2, "end_offset": 3, "type": "word", "position": 2 }, { "token": "shuai", "start_offset": 6, "end_offset": 7, "type": "word", "position": 3 }, { "token": "刘德华刘德华帅帅帅,帅帅帅", "start_offset": 0, "end_offset": 13, "type": "word", "position": 4 }, { "token": "liudehualiudehuashuaishuaishuaishuaishuaishuai", "start_offset": 0, "end_offset": 46, "type": "word", "position": 5 }, { "token": "ldhldhssssss", "start_offset": 0, "end_offset": 12, "type": "word", "position": 6 } ] } remove_duplicated_term 功能 remove_duplicated_term = true 会过滤相同的拼音,但是不影响全拼,刘德华刘德华 生成的字符串全拼为 liudehualiudehua keep_none_chinese keep_none_chinese = true POST /medcl20/_analyze HTTP/1.1 Host: localhost:9200 { "analyzer" : "pinyin_analyzer", "text" : ["刘*20*德b华DJ"] } { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "20", "start_offset": 3, "end_offset": 5, "type": "word", "position": 1 }, { "token": "de", "start_offset": 5, "end_offset": 6, "type": "word", "position": 2 }, { "token": "b", "start_offset": 6, "end_offset": 7, "type": "word", "position": 3 }, { "token": "hua", "start_offset": 7, "end_offset": 8, "type": "word", "position": 4 }, { "token": "d", "start_offset": 7, "end_offset": 9, "type": "word", "position": 5 }, { "token": "j", "start_offset": 7, "end_offset": 9, "type": "word", "position": 6 }, { "token": "刘*20*德b华dj", "start_offset": 0, "end_offset": 10, "type": "word", "position": 7 }, { "token": "l20dbhdj", "start_offset": 0, "end_offset": 8, "type": "word", "position": 8 } ] } keep_none_chinese = false POST /medcl28/_analyze HTTP/1.1 Host: localhost:9200 { "analyzer" : "pinyin_analyzer", "text" : ["刘*20*德b华DJ"] } { "tokens": [ { "token": "liu", "start_offset": 0, "end_offset": 1, "type": "word", "position": 0 }, { "token": "de", "start_offset": 5, "end_offset": 6, "type": "word", "position": 1 }, { "token": "hua", "start_offset": 7, "end_offset": 8, "type": "word", "position": 2 }, { "token": "刘*20*德b华dj", "start_offset": 0, "end_offset": 10, "type": "word", "position": 3 }, { "token": "l20dbhdj", "start_offset": 0, "end_offset": 8, "type": "word", "position": 4 } ] } keep_none_chinese 功能 keep_none_chinese = true 则非中文字母以及数字将会被保留,但是要确定所有的特别字符都是无法被保留下来的。例如,文本 刘*20*德b华dj 中的数字 20,字母 b 与 dj 将会被保留,而特殊字符 * 是不会保留的;当 keep_none_chinese=false 则非中文字母以及数字将不会被保留,上述文本中的数字 20,字母 b 与 dj 将不会被保留。注意:参数 keep_none_chinese 是不会影响首字母以及所有字符组成全拼的拼音,上述文本生成的首字母拼音为 l20dbhdj,所有字符组成的全拼为:liu20debhuadj,特别字符始终是被过滤去除的。 欢迎转载,请注明本文链接,谢谢你。 2017.4.12 20:44

优秀的个人博客,低调大师

通过VBA检索AD中的用户

VBA作为一种被用在Microsoft Office产品中的脚本语言,功能实际上是很强大的,这要归功于它继承了Visual Basic的很多优良传统,如我们可以在VBA工程中像VB 6一样引入类库或ActiveX控件。当然,你在VB 6中使用的组件或控件也完成可以在VBA中使用,例如日期输入组件。 VBA支持ADO,这也意味着我们可以在VBA中操作数据库,包括SQL Server、Access等。正因为如此,你可以尝试着在Excel中编写一个VBA程序来将Excel作为一个用于数据同步的中间件,该中间件可以用来同步应用程序与后台数据库中的数据,并在其中做一些处理。这是完全可以做到的!我在之前的一个项目中就曾做过这样的例子,我们甚至通过Excel同步了SQL Server和Sharepoint List之间的数据(这个我会在随后的一些中再做介绍)。 ADO的功能也很强大,我们可以在其中执行Windows脚本,经常使用的就是通过它来管理Windows Server的AD,如遍历AD中的账户,批量修改AD中的账户等,这已经没有什么好奇怪的了,你完全可以在Windows上使用记事本编写一个vbs脚本,然后直接使用它来管理你服务器上的AD,当然,基于安全考虑,这些都是需要适当的执行权限的。这些工作通过VBA也可以完成,并且在Excel中你会发觉有的时候很方便,如借用Excel现有的UI来展现所操作的数据;基于所查询的数据来生成图表;使用Excel提供的功能修改数据;遍历和组织数据的方便性等等... 下面我给出一个示例。在新的Sheet中添加一个ActiveX Button,然后添加下面的代码。 Private Sub CommandButton1_Click() On Error Resume Next Const ADS_SCOPE_SUBTREE = 2 Set objConnection = CreateObject ( " ADODB.Connection " ) Set objCommand = CreateObject ( " ADODB.Command " ) objConnection.Provider = " ADsDSOObject " objConnection.Open " Active Directory Provider " Set objCommand.ActiveConnection = objConnection objCommand.Properties( " Page Size " ) = 1000 objCommand.Properties( " Searchscope " ) = ADS_SCOPE_SUBTREE objCommand.CommandText = _ " SELECT Name FROM 'LDAP://dc=XXX,dc=com' WHERE objectCategory='user' " ' search all users from the domn XXX Set objRecordSet = objCommand.Execute objRecordSet.MoveFirst Dim currCell As Range Set currCell = Range( " A1 " ) Do Until objRecordSet.EOF currCell.Value = objRecordSet.fields( " Name " ).Value Set currCell = currCell.Offset( 1 , 0 ) objRecordSet.MoveNext Loop End Sub 代码中我使用了一个假象的域名称XXX,读者如果想要测试可将其改成真实的域名称。我们使用ADO对所指定的域进行了用户查询,并将域中所有用户的Name依次输出到Excel当前Sheet的第一列中,下面是输出结果的部分截图。 有关如何通过脚本操作AD,读者可以参考一下Microsoft的官方文档http://www.microsoft.com/technet/scriptcenter/guide/sas_usr_ykxh.mspx?mfr=true 如果权限允许,稍微修改一下上面的代码,完全可以实现批量修改AD中的账户的功能,鉴于公司网络环境,这里不方便进行测试了,读者如果有条件可以自己尝试。 本文转自Jaxu博客园博客,原文链接:http://www.cnblogs.com/jaxu/archive/2009/06/18/1505849.html,如需转载请自行联系原作者

资源下载

更多资源
腾讯云软件源

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题,腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构,目前腾讯云软件源站支持公网访问和内网访问。

Spring

Spring

Spring框架(Spring Framework)是由Rod Johnson于2002年提出的开源Java企业级应用框架,旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念,提供核心容器、应用上下文、数据访问集成等模块,支持整合Hibernate、Struts等第三方框架,其适用范围不仅限于服务器端开发,绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux

Rocky Linux(中文名:洛基)是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版,作为CentOS稳定版停止维护后与RHEL(Red Hat Enterprise Linux)完全兼容的开源替代方案,由社区拥有并管理,支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性,采用模块化包装和SELinux安全架构,默认包含GNOME桌面环境及XFS文件系统,支持十年生命周期更新。

Sublime Text

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能,例如代码缩略图,Python的插件,代码段等。还可自定义键绑定,菜单和工具栏。Sublime Text 的主要功能包括:拼写检查,书签,完整的 Python API , Goto 功能,即时项目切换,多选择,多窗口等等。Sublime Text 是一个跨平台的编辑器,同时支持Windows、Linux、Mac OS X等操作系统。

用户登录
用户注册