Mysql – Extract all unique chars from all texts across the table

character-setMySQL

I have a huge mysql table (20 columns by 320.000 rows) that contains a lot of text from a lot of languages and the problem is that I need to identify all the unique characters in that table. For example, if character "," appears one time or more than one time in that table I need this query to show it to me once.

The query should return a list with all the unique characters from this database. I don’t need the query to count them.

Can you help me finding this solution?
Thanks in advance, Catalin

Best Answer

MySQL has no way to split a cell into a bunch of values. (Yeah, it can split a piece of a cell off -- see LEFT(), etc. But not an arbitrary set.)

You would be better off doing the task in PHP or some other language -- more powerful than SQL. (Yeah, it could be done in a Stored Procedure, the the code would be really messy.)

Here is some pseudo code for PHP (etc):

declare $hash as an associative array
foreach row of SELECT * FROM tbl
    foreach cell
        split cell into characters
            $hash[$char] = 1
dump the _keys_ of $hash

(Getting the counts is a trivial enhancement.)

Be sure to work in "characters", not "bytes". The details of that depend on the language.

Related Solutions

MySql – selecting into file and re-loading it with special characters

I would suggest remove all those REPLACE(text,',','****') stuff, and use FIELDS ENCLOSED BY...:

SELECT 
  id,
  status_twitter_id,
  twitter_id, 
  creator_twitter_identity_id, 
  text, 
  in_reply_to_status_id, 
  in_reply_to_user_id, 
  urls,
  hashtags,
  created_at, 
  updated_at, 
  sent_at,
  user_mentions
FROM 
  twitter_statuses 
into outfile '/tmp/twitter_statuses.csv'  
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
;

You then LOAD DATA INFILE like this:

LOAD DATA LOCAL INFILE '/srv/$SPLIT_FOLDER/$f' 
INTO TABLE my_db.twitter_statuses_new 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
...

This should do the work.

Mysql – Cache all thesql table

Certain database engines do support pinning objects to cache, Oracle and MS-SQL to name a couple. MySQL does not have this option, as far as I know.

On the other hand I cannot think of many use cases for this option, as modern database systems are very good at managing cache. MySQL offers block caching with an LRU algorithm through the InnoDB storage engine.

There is also an in memory storage engine, but there is no easy and efficient way replicate changes back to disk.

There are complete in-memory databases out there if you want everything in RAM, including MySQL Cluster.

On a sidenote, in practical use there is a trade-off between I/O and CPU resources. More cache means less I/O but more CPU is consumed, and there is a real possibility of introducing new bottlenecks.

Best Answer

Related Solutions

MySql – selecting into file and re-loading it with special characters

Mysql – Cache all thesql table

Related Question