iT邦幫忙

0

[php]正則表示式要如何下

我有一串複雜的SQL,加上limit等參數取出該分頁的資料後,接著要重新取有總共有多少筆資料,主要是用在分頁功能上~

想透過正則表示式把我的複雜SQL取代成 SELECT COUNT(1) AS total_rows FROM ....的形式,想請問前輩們,要怎麼下正則表示式??

環境:php5.5 / mysql 5.5

比如說這個SQL

SELECT
    view_terms.dep_AS_value schAsName,
    view_terms.dep_kind_value schKindName,
    view_terms.dep_division_value schDepName,
    GROUP_CONCAT(DISTINCT CONCAT(classes.class_grade,'-',classes.class_room)) classRoom,
    if(wtplan.rv_opinion2<>'',wtplan.rv_opinion2,wtplan.rv_opinion1)opinion,
    GROUP_CONCAT(DISTINCT result ORDER BY result DESC) 'status',
    sum(perweekno*weeksno)cnt
FROM (SELECT class_id, class_division, class_grade, class_room
        FROM classes
        WHERE sch_id = '116' AND class_year = 109
        GROUP BY class_division
    ) classes
INNER JOIN view_terms ON `classes`.class_division = view_terms.dep_division_id
LEFT JOIN (
    SELECT *, (CASE WHEN giveup THEN -1 ELSE rvornot END) result
    FROM wtplan
    WHERE schid = '116' AND years = 109 AND giveup = 0 AND rvornot IN (0, 1, 3, 4)
    GROUP BY schdepid
) wtplan ON wtplan.schdepid = classes.class_division
GROUP BY classes.class_division

要如何在不影響from後面的sql之下,把 select ... from 中間的欄位(含子查詢)取代成COUNT(1) AS total_rows?

目前失敗的做法

// $this->sql : 上面那陀SQL

$sql = preg_replace('/\s{2,}/', ' ', $this->sql); // 替換掉空白
$sql = preg_replace('/(?:order|ORDER).*/s', ' ', $this->sql); // order之後的全部拿掉
$sql = preg_replace('/(?:LIMIT|limit|OFFSET|office)\s+\d+/', '', $sql); // 拿掉LIMIT|OFFSET (以防萬一regex不支援s參數)
$sql = preg_replace('/^(?:SELECT|select).+?(?:FROM|from)/s', 'SELECT 1 FROM', $sql); // 避免因重複欄位出錯
$sql = "SELECT COUNT(1) AS `total_rows` FROM ($sql) _pagination_"; // 因應可能有group,所以用新的select 包起來以防萬一

/*
此時SQL長這樣,出現錯誤的$sql
SELECT COUNT(1) AS `total_rows` FROM (SELECT
view_terms.dep_AS_value schAsName,
view_terms.dep_kind_value schKindName,
view_terms.dep_division_value schDepName,
GROUP_CONCAT(DISTINCT CONCAT(classes.class_grade,'-',classes.class_room)) classRoom,
wtplan.wtplanid,
if(wtplan.rv_opinion2<>'',wtplan.rv_opinion2,wtplan.rv_opinion1)opinion,
GROUP_CONCAT(DISTINCT result  ) _pagination_
*/

預期正則表示式處理的結果

SELECT
    COUNT(1) AS total_rows 
FROM (SELECT class_id, class_division, class_grade, class_room
        FROM classes
        WHERE sch_id = '116' AND class_year = 109
        GROUP BY class_division
    ) classes
INNER JOIN view_terms ON `classes`.class_division = view_terms.dep_division_id
LEFT JOIN (
    SELECT *, (CASE WHEN giveup THEN -1 ELSE rvornot END) result
    FROM wtplan
    WHERE schid = '116' AND years = 109 AND giveup = 0 AND rvornot IN (0, 1, 3, 4)
    GROUP BY schdepid
) wtplan ON wtplan.schdepid = classes.class_division
GROUP BY classes.class_division

2020/1/13 想到替代方法

//把原本的sql當作子查詢來處理
$sql = preg_replace('/\s{2,}/', ' ', $this->sql); // 替換掉空白
$sql = preg_replace('/(LIMIT\s+\d+\s)+(OFFSET\s+\d+)+$/', '', $sql); // 拿掉LIMIT|OFFSET (以防萬一regex不支援s參數)
$sql = "SELECT COUNT(1) AS `total_rows` FROM ($this->sql) _pagination_"; 

不過還是想知道如果要用regex來替換掉複雜sql中的
select[...,(select...from...limit 1)abc, ...]from...where...exists(select...from...)
中間的內容(中括號刮起來的地方),要怎麼下正則??

1
浩瀚星空
iT邦大師 1 級 ‧ 2020-01-12 12:00:38

由於不太清楚你的資料情況。很難給你有效的答案。
一般先教你不二法門的方發。

先用手寫的方式。將你目前的sql化成你要的計數方式。確定是否正確後。
再查看兩者的差別性質在哪。

如果可以簡化就盡量簡化。畢竟像是select之類的東西就沒必要的。
畢竟你只是想要計數。

大多數而言。我會將主表的select化成count。將join表內的select化成*處理。如果有搭配group的才只針對其group處理。

