Re: mysql lenght() and char_length not working for longer texts
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