นับจำนวนตัวอักษรรวมทุกแถวของคอลัมน์ใน MySQL

คำสั่งหลัก

ใช้ CHAR_LENGTH() ร่วมกับ SUM() เพื่อนับตัวอักษรรวมทั้งตาราง

รูปแบบคำสั่ง

SELECT SUM(CHAR_LENGTH(yourColumnName)) AS total_chars FROM yourTableName;

ตัวอย่างครบถ้วน

1. สร้างตารางเก็บรายวิชา

mysql> create table CountAllCharactersDemo 
    -> ( 
    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> UserName varchar(20), 
    -> UserSubject text 
    -> ); 
Query OK, 0 rows affected (0.47 sec)

2. เพิ่มข้อมูลตัวอย่าง

mysql> insert into CountAllCharactersDemo(UserName,UserSubject) values('Larry','Introduction To Java'); 
Query OK, 1 row affected (0.19 sec)
mysql> insert into CountAllCharactersDemo(UserName,UserSubject) values('Mike','Introduction To Computer Networks'); 
Query OK, 1 row affected (0.21 sec)
mysql> insert into CountAllCharactersDemo(UserName,UserSubject) values('Sam','Introduction To C'); 
Query OK, 1 row affected (0.18 sec)
mysql> insert into CountAllCharactersDemo(UserName,UserSubject) values('Carol','Introduction To Python'); 
Query OK, 1 row affected (0.25 sec)
mysql> insert into CountAllCharactersDemo(UserName,UserSubject) values('David','Introduction To Spring And Hibernate Framework'); 
Query OK, 1 row affected (0.15 sec)

3. ดูข้อมูลทั้งหมด

mysql> select * from CountAllCharactersDemo;

+--------+----------+------------------------------------------------+
| UserId | UserName | UserSubject                                    |
+--------+----------+------------------------------------------------+
| 1      | Larry    | Introduction To Java                           |
| 2      | Mike     | Introduction To Computer Networks              |
| 3      | Sam      | Introduction To C                              |
| 4      | Carol    | Introduction To Python                         |
| 5      | David    | Introduction To Spring And Hibernate Framework |
+--------+----------+------------------------------------------------+

4. นับตัวอักษรรวมทุกแถว (Case 1)

mysql> select sum(char_length(UserSubject)) AS AllCharactersLength from CountAllCharactersDemo;

+---------------------+
| AllCharactersLength |
+---------------------+
| 138                 |
+---------------------+

5. นับตัวอักษรแต่ละแถว (Case 2)

mysql> select UserId, UserName, UserSubject, char_length(UserSubject) AS Length from CountAllCharactersDemo;

+--------+----------+------------------------------------------------+--------+
| UserId | UserName | UserSubject                                    | Length |
+--------+----------+------------------------------------------------+--------+
| 1      | Larry    | Introduction To Java                           | 20     |
| 2      | Mike     | Introduction To Computer Networks              | 33     |
| 3      | Sam      | Introduction To C                              | 17     |
| 4      | Carol    | Introduction To Python                         | 22     |
| 5      | David    | Introduction To Spring And Hibernate Framework | 46     |
+--------+----------+------------------------------------------------+--------+

หมายเหตุ: CHAR_LENGTH() นับจำนวนตัวอักษร (characters) ส่วน LENGTH() นับจำนวน bytes - สำคัญเมื่อเก็บข้อความ UTF-8