mysql转换整数_在MySQL中将文本列转换为整数

I'm trying to create a cleaned-up table that replaces a varchar(50) field with an integer. The original field has occasional text values that I'd like to convert to 0 or null values.

I can do this in a select statement just fine, but get an error when trying to create my table.

Below is a minimal example that uses strings:

/* This works */

DROP TABLE IF EXISTS mytest;

CREATE TABLE mytest

AS

SELECT convert("123", unsigned) AS mynum;

/* This works, returning a row of 0 */

SELECT convert("TEST", unsigned) AS mynum;

/* But this fails, with: Truncated incorrect INTEGER value: 'TEST'*/

DROP TABLE IF EXISTS mytest;

CREATE TABLE mytest

AS

SELECT convert("TEST", unsigned) AS mynum;`

What is wrong with the above, and is there a better way to accomplish what I want? Thanks for the help.

解决方案

I'd use a case statement for it and also switch over to using CAST instead of convert so you'll get failures instead of implicit conversions.

CREATE TABLE mytest(myVarchar varchar(10));

INSERT mytest VALUES ('123'),('TEST');

SELECT CASE

WHEN myVarchar REGEXP '^[0-9]+$' THEN CAST(myVarchar,unsigned)

ELSE 0

END As mynum

FROM mytest;

I don't have a mysql instance handy to test this so hopefully I didn't goof any syntax errors.