MySQL选择CONCAT条件（mysql选择语句）

25-02-17 15

在本文中，我们将给您介绍关于MySQL选择CONCAT条件的详细内容，并且为您解答mysql选择语句的相关问题，此外，我们还将为您提供关于com.mysql.jdbc.MysqlDataTruncat

在本文中，我们将给您介绍关于MySQL选择CONCAT条件的详细内容，并且为您解答mysql选择语句的相关问题，此外，我们还将为您提供关于com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value:...、com.mysql.jdbc.MysqlDataTruncation的实例源码、concat和concat_ws()区别及group_concat() ,repeat()字符串函数_MySQL、concat和concat_ws()区别及MySQL的几个实用字符串函数_MySQL的知识。

本文目录一览：

MySQL选择CONCAT条件（mysql选择语句）
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value:...
com.mysql.jdbc.MysqlDataTruncation的实例源码
concat和concat_ws()区别及group_concat() ,repeat()字符串函数_MySQL
concat和concat_ws()区别及MySQL的几个实用字符串函数_MySQL

MySQL选择CONCAT条件（mysql选择语句）

我正在尝试将其编译。.我有一个带有firstname和lastname字段的表，并且我有一个字符串，例如“ Bob Jones”或“ Bob Michael Jones”等。

事实是，例如，我姓鲍勃，姓迈克尔·琼斯

所以我想

SELECT neededfield,CONCAT(firstname,' ',lastname) as firstlast 
  FROM users 
 WHERE firstlast = "Bob Michael Jones"

但是它说未知列“ firstlast” ..有人可以帮助吗？

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value:...

在使用mybatis的@Update注解的时候，报了一个这样的错

### Error updating database.  Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: ''4028158162c347830162c347fb050000''
### The error may involve com.xwj.mapper.UserMapper.updateUser-Inline
### The error occurred while setting parameters
### SQL: UPDATE xwj_user SET last_name = ? WHERE id = 2
### Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: ''4028158162c347830162c347fb050000''
; ]; Data truncation: Truncated incorrect DOUBLE value: ''4028158162c347830162c347fb050000''; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: ''4028158162c347830162c347fb050000''] with root cause

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: ''4028158162c347830162c347fb050000''
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3974) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486) ~[mysql-connector-java-5.1.46.jar:5.1.46]

调用的方法如下：

/**
     * 更新
     */
    @Update("UPDATE xwj_user SET last_name = #{lastName} WHERE id = ${id}")
    int updateUser(@Param("lastName") String name, @Param("id") String id);

在网上看了各种解决办法，有说是set语句中逗号误写成了and导致的，楼主遇到的不是这个情况

看上面的报错，以为是数据库中id字段(长度VARCHAR(32))为''4028158162c347830162c347fb050000''的这条数据长度过长导致的。于是将这条数据的id改为‘1’，一执行，发现执行成功。楼主便得意的以为是id过长导致的。

但心里也奇怪数据库明明可以放32位，为啥会报错呢？于是想一探究竟，id字段多少位时，才会报错。于是便将id改为是‘12’，然后‘123’...依次加，直到加到‘12345678901234567890123456789012’，一共32位了，仍然执行成功，这个时候楼主就懵逼了。这怎么会执行成功呢？

突然灵光一闪，观察到有没有可能是id中不是全数字导致的呢？于是将其中的一个数字改为字母，再执行就报错了。功夫不负有心人，终于找到了罪魁祸首

至于为啥id必须是纯数字，希望哪位大神在评论区留言，再次小弟谢过~

com.mysql.jdbc.MysqlDataTruncation的实例源码

