0

I have a simple task to do. I have a long text in DB column and I need to break this long text for words and put each word to another table, lets say to fts_words: (id INT, wrd VARCHAR[255]). I can't find anything like split() or explode() in MySQL and even MySQL examples. All I found was stupid "look-for-each-delimeter-substring-and-insert-each" solutions.

Is there some block functions in MySQL to help me with this or more elegant cycle solution?

Awtszs
  • 323
  • 1
  • 8
  • 33
Epsiloncool
  • 1,435
  • 16
  • 39
  • Why don't you use an external scripting language like Ruby, Python or Perl to manage this for you? – tadman Oct 29 '14 at 02:56
  • @tadman Because I want to use TRIGGERs to start this task. Can I use triggers to run scripts written on Ruby, Python or Perl? – Epsiloncool Oct 29 '14 at 02:57
  • What's your use case for this carved up data? A trigger is a messy way to do this no matter how you slice it. It's worth noting that Postgres allows you to [run arbitrary JavaScript](https://code.google.com/p/plv8js/wiki/PLV8) as an alternative to the older scripting language used to write triggers and such, giving it a lot of flexibility compared to MySQL. – tadman Oct 29 '14 at 02:57
  • @tadman I am happy that Postgres can do that, but I need the solution for MySQL. My idea is to index all texts in database automatically (they are placed in very different tables and columns) - this is for fulltext search algorithm which I need to implement. – Epsiloncool Oct 29 '14 at 03:18
  • That leads to the question of why you'd want to implement your own full-text system when there's already one built into MySQL, plus others like [Sphinx](http://sphinxsearch.com/) or services like [Elastic Search](http://www.elasticsearch.org/) that already do this. Unless you've written a few full-text search engines before, this is going to be an uphill battle. – tadman Oct 29 '14 at 04:00
  • @tadman The fulltext index works only with MyISAM tables, which is very bad, because these tables has limitations (no ROW LOCKS for example). But other types of tables (InnoDB) does not provide any fulltext search capabilities. I also do not want to use any 3rd side systems like Sphinx etc, because I should to implement their support in all my code, and they are too big for me. – Epsiloncool Oct 29 '14 at 08:58
  • MySQL 5.6 adds [`FULLTEXT` indexes to InnoDB as well](http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html). Prior to that what you'd do is make a duplicate table in MyISAM mode kept in sync with triggers and add the indexes there. I have a hard time taking the "too big for me" comment seriously from someone about to implement their own full-text search. Do you know how hard that is to get right and make it work on non-trivial sets of data? – tadman Oct 29 '14 at 14:50
  • @tadman Well, I just do not like integrated fulltext search. For example it works worse when I need to search by only part of word or I have a mistake in search term. I want to improve such things. – Epsiloncool Oct 29 '14 at 15:38
  • Unless you have two or three years to spare, and at least a software engineering degree, you're probably in for a world of hurt you will not escape from. You might want to try tackling some easy problems, like creating a self-driving car or sending a rocket to Mars. A full-text search system is very easy to prototype, but the difficulty in implementing something that scales beyond a handful of words ramps up exponentially. – tadman Oct 29 '14 at 17:12

0 Answers0