/**
* 执行查询
*
* @param string $sql
* @return mixed
*/
public static function query($sql, $host = 'master')
{
self::connect($host);
if (C('debug')) {
addUpTime('queryStartTime');
}
$query = mysql_query($sql, self::$link[$host]);
if (C('debug')) {
addUpTime('queryEndTime');
}
if ($query === false) {
$error = "Db Error: " . mysql_error(self::$link[$host]);
if (C('debug')) {
throw_exception($error . '<br/>' . $sql);
} else {
Log::record($error . "\r\n" . $sql, Log::ERR);
return false;
}
} else {
Log::record($sql . " [ RunTime:" . addUpTime('queryStartTime', 'queryEndTime', 6) . "s ]", Log::SQL);
return $query;
}
}
/**
* 调试模式记录
*
* @param string $sql
*/
public static function debug($sql)
{
addUpTime('queryEndTime');
Log::record($sql . " [ RunTime:" . addUpTime('queryStartTime', 'queryEndTime', 6) . "s ]", Log::SQL);
}
/**
* 执行查询
*
* @param string $sql
* @return mixed
*/
public static function query($sql, $host = 'sqlserver')
{
$host = 'sqlserver';
self::connect($host);
if (C('debug')) {
addUpTime('queryStartTime');
}
$lowstr = strtolower($sql);
//处理 limit
$limitindex = strpos($lowstr, ' limit ');
if ($limitindex > 0) {
$limitstr = strtolower(trim(substr($sql, $limitindex)));
$limits = explode(' ', $limitstr);
$limitnum = explode(',', $limits[1]);
//得到主键用来orderby
$orderindex = strpos($lowstr, ' order ');
$fromindex = strpos($lowstr, ' from ');
$whereindex = strpos($lowstr, ' left join ', $fromindex);
if ($whereindex <= 0) {
$whereindex = strpos($lowstr, ' where ');
}
if ($whereindex <= 0) {
$whereindex = strpos($lowstr, ' order ', $fromindex);
}
if ($whereindex <= 0) {
$whereindex = strpos($lowstr, ' group ', $fromindex);
}
if ($whereindex <= 0) {
$whereindex = $limitindex;
}
$tableastr = substr($sql, $fromindex + 6, $whereindex - $fromindex - 6);
$tmp_table = explode(',', $tableastr);
for ($i = 0; $i < count($tmp_table); $i++) {
$tmp_strs = explode('.', str_replace('`', '', $tmp_table[$i]));
if (count($tmp_strs) >= 2) {
$tmp_table[$i] = $tmp_strs[1];
} else {
$tmp_table[$i] = $tmp_strs[0];
}
}
if ($orderindex && $orderindex > 0) {
$byindex = strpos($lowstr, ' by ', $orderindex);
$orderby = substr($sql, $byindex + 4, $limitindex - $byindex - 4);
} else {
if (strpos($tmp_table[0], MS_DBPRE) <= 0) {
$tmp_table[0] = MS_DBPRE . $tmp_table[0];
}
$result = self::$link[$host]->query('exec sp_pkeys ' . explode(' ', $tmp_table[0])[0]);
$keys = [];
while ($tmp = $result->fetch(PDO::FETCH_OBJ)) {
$keys[] = $tmp->COLUMN_NAME;
}
$orderby = implode(',', $keys);
}
$selectindex = strpos($lowstr, 'select ');
if ($whereindex <= 0) {
$sql = substr($sql, 0, $fromindex + 6) . implode(',', $tmp_table);
} else {
$sql = substr($sql, 0, $fromindex + 6) . implode(',', $tmp_table) . substr($sql, $whereindex);
}
$limitindex = strpos(strtolower($sql), ' limit ');
$sql = substr($sql, $selectindex + 7, $limitindex - $selectindex - 7);
if (count($limitnum) > 1) {
$start = intval($limitnum[0]);
$len = intval($limitnum[1]);
$end = $start + $len;
$sql = 'select * from ( SELECT TOP ' . $end . ' row_number() OVER (order by ' . $orderby . ') rownum, ' . $sql . ')tb WHERE tb.rownum > ' . $start . ' ORDER BY tb.rownum ASC ';
} else {
$start = 0;
$end = intval($limitnum[0]);
$sql = ' SELECT TOP ' . $end . ' ' . $sql . ' ';
}
}
//处理 concat
$sql = str_replace('FROM_UNIXTIME(', '(', $sql);
$sql = str_replace('HOUR(', 'datepart(hour,', $sql);
$sql = str_replace('!(', 'not(', $sql);
$query = self::$link[$host]->query($sql);
if (C('debug')) {
addUpTime('queryEndTime');
}
if ($query === false) {
$error = 'Db Error: ' . self::$link[$host]->errorInfo();
if (C('debug')) {
throw_exception($error . '<br/>' . $sql);
} else {
Log::record($error . "\r\n" . $sql, Log::ERR);
return false;
}
} else {
Log::record($sql . " [ RunTime:" . addUpTime('queryStartTime', 'queryEndTime', 6) . "s ]", Log::SQL);
return $query;
}
}
