需要将SQL列从varchar（255）转换为bigint（20）

The 'thread' column in an old Drupal 4.7 Comments table is varchar(255), and I need to convert it to bigint(20) to make it work for the 'comment_parent' column in a Wordpress wp_comments table.

旧的Drupal 4.7 Comments表中的'thread'列是varchar(255),我需要将它转换为bigint(20),使其适用于Wordpress wp_comments表中的'comment_parent'列。

I've tried various Cast and Convert commands I've seen and always get syntax errors.

我已经尝试了各种Cast和Convert命令,并且总是会遇到语法错误。

3 个解决方案

#1

This works in SQL Server:

这适用于SQL Server:

create table Comments 
(
    [thread] nvarchar(255)
) 

insert comments 
select '1' 
union select '2' 
union select '3' 
union select '4' 
union select '5' 
union select 'x' 

select 
    case 
        when ISNUMERIC([thread]) > 0 
            then CAST([thread] as bigint) 
        else 
            null 
    end colAsBigInt 
    , [thread] colAsNvarChar 
from comments

http://sqlfiddle.com/#!6/337eb/1

For MySQL try:

对于MySQL尝试:

create table if not exists Comments 
(
    thread varchar(255) character set UTF8 not null
);

insert comments(thread) values ('1');
insert comments(thread) values ('2');
insert comments(thread) values ('3');
insert comments(thread) values ('4');
insert comments(thread) values ('5');
insert comments(thread) values ('6.1');
insert comments(thread) values ('x');

select 
    case 
        when thread  REGEXP ('^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$') 
            then cast(thread as signed)
        else 
            null 
    end colAsBigInt 
    , thread colAsVarChar 
from comments

--regex trick from here: http://forums.mysql.com/read.php?60,1907,241284#msg-241284
--without the regex you'll get 0s instead of nulls for invalid values
--MySQL's cast only works on certain data types, given here http://www.roseindia.net/sql/mysql-example/mysql-cast.shtml

Runnable MySQL sample here: http://sqlfiddle.com/#!2/6d848/9

这里有Runnable MySQL示例:http://sqlfiddle.com/#!2/6d848/9

#2

when I ran that second set of code you provided for MySQL, it didn't convert that column to BigInt. It did give a side-by-side comparison of the colasBigInt and colasVarChar. Without exception, for thousands of rows, all colasBigInt read as Null, regardless of the colasVarChar value.

当我运行你为MySQL提供的第二组代码时,它没有将该列转换为BigInt。它确实给出了colasBigInt和colasVarChar的并列比较。毫无例外,对于数千行,无论colasVarChar值如何,所有colasBigInt都读为Null。

#3

What are the syntax errors you are getting? What database are you using?

你得到的语法错误是什么?你用的是什么数据库?

The more information that you can give, the more likely it is that someone can help you.

您可以提供的信息越多,有人可以帮助您的可能性就越大。

#1