基于MS SQLServer的全表搜索-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

基于MS SQLServer的全表搜索

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:43 PM

sqlserverbased onsearch

最近在做项目的时候遇到一个问题，就是在查找记录的时候输入关键字找出满足条件的记录。一开始是每一个字段一个关键字，后面感觉有的查找的时候会涉及到6个甚至更多的字段，感觉在查找的时候相当困难。网上找了很久，想找到一种能够不需要指定字段就可以全表

最近在做项目的时候遇到一个问题，就是在查找记录的时候输入关键字找出满足条件的记录。一开始是每一个字段一个关键字，服务器空间，后面感觉有的查找的时候会涉及到6个甚至更多的字段，感觉在查找的时候相当困难。网上找了很久，想找到一种能够不需要指定字段就可以全表搜索的方法，可惜一直没找到，最后找到一个折中的办法：在每一个表中定义一个字段，这个字段存储其他所有字段的和，然后查找的时候就只要匹配这个字段就行了。

下面是实现思路：

因为在每条数据插入和修改的时候需要往这个字段中插入值或者是更新这个字段，而如果靠代码来控制的话会非常麻烦：你必须时时刻刻记得在哪里插入数据了，又在哪里修改了数据。最后想到了触发器，直接在数据库中建立一个触发器。

触发器语句如下：

Create trigger [GECOAssets_Trigger]
on [dbo].[GECOAssets] for
insert,update
as
Update GECOAssets Set
SumText=isnull(SourceID,'') +'∩'+isnull(SourceName,'')+'∩'+isnull(StateNow,'')+'∩'+isnull(BuyDepartment,'')+'∩'+isnull(Factory,'')
+'∩'+isnull(SourceType,'')+'∩'+isnull(BackInfo,'')

这样原本以为已经大功告成了，最后测试的时候发现，只要插入一条数据，或者表中的某个字段的值发生改变，所有的SumText的值都会被重新更新或者插入，美国空间，当数据量很大的时候或者数据更新插入很频繁的时候，势必给数据库造成很大的压力。

这时我突然想起了当初学Orace的时候老师讲过的行级触发器，于是百度、谷歌找了很久，最后得出的结论是：MS SQLServer不支持行级触发器。这下让我很受打击啊，毕竟自己觉得还不错的方法就这样被终结了，心情极度郁闷。不过，不抛弃不放弃是我的座右铭，于是死磕继续百度、谷歌，最后发现，在插入和更新数据的时候，会有一个表叫做inserted,而这个表会存储你更新时候的数据，这样，我灵光一闪：那这个表中肯定就会存储这条记录的ID值，只要能取到ID的值，想必就可以指定更新某一条记录了，也就可以实现行级触发器的效果了，我抱着试试看的心态把触发器改成了如下：

Alter trigger [GECOAssets_Trigger]
on [dbo].[GECOAssets] for
insert,update
as
Update GECOAssets Set
SumText=isnull(SourceID,'') +'∩'+isnull(SourceName,'')+'∩'+isnull(StateNow,'')+'∩'+isnull(BuyDepartment,'')+'∩'+isnull(Factory,'')
+'∩'+isnull(SourceType,'')+'∩'+isnull(BackInfo,'') where ID in (select ID from inserted)

果不其然，完美地实现了行级触发器的效果，至此就可以全表不指定字段(当然还是需要指定SumText字段的)搜索了，感觉虽然不是什么完美的解决方案，香港空间，但是毕竟还是比较理想的实现了想要的效果。不知道各位有没有更好的解决办法，或者说有没有第三方的组件来实现全表关键字搜索或者更牛的多表联合关键字搜索呢？

如果有的话，各位不要吝啬哦，小弟这厢有礼了...

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the role of InnoDB redo logs and undo logs.Apr 15, 2025 am 12:16 AM

InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.

What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?Apr 15, 2025 am 12:15 AM

Key metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.

What is the Using temporary status in EXPLAIN and how to avoid it?Apr 15, 2025 am 12:14 AM

Usingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB

Describe the different SQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and their implications in MySQL/InnoDB.Apr 15, 2025 am 12:11 AM

MySQL/InnoDB supports four transaction isolation levels: ReadUncommitted, ReadCommitted, RepeatableRead and Serializable. 1.ReadUncommitted allows reading of uncommitted data, which may cause dirty reading. 2. ReadCommitted avoids dirty reading, but non-repeatable reading may occur. 3.RepeatableRead is the default level, avoiding dirty reading and non-repeatable reading, but phantom reading may occur. 4. Serializable avoids all concurrency problems but reduces concurrency. Choosing the appropriate isolation level requires balancing data consistency and performance requirements.

MySQL vs. Other Databases: Comparing the OptionsApr 15, 2025 am 12:08 AM

MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.

How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AM

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AM

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AM

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.

See all articles