有关mysql中的sql语法的问题，请高手指点!

INSERT INTO table1 (id, sid) VALUES ('1', '"域1"');
INSERT INTO table1 (id, sid) VALUES ('2', '"域2"');
INSERT INTO table1 (id, sid) VALUES ('3', '"域3"');
INSERT INTO table1 (id, sid) VALUES ('4', '\"域4\"');
INSERT INTO table1 (id, sid) VALUES ('5', '\"域5\"');mysql> SELECT * FROM test.Table1;
+----+-------+
| id | sid   |
+----+-------+
| 1  | "域1" |
| 5  | "域5" |
| 3  | "域3" |
| 4  | "域4" |
| 5  | "域5" |
+----+-------+
5 rows in set (0.00 sec)
mysql> SELECT id,TRIM(BOTH '"' FROM sid) FROM test.Table1 ;
+----+-------------------------+
| id | TRIM(BOTH '"' FROM sid) |
+----+-------------------------+
| 1  | 域1                     |
| 5  | 域5                     |
| 3  | 域3                     |
| 4  | 域4                     |
| 5  | 域5                     |
+----+-------------------------+
5 rows in set (0.03 sec)

解决方案 »

  1.   

    mysql> SELECT BENCHMARK(100000 ,TRIM(BOTH '"' FROM sid)) FROM test.Table1;
    +--------------------------------------------+
    | BENCHMARK(100000 ,TRIM(BOTH '"' FROM sid)) |
    +--------------------------------------------+
    |                                          0 |
    |                                          0 |
    |                                          0 |
    |                                          0 |
    |                                          0 |
    +--------------------------------------------+
    5 rows in set (0.19 sec)SELECT BENCHMARK(100000 ,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sid,'"',1),'"',-1))
    FROM test.Table1;
    +-----------------------------------------------------------------------+
    | BENCHMARK(100000 ,SUBSTRING_INDEX(SUBSTRING_INDEX(sid,'"',1),'"',-1)) |
    +-----------------------------------------------------------------------+
    |                                                                     0 |
    |                                                                     0 |
    |                                                                     0 |
    |                                                                     0 |
    |                                                                     0 |
    +-----------------------------------------------------------------------+
    5 rows in set (0.14 sec)
    试了好五六次，
    好像这个更快一点嘛
    SELECT BENCHMARK(100000 ,SUBSTRING_INDEX(SUBSTRING_INDEX(sid,'"',1),'"',-1)) FROM test.Table1;
      

  2.   

    呵呵，
    怪不得，
    是我将SQL语句写错了，
    SELECT BENCHMARK(100000 ,SUBSTRING_INDEX(SUBSTRING_INDEX(sid,'\"',-2),'"',1)) FROM test.Table1;
    +------------------------------------------------------------------------+
    | BENCHMARK(100000 ,SUBSTRING_INDEX(SUBSTRING_INDEX(sid,'\"',-2),'"',1)) |
    +------------------------------------------------------------------------+
    |                                                                      0 |
    |                                                                      0 |
    |                                                                      0 |
    |                                                                      0 |
    |                                                                      0 |
    +------------------------------------------------------------------------+
    5 rows in set (0.26 sec)mysql> SELECT BENCHMARK(100000 ,TRIM(BOTH '"' FROM sid)) FROM test.Table1;
    +--------------------------------------------+
    | BENCHMARK(100000 ,TRIM(BOTH '"' FROM sid)) |
    +--------------------------------------------+
    |                                          0 |
    |                                          0 |
    |                                          0 |
    |                                          0 |
    |                                          0 |
    +--------------------------------------------+
    5 rows in set (0.20 sec)》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》SELECT BENCHMARK(100000 ,TRIM(BOTH '"' FROM sid)) FROM test.Table1;
    还是快一点