mysql删除unionkey_如何从批量数据中的MySQL列中删除所有非数字字符

I want to remove all the non numeric characters from the column. I have bulk data in my database.

Currently I am using method as describe in below link:

The problem is that its taking too much time for preocessing.

For 1 million of row current logic takes 1 hour to process the data.

please help me..

Thank You,

Ronak

解决方案

Here's another spin on things...

First, create yourself a numbers table

CREATE TABLE numbers (

number int NOT NULL PRIMARY KEY

);

INSERT INTO numbers (number)

SELECT n0 + n1 + n2 + n3 + n4 + n5

FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS z0

CROSS

JOIN (SELECT 0 AS n1 UNION SELECT 4 UNION SELECT 8 UNION SELECT 12) AS z1

CROSS

JOIN (SELECT 0 AS n2 UNION SELECT 16 UNION SELECT 32 UNION SELECT 48) AS z2

CROSS

JOIN (SELECT 0 AS n3 UNION SELECT 64 UNION SELECT 128 UNION SELECT 192) AS z3

CROSS

JOIN (SELECT 0 AS n4 UNION SELECT 256 UNION SELECT 512 UNION SELECT 768) AS z4

CROSS

JOIN (SELECT 0 AS n5 UNION SELECT 1024 UNION SELECT 2048 UNION SELECT 3072) AS z5

ORDER

BY 1;

Here's some sample data to play with

CREATE TABLE your_table (

foo varchar(50)

);

INSERT INTO your_table (foo)

VALUES ('124nhasfonasf13')

, ('NONE')

, ('r937')

, ('o9o9')

, ('n444n4n455n')

, ('blah');

Then here's a query to give you just the numbers. Should be more efficient as it is SET based instead of iterative like your function example...

SELECT foo

, Group_Concat(c ORDER BY position SEPARATOR '')

FROM (

SELECT vals.foo

, numbers.number As position

, SubString(vals.foo, numbers.number, 1) As c

FROM (

SELECT foo

, Length(foo) As lngth

FROM your_table

WHERE foo REGEXP '[0-9]'

) As vals

INNER

JOIN numbers

ON numbers.number BETWEEN 1 AND vals.lngth

) As x

WHERE c REGEXP '[0-9]'

GROUP

BY foo