项目：the-vigilantes 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable("bug44324","(Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SomeVChar VARCHAR(10)) ENGINE=MyISAM;");

    try {
        this.stmt.executeUpdate("INSERT INTO bug44324 values (null,'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：OpenVertretung 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable("bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：ProyectoPacientes 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable("bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：BibliotecaPS 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable("bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：leopard 文件：MessageParserImpl.java

public static String parse(MysqLDataTruncation exception) {//Todo 这里改成sqlException
    String msg;
    try {
        msg = INSTANCE.parse(exception.getMessage());
        if (msg == null) {
            msg = exception.getMessage();
        }
    }
    catch (Exception e) {
        e.printstacktrace();
        msg = e.getMessage();
    }
    return msg;
}

项目：Geometry-wars 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable("bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：TPKB 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable("bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：x-cure-chat 文件：UpdateForumMessageExecutor.java

public ResultSet executeQuery( PreparedStatement pstmt,Void result ) throws sqlException,SiteException {
    try {
        if( pstmt.executeUpdate() == 0 ) {
            logger.error("Could not update the forum message " +  message.messageID + " by user " + message.senderID );
            throw new MessageException( MessageException.THE_MESSAGE_YOU_UPDATE_DOES_NOT_EXIST );
        }
    } catch( MysqLDataTruncation e ) {
        logger.error( "An unhandled exception when sending a forum message by " + message.senderID,e);
        throw new MessageException( MessageException.THE_RESULTING_MESSAGE_IS_TOO_LONG );
    }
    return null;
}

项目：cloudera-cli-scripts 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable("bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：fil_project_mgmt_app_v2 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable(
            "bug44324",SomeVChar VARCHAR(10)) ENGINE=MyISAM;");

    try {
        this.stmt
                .executeUpdate("INSERT INTO bug44324 values (null,'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：SMPT42 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable("bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：ForYou 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable(
            "bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：group-five 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable("bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：locaviewer 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable("bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：Gladiatus 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable(
            "bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：Jspad 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable(
            "bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：Trails 文件：ConnectionRegressionTest.java

public void testBug44324() throws Exception {
    createTable(
            "bug44324",'Some text much longer than 10 characters')");
    } catch (MysqLDataTruncation sqlEx) {
        assertTrue(0 != sqlEx.getErrorCode());
    }

}

项目：cheep-orm 文件：BeanColumnDeFinitionTest.java

/**
 * Test method for {@link net.jirasystems.cheeporm.Orm#create(java.lang.Object)}.
 * 
 * @throws sqlException .
 * @throws illegalaccessexception .
 * @throws InstantiationException .
 */
@Test(expected = MysqLDataTruncation.class)
public void testCreatetooLong() throws sqlException,InstantiationException,illegalaccessexception {

    // Given
    bean = newBean();
    String stringValue = "value too long";
    bean.setName(stringValue);

    // When
    orm.create(bean);

    // Then
    // Expected exception: MysqLDataTruncation
}

项目：pentaho-kettle 文件：DatabaseLogExceptionFactoryTest.java

/**
 * PDI-5153
 * Test that in case of MysqLDataTruncation exception there will be no stack trace in log
 */
@Test public void testExceptionStrategyWithMysqLDataTruncationException() {
  DatabaseMeta databaseMeta = mock( DatabaseMeta.class );
  DatabaseInterface databaseInterface = new MysqLDatabaseMeta();
  MysqLDataTruncation e = new MysqLDataTruncation();

  when( logTable.getDatabaseMeta() ).thenReturn( databaseMeta );
  when( databaseMeta.getDatabaseInterface() ).thenReturn( databaseInterface );

  LogExceptionBehavIoUrInterface
    exceptionStrategy =
    DatabaseLogExceptionFactory.getExceptionStrategy( logTable,new KettleDatabaseException( e ) );
  String strategyName = exceptionStrategy.getClass().getName();
  assertEquals( SUPpressable_WITH_SHORT_MESSAGE,strategyName );
}

项目：leopard 文件：ErrorUtil.java

/**
 * 获取异常信息.
 * 
 * @param e
 * @return
 */
public static String parseMessage(Throwable e) {
    if (e == null) {
        throw new IllegalArgumentException("exception不能为空?");
    }

    String className = e.getClass().getName();

    String message = MESSAGE_MAP.get(className);
    if (message != null) {
        return message;
    }
    if (e instanceof GenericSignatureFormatError) {
        printStartupTime();
        return "更新程序后，还没有重启服务.";
    }
    if (e instanceof NoSuchMethodError) {
        printStartupTime();
        return "NoSuchMethodError:方法找不到.";
    }
    if (e instanceof sqlException) {
        return "操作数据库出错，请稍后重试.";
    }
    if (e instanceof DataIntegrityViolationException) {
        Exception exception = (Exception) e.getCause();
        if (exception instanceof MysqLDataTruncation) {
            return MessageParserImpl.parse((MysqLDataTruncation) exception);
        }
        else {
            return "操作数据库出错，请稍后重试.";
        }
        // try {
        // return parseDataIntegrityViolationException((DataIntegrityViolationException) e);
        // }
        // catch (Exception e1) {
        // e1.printstacktrace();
        // return "字段太长，请稍后重试.";
        // }

    }
    message = e.getMessage();
    if (message == null) {
        return null;
    }
    return fillterDebugInfo(message);
}

concat和concat_ws()区别及group_concat() ,repeat()字符串函数_MySQL

1、concat()函数
    1.1 mysql的concat函数可以连接一个或者多个字符串,如
        mysql> select concat(''10'');
        +--------------+
        | concat(''10'') |
        +--------------+
        | 10           |
        +--------------+
       1 row in set (0.00 sec)

       mysql> select concat(''11'',''22'',''33'');
        +------------------------+
        | concat(''11'',''22'',''33'') |
        +------------------------+
        | 112233                 |
        +------------------------+

1 row in set (0.00 sec)

而Oracle的concat函数只能连接两个字符串

SQL> select concat(''11'',''22'') from dual;

点击下载“修复打印机驱动工具”；

1.2 MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL

        mysql> select concat(''11'',''22'',null);
        +------------------------+
        | concat(''11'',''22'',null) |
        +------------------------+
        | NULL                   |
        +------------------------+
       1 row in set (0.00 sec)

而Oracle的concat函数连接的时候，只要有一个字符串不是NULL,就不会返回NULL

        SQL> select concat(''11'',NULL) from dual;
       CONCAT
       --
       11

2、concat_ws()函数, 表示concat with separator,即有分隔符的字符串连接
如连接后以逗号分隔
mysql> select concat_ws('','',''11'',''22'',''33'');

        +-------------------------------+
        | concat_ws('','',''11'',''22'',''33'') |
        +-------------------------------+
        | 11,22,33                      |
        +-------------------------------+
       1 row in set (0.00 sec)

   和concat不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL
        mysql> select concat_ws('','',''11'',''22'',NULL);
        +-------------------------------+
        | concat_ws('','',''11'',''22'',NULL) |
        +-------------------------------+
        | 11,22                         |
        +-------------------------------+
       1 row in set (0.00 sec)

3、group_concat()可用来行转列, Oracle没有这样的函数（mysql4.1及以上）

    完整的语法如下
    group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ''分隔符''])
   如下例子
    mysql> select * from aa;

    +------+------+
    | id   | name |
    +------+------+
    |    1 | 10   |
    |    1 | 20   |
    |    1 | 20   |
    |    2 | 20   |
    |    3 | 200 |
    |    3 | 500 |
    +------+------+
   6 rows in set (0.00 sec)
   3.1 以id分组，把name字段的值打印在一行，逗号分隔(默认)
       mysql> select id,group_concat(name) from aa group by id;
        +------+--------------------+
        | id   | group_concat(name) |
        +------+--------------------+
        |    1 | 10,20,20           |
        |    2 | 20                 |
        |    3 | 200,500            |
        +------+--------------------+

3 rows in set (0.00 sec)

   3.2 以id分组，把name字段的值打印在一行，分号分隔
        mysql> select id,group_concat(name separator '';'') from aa group by id;
        +------+----------------------------------+
        | id   | group_concat(name separator '';'') |
        +------+----------------------------------+
        |    1 | 10;20;20                         |
        |    2 | 20                               |
        |    3 | 200;500                          |
        +------+----------------------------------+

3 rows in set (0.00 sec)

3.3 以id分组，把去冗余的name字段的值打印在一行，逗号分隔

mysql> select id,group_concat(distinct name) from aa group by id;

        +------+-----------------------------+
        | id   | group_concat(distinct name) |
        +------+-----------------------------+
        |    1 | 10,20                       |
        |    2 | 20                          |
        |    3 | 200,500                     |
        +------+-----------------------------+

3 rows in set (0.00 sec)

3.4 以id分组，把name字段的值打印在一行，逗号分隔,以name排倒序

mysql> select id,group_concat(name order by name desc) from aa group by id;

        +------+---------------------------------------+
        | id   | group_concat(name order by name desc) |
        +------+---------------------------------------+
        |    1 | 20,20,10                              |
        |    2 | 20                                    |
        |    3 | 500,200                               |
        +------+---------------------------------------+

3 rows in set (0.00 sec)

4、repeat()函数，用来复制字符串,如下''ab''表示要复制的字符串，2表示复制的份数

mysql> select repeat(''ab'',2);

    +----------------+
    | repeat(''ab'',2) |
    +----------------+
    | abab           |
    +----------------+

1 row in set (0.00 sec)

又如
mysql> select repeat(''a'',2);

    +---------------+
    | repeat(''a'',2) |
    +---------------+
    | aa            |
    +---------------+
   1 row in set (0.00 sec)

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/shuicaohui5/archive/2008/10/23/3129489.aspx

concat和concat_ws()区别及MySQL的几个实用字符串函数_MySQL

bitsCN.com 1、concat()函数
1.1 MySQL的concat函数可以连接一个或者多个字符串,如
mysql> select concat(''10'');
+--------------+
| concat(''10'') |
+--------------+
| 10           |
+--------------+
1 row in set (0.00 sec)

mysql> select concat(''11'',''22'',''33'');
+------------------------+
| concat(''11'',''22'',''33'') |
+------------------------+
| 112233                 |
+------------------------+

1 row in set (0.00 sec)

而Oracle的concat函数只能连接两个字符串

SQL> select concat(''11'',''22'') from dual;

1.2 MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL

mysql> select concat(''11'',''22'',null);
+------------------------+
| concat(''11'',''22'',null) |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)

而Oracle的concat函数连接的时候，只要有一个字符串不是NULL,就不会返回NULL

SQL> select concat(''11'',NULL) from dual;
CONCAT
--
11

2、concat_ws()函数, 表示concat with separator,即有分隔符的字符串连接
如连接后以逗号分隔
mysql> select concat_ws('','',''11'',''22'',''33'');

+-------------------------------+
| concat_ws('','',''11'',''22'',''33'') |
+-------------------------------+
| 11,22,33                      |
+-------------------------------+
1 row in set (0.00 sec)

和concat不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL
mysql> select concat_ws('','',''11'',''22'',NULL);
+-------------------------------+
| concat_ws('','',''11'',''22'',NULL) |
+-------------------------------+
| 11,22                         |
+-------------------------------+
1 row in set (0.00 sec)

3、group_concat()可用来行转列, Oracle没有这样的函数

完整的语法如下
group_concat([DISTINCT] 要连接的字段[Order BY ASC/DESC 排序字段] [Separator ''分隔符''])
如下例子
mysql> select * from aa;

+------+------+
| id   | name |
+------+------+
|    1 | 10   |
|    1 | 20   |
|    1 | 20   |
|    2 | 20   |
|    3 | 200 |
|    3 | 500 |
+------+------+
6 rows in set (0.00 sec)
3.1 以id分组，把name字段的值打印在一行，逗号分隔(默认)
mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id   | group_concat(name) |
+------+--------------------+
|    1 | 10,20,20           |
|    2 | 20                 |
|    3 | 200,500            |
+------+--------------------+

3 rows in set (0.00 sec)

3.2 以id分组，把name字段的值打印在一行，分号分隔
mysql> select id,group_concat(name separator '';'') from aa group by id;
+------+----------------------------------+
| id   | group_concat(name separator '';'') |
+------+----------------------------------+
|    1 | 10;20;20                         |
|    2 | 20                               |
|    3 | 200;500                          |
+------+----------------------------------+

3 rows in set (0.00 sec)

3.3 以id分组，把去冗余的name字段的值打印在一行，逗号分隔

mysql> select id,group_concat(distinct name) from aa group by id;

+------+-----------------------------+
| id   | group_concat(distinct name) |
+------+-----------------------------+
|    1 | 10,20                       |
|    2 | 20                          |
|    3 | 200,500                     |
+------+-----------------------------+

3 rows in set (0.00 sec)

3.4 以id分组，把name字段的值打印在一行，逗号分隔,以name排倒序

mysql> select id,group_concat(name order by name desc) from aa group by id;

+------+---------------------------------------+
| id   | group_concat(name order by name desc) |
+------+---------------------------------------+
|    1 | 20,20,10                              |
|    2 | 20                                    |
|    3 | 500,200                               |
+------+---------------------------------------+

3 rows in set (0.00 sec)

4、repeat()函数，用来复制字符串,如下''ab''表示要复制的字符串，2表示复制的份数

mysql> select repeat(''ab'',2);

+----------------+
| repeat(''ab'',2) |
+----------------+
| abab           |
+----------------+

1 row in set (0.00 sec)

又如
mysql> select repeat(''a'',2);

+---------------+
| repeat(''a'',2) |
+---------------+
| aa            |
+---------------+
1 row in set (0.00 sec) bitsCN.com

今天关于MySQL选择CONCAT条件和mysql选择语句的分享就到这里，希望大家有所收获，若想了解更多关于com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value:...、com.mysql.jdbc.MysqlDataTruncation的实例源码、concat和concat_ws()区别及group_concat() ,repeat()字符串函数_MySQL、concat和concat_ws()区别及MySQL的几个实用字符串函数_MySQL等相关知识，可以在本站进行查询。

本文标签：