Re: mysql lenght() and char_length not working for longer texts

From: Axel Schwenke <axel.schwenke_at_gmx.de>
Date: Mon, 6 Jul 2015 22:32:58 +0200
Message-ID: <q85r6c-k4n.ln1_at_xl.homelinux.org>

manoj_paramasivam_at_trimble.com wrote:
> I want restrict my text column 'b' to throw message on lenght greater
> than 32766. But CHAR_LENGHT() is not working for such a large texts.

How do you work out that
"CHAR_LENGHT() is not working for ... large texts" ?

This works perfectly for me:

mysql>show create table t1\G

*************************** 1. row ***************************
       Table: t1

Create Table: CREATE TABLE `t1` (
  `c1` int(11) NOT NULL,
  `c2` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>show triggers\G

*************************** 1. row ***************************
             Trigger: test
               Event: INSERT
               Table: t1
           Statement: BEGIN
    IF CHAR_LENGTH(NEW.c2) > 32766 THEN
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'check constraint on Attributes.stringValue failed';

    END IF;
END

              Timing: BEFORE
             Created: NULL
            sql_mode: NO_ENGINE_SUBSTITUTION
             Definer: root_at_localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

mysql>insert into t1 values (1, "test"); Query OK, 1 row affected (0.01 sec)

mysql>insert into t1 values (2, repeat("x", 32766)); Query OK, 1 row affected (0.00 sec)

mysql>insert into t1 values (3, repeat("x", 32767)); ERROR 1643 (02000): check constraint on Attributes.stringValue failed

mysql>select version();
+-----------+
| version() |
+-----------+
| 5.6.21 |
+-----------+
1 row in set (0.00 sec)

XL Received on Mon Jul 06 2015 - 22:32:58 CEST