太過複雜的。則會先分段來處理正則。再組合起來處理。

看更多先前的回應...收起先前的回應...

他一系列的問題,很努力的用盡各種方法,是精神可嘉.
但是我個人看法是,問題的核心在於,沒有適當的規劃好基本的各table.
也零散的問,遇到卡關,就用很辛苦的方法突破,或者說是往下走.
但是越來越複雜,也越來越難處理.
若能夠好好規劃,應該是會比現在順利.

這我承認,看他的sql就很頭痛。這因該還可以利用一下緩存或是統計表來處理。讓sql不至於那麼複雜化。

不過我還是試教了第一招。先試著寫計數化的對應sql出來後。再來決定該怎麼故。

我是覺得有點可惜,往很辛苦的方向走,不過這也是經驗,沒有經過
這些嘗試,就不會成長.
經過嘗試後,再來總結,改進,會有更深刻的體會.

其實我很想問欸,這個提問究竟是在問
php的正規式怎麼用,還是sql syntax為什麼會出錯?
有誰看懂了可以先為我解惑嗎?

舜~ iT邦好手 1 級 ‧ 2020-01-13 01:22:06 檢舉

抱歉語意不清楚,問正規式~~因為正規式沒下好導致sql處理得不如預期導致出錯
至於資料庫...接手時就一個坑.....
那陀sql是分頁模組在撈出表格資料後接著要計算總筆數用,所以想說透過正則表示式來處理會比較快...

SQL+正則=FOREACH.......這效率會合理嗎?

舜~ iT邦好手 1 級 ‧ 2020-01-13 08:47:59 檢舉

SQL+正則=FOREACH ?? 抱歉有看沒懂
我這模組功能是從外面接收一串sql進來,吐回去表格所需的資料與可以前後換頁的導覽條,每換一次頁執行一次

firecold iT邦新手 4 級 ‧ 2020-01-13 10:37:22 檢舉

SQL+正則=FOREACH 意思是效率很差...
如果能改結構

在寫入, 更新時把正則後需要的資料另外存
看搜尋需求是要存欄位或者一對多關聯都可以

一般這種的,我會建議你用自刻算總數量會比較好。
如果沒有太多的話。

如可以改變資料節構當然是最好了。不過我懂接手舊程式的痛苦。
如果需求上沒必要或是沒時間搞的情況下。
只好用補破網的方式來解決了。

畢竟你這真的不好搞。

好吧 我看懂你那一坨拉苦的 sql 了
只能說效能爆差的 以要取總筆數的目的來說
可以請你註記一下你的 php 版本嗎?
正常 sql: select a,b,... from ....;
不可能的 sql:
select a, b, (select ... from xxx) ... from ...;
如果你有不可能版的 sql 並且可以成功執行,請務必給小弟我長長見識。

舜~ iT邦好手 1 級 ‧ 2020-01-15 08:49:56 檢舉

@f107110126 辛苦了...抱歉少打字,環境已經補上
select a, b, (select xxx from xxx limit 1) as 'abc' ... from ...;
多個limit 1 就可以了,只要輸出是只有一筆的單一欄位就可以了

@舜~ 還真長見識了,沒想到 sql 如此破壞結構的事情都辦得到。有需要的時候一定會有幫助的。很遺憾我的解法,無法解決這種等級的問題。不過這樣的問題,估計得用 stack 的方式解決了,正規式可無法幫到你。

1
海綿寶寶
iT邦大神 1 級 ‧ 2020-01-14 09:35:06

看看可不可以用

<?php
$sql = "SELECT
    view_terms.dep_AS_value schAsName,
    view_terms.dep_kind_value schKindName,
    view_terms.dep_division_value schDepName,
    GROUP_CONCAT(DISTINCT CONCAT(classes.class_grade,'-',classes.class_room)) classRoom,
    if(wtplan.rv_opinion2<>'',wtplan.rv_opinion2,wtplan.rv_opinion1)opinion,
    GROUP_CONCAT(DISTINCT result ORDER BY result DESC) 'status',
    sum(perweekno*weeksno)cnt
FROM (SELECT class_id, class_division, class_grade, class_room
        FROM classes
        WHERE sch_id = '116' AND class_year = 109
        GROUP BY class_division
    ) classes
INNER JOIN view_terms ON `classes`.class_division = view_terms.dep_division_id
LEFT JOIN (
    SELECT *, (CASE WHEN giveup THEN -1 ELSE rvornot END) result
    FROM wtplan
    WHERE schid = '116' AND years = 109 AND giveup = 0 AND rvornot IN (0, 1, 3, 4)
    GROUP BY schdepid
) wtplan ON wtplan.schdepid = classes.class_division
GROUP BY classes.class_division";

	echo "Before:\n" . $sql . "\n";
	$sql = preg_replace("/SELECT((.*)(\n))*FROM \(/", "SELECT COUNT(1) AS total_rows FROM (", $sql);
	echo "After:\n" . $sql . "\n";
?>
1
f107110126
iT邦新手 5 級 ‧ 2020-01-14 22:02:25

此解無用,略。

我要發表回答

立即登入回答