裑軆ケ喥 – 第 2 页

网络安全设备

是否具备测试环境进行功能性验证；
设备接入方式为旁路还是串联；
设备能否支持 Bypass 或者 PoE 功能；
设备是否与其它设备有联动机制；
设备离线会不会触发其它系统上配置的策略；
没有升级经验的情况下，设备版本升级最好由原厂工程师进行或提供手册；
没有测试环境的情况下，能升级特征库解决的问题最好就不要升级版本；
拟定回退方案；在不支持升级回退的情况下提前准备备机；
提前做好配置及数据的备份；
准备升级过程中要用到的工具；
升级完成后进行功能验证。

查看 MSSQL 的审计日志

发表于: 2020-03-11 by 裑軆ケ喥

-- 注意：event_time 使用的是 UTC 时间，所以要加上当地时区的差值

SELECT

CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, f.event_time), DATENAME(TZoffset, SYSDATETIMEOFFSET()))) AS event_time,

f.sequence_number,

f.action_id,

a.name AS action_name,

f.succeeded,

f.session_server_principal_name,

f.server_principal_name,

f.database_name,

f.object_id,

f.schema_name,

f.object_name,

f.class_type,

m.class_type_desc,

f.statement

FROM sys.fn_get_audit_file('E:\sql_audit\*',default,default) f

INNER JOIN sys.dm_audit_actions a ON f.action_id=a.action_id

INNER JOIN sys.dm_audit_class_type_map m ON f.class_type=m.class_type

WHERE f.object_name='news_info'

AND CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, f.event_time), DATENAME(TZoffset, SYSDATETIMEOFFSET())))>'2020-03-11 12:00:00'

ORDER BY f.event_time;

-- 或者

SELECT *

FROM sys.fn_get_audit_file('E:\sql_audit\*',default,default) f

WHERE f.object_name='news_info'

ORDER BY f.event_time;

一动一静养正气

发表于: 2020-03-092021-06-14 by 裑軆ケ喥

前言本文内容来源于北京卫视“养生堂”节目；本文内容仅供参考、请勿过于克制，结论来源于生活但不一定是100%正确；不同场景不同患者适用程度不同，请谨慎采纳以下内容、博主不对本文内容承担任何责任。阅读全文

NOT IN 方式查询

SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T2.points IS NULL OR T2.points = 0 )     -- 条件记录 3870358 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '')                         -- 条件记录 6286655 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T1.create_date <= '2020-03-01 00:00:00'  -- 条件记录 6731569 条
  -- 查询语句一耗时 7s, 返回记录 150681 条
UNION 
SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '' )                        -- 条件记录 6286656 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T1.uid NOT IN
    (
      SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 225 条
      SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 271 条
      SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 221 条
      SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 388 条
      SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 373 条
      SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 5986 条
      SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 23079 条
      SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id =1        -- 子查询记录 24095 条
       -- 子查询总耗时 1m14s, 返回记录 28411 条
    )  -- 条件记录 6711840 条
  -- 查询语句二耗时 1h13m52s, 返回记录 191067 条
ORDER BY
    T1.region,
    T1.streets,
    T1.village,
    T1.organize;
-- 总耗时 1h15m35s, 返回记录 191067 条

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T2.points IS NULL OR T2.points = 0 ) -- 条件记录 3870358 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '') -- 条件记录 6286655 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T1.create_date <= '2020-03-01 00:00:00' -- 条件记录 6731569 条

-- 查询语句一耗时 7s, 返回记录 150681 条

UNION

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '' ) -- 条件记录 6286656 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T1.uid NOT IN

(

SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 225 条

SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 271 条

SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 221 条

SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 388 条

SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 373 条

SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 5986 条

SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 23079 条

SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id =1 -- 子查询记录 24095 条

-- 子查询总耗时 1m14s, 返回记录 28411 条

) -- 条件记录 6711840 条

-- 查询语句二耗时 1h13m52s, 返回记录 191067 条

ORDER BY

T1.region,

T1.streets,

T1.village,

T1.organize;

-- 总耗时 1h15m35s, 返回记录 191067 条

LEFT JOIN 方式查询

SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T2.points IS NULL OR T2.points = 0 )     -- 条件记录 3870358 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '')                         -- 条件记录 6286655 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T1.create_date <= '2020-03-01 00:00:00'  -- 条件记录 6731569 条
  -- 查询语句一耗时 7s, 返回记录 150681 条
UNION 
SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  LEFT JOIN
    (
      SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 225 条
      SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 271 条
      SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 221 条
      SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 388 条
      SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 373 条
      SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 5986 条
      SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 23079 条
      SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id=1        -- 子查询记录 24095 条
        -- 子查询耗时 1m14s, 返回记录 28411 条
    ) AS T3 ON T3.account_code=T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '' )                        -- 条件记录 6286656 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T3.id IS NULL                            -- 条件记录 6711856 条
ORDER BY
    T1.region,
    T1.streets,
    T1.village,
    T1.organize;
-- 总耗时 1m46s, 返回记录 191067 条

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T2.points IS NULL OR T2.points = 0 ) -- 条件记录 3870358 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '') -- 条件记录 6286655 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T1.create_date <= '2020-03-01 00:00:00' -- 条件记录 6731569 条

-- 查询语句一耗时 7s, 返回记录 150681 条

UNION

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

LEFT JOIN

(

SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 225 条

SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 271 条

SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 221 条

SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 388 条

SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 373 条

SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 5986 条

SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 23079 条

SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id=1 -- 子查询记录 24095 条

-- 子查询耗时 1m14s, 返回记录 28411 条

) AS T3 ON T3.account_code=T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '' ) -- 条件记录 6286656 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T3.id IS NULL -- 条件记录 6711856 条

ORDER BY

T1.region,

T1.streets,

T1.village,

T1.organize;

-- 总耗时 1m46s, 返回记录 191067 条

总结

UNION 与 WHERE 语句应用将返回记录小的条件最先进行匹配，以小表的记录去遍历大表耗时更短；
本次查询中 NOT IN 与 NOT EXISTS 耗时基本相同，由于查询字段存在唯一值、故无法通过索引优化；所以 NOT EXISTS 方式没有明显优势。

密码保护：Linux 系统检查用

发表于: 2020-02-282022-06-16 by 裑軆ケ喥

MySQL 存储过程示例

发表于: 2020-02-282021-05-10 by 裑軆ケ喥

DROP PROCEDURE IF EXISTS order_summary_month;
DELIMITER //
CREATE PROCEDURE order_summary_month()
BEGIN
  DECLARE m datetime DEFAULT '2019-01-01';
  WHILE ( DATE_FORMAT(m,'%Y-%m') <= DATE_FORMAT(NOW(),'%Y-%m') )
    DO
    IF NOT EXISTS
      (
        SELECT insertdate
        FROM app.order_summary
        WHERE statdate=DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1'
      )
      THEN
        INSERT INTO app.order_summary
          (
            statdate,
            time_unit,
            type,
            cnt_order,
            sum_price,
            cnt_weight,
            insertdate
          )
        VALUES
          (
            DATE_FORMAT(m,'%Y-%m'),
            3,
            1,
            ( 
              SELECT COUNT(order_sn) AS cnt_order
              FROM app.wx_order
              WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                    AND type='1'
            ),
            ( 
              SELECT SUM(price) AS sum_price
              FROM app.wx_order
              WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                    AND type='1'
                    AND gains_type='1'
            ),
            ( 
              SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight
              FROM app.wx_order O
              LEFT JOIN app.wx_order_goods G on O.id=G.order_id
              LEFT JOIN app.wx_goods_class C on G.goods_id=C.id
              WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                    AND O.type='1'
            ),
            NOW()
          );
-- /*      --不更新旧数据则可以跳过
        ELSE
          UPDATE app.wx_order_summary
            SET
              time_unit = 3,
              type = 1,
              cnt_order =
              ( 
                SELECT COUNT(order_sn) AS cnt_order
                FROM app.wx_order
                WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                      AND type='1'
              ),
              sum_price =
              ( 
                SELECT SUM(price) AS sum_price
                FROM app.wx_order
                WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                      AND type='1'
                      AND gains_type='1'
              ),
              cnt_weight =
              ( 
                SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight
                FROM app.wx_order O
                LEFT JOIN app.wx_order_goods G on O.id=G.order_id
                LEFT JOIN app.wx_goods_class C on G.goods_id=C.id
                WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                      AND O.type='1'
              ),
              insertdate = NOW()
            WHERE statdate = DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1';
-- */      -- 不更新旧数据则可以跳过
    END IF;
    COMMIT;
    SET m = DATE_SUB(m, INTERVAL -1 MONTH);
  END WHILE;
END; //
DELIMITER ;
CALL order_summary_month();

DROP PROCEDURE IF EXISTS order_summary_month;

DELIMITER //

CREATE PROCEDURE order_summary_month()

BEGIN

DECLARE m datetime DEFAULT '2019-01-01';

WHILE ( DATE_FORMAT(m,'%Y-%m') <= DATE_FORMAT(NOW(),'%Y-%m') )

IF NOT EXISTS

(

SELECT insertdate

FROM app.order_summary

WHERE statdate=DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1'

)

THEN

INSERT INTO app.order_summary

(

statdate,

time_unit,

type,

cnt_order,

sum_price,

cnt_weight,

insertdate

)

VALUES

(

DATE_FORMAT(m,'%Y-%m'),

(

SELECT COUNT(order_sn) AS cnt_order

FROM app.wx_order

WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

(

SELECT SUM(price) AS sum_price

FROM app.wx_order

WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

AND gains_type='1'

(

SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight

FROM app.wx_order O

LEFT JOIN app.wx_order_goods G on O.id=G.order_id

LEFT JOIN app.wx_goods_class C on G.goods_id=C.id

WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND O.type='1'

NOW()

);

-- /* --不更新旧数据则可以跳过

ELSE

UPDATE app.wx_order_summary

SET

time_unit = 3,

type = 1,

cnt_order =

(

SELECT COUNT(order_sn) AS cnt_order

FROM app.wx_order

WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

sum_price =

(

SELECT SUM(price) AS sum_price

FROM app.wx_order

WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

AND gains_type='1'

cnt_weight =

(

SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight

FROM app.wx_order O

LEFT JOIN app.wx_order_goods G on O.id=G.order_id

LEFT JOIN app.wx_goods_class C on G.goods_id=C.id

WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND O.type='1'

insertdate = NOW()

WHERE statdate = DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1';

-- */ -- 不更新旧数据则可以跳过

END IF;

COMMIT;

SET m = DATE_SUB(m, INTERVAL -1 MONTH);

END WHILE;

END; //

DELIMITER ;

CALL order_summary_month();

MSSQL 查询所有表结构

发表于: 2020-02-272021-05-10 by 裑軆ケ喥

SELECT
  表名       = CASE WHEN a.colorder = 1
                 THEN d.name
                 ELSE ''
               END,
  表描述     = CASE WHEN a.colorder = 1
                 THEN ISNULL(f.[value], '')
                 ELSE ''
               END,
  字段序号   = a.colorder,
  字段名称   = a.name,
  类型       = b.name,
  长度       = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
  小数位数   = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'),0),
  允许为空   = CASE WHEN a.isnullable = 1
                 THEN '√'
                 ELSE ''
               END,
  主键       = CASE WHEN EXISTS(
                 SELECT 1 FROM sysobjects
                 WHERE xtype      = 'PK'
                   AND parent_obj = a.id
                   AND name IN(
                     SELECT name FROM sysindexes
                     WHERE indid IN(
                         SELECT indid FROM sysindexkeys
                         WHERE id    = a.id
                           AND colid = a.colid
                     )
                   )
               )
                 THEN '√'
                 ELSE ''
               END,
  默认值     = ISNULL(e.text, ''),
  占用字节数 = a.length,
  标识       = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1
                 THEN '√'
                 ELSE ''
               END,
  字段描述   = ISNULL(g.[value], '')
FROM       syscolumns              a
LEFT JOIN  systypes                b ON  a.xusertype =  b.xusertype
INNER JOIN sysobjects              d ON  a.id        =  d.id
                                     AND d.xtype     =  'U'
                                     AND d.name      <> 'dtproperties'
LEFT JOIN  syscomments             e ON  a.cdefault  =  e.id
LEFT JOIN  sys.extended_properties g ON  a.id        =  g.major_id
                                     AND a.colid     =  g.minor_id
LEFT JOIN  sys.extended_properties f ON  d.id        =  f.major_id
                                     AND f.minor_id  =  0
WHERE b.name IS NOT NULL          -- AND d.name='要查询的表'
                                  -- 如果只查询表,加上 AND 条件
ORDER BY   d.name, a.colorder;

-- 验证表数量，避免空表或表中没有ID为1的记录
SELECT name FROM sysobjects WHERE xtype='U';

SELECT

表名 = CASE WHEN a.colorder = 1

THEN d.name

ELSE ''

END,

表描述 = CASE WHEN a.colorder = 1

THEN ISNULL(f.[value], '')

ELSE ''

END,

字段序号 = a.colorder,

字段名称 = a.name,

类型 = b.name,

长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),

小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'),0),

允许为空 = CASE WHEN a.isnullable = 1

THEN '√'

ELSE ''

END,

主键 = CASE WHEN EXISTS(

SELECT 1 FROM sysobjects

WHERE xtype = 'PK'

AND parent_obj = a.id

AND name IN(

SELECT name FROM sysindexes

WHERE indid IN(

SELECT indid FROM sysindexkeys

WHERE id = a.id

AND colid = a.colid

)

THEN '√'

ELSE ''

END,

默认值 = ISNULL(e.text, ''),

占用字节数 = a.length,

标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1

THEN '√'

ELSE ''

END,

字段描述 = ISNULL(g.[value], '')

FROM syscolumns a

LEFT JOIN systypes b ON a.xusertype = b.xusertype

INNER JOIN sysobjects d ON a.id = d.id

AND d.xtype = 'U'

AND d.name <> 'dtproperties'

LEFT JOIN syscomments e ON a.cdefault = e.id

LEFT JOIN sys.extended_properties g ON a.id = g.major_id

AND a.colid = g.minor_id

LEFT JOIN sys.extended_properties f ON d.id = f.major_id

AND f.minor_id = 0

WHERE b.name IS NOT NULL -- AND d.name='要查询的表'

-- 如果只查询表,加上 AND 条件

ORDER BY d.name, a.colorder;

-- 验证表数量，避免空表或表中没有ID为1的记录

SELECT name FROM sysobjects WHERE xtype='U';

MySQL 问题汇总

发表于: 2020-02-272021-05-10 by 裑軆ケ喥

ERROR 1146 (42S02): Table 'mysql.servers' doesn't exist

执行以下 SQL 语句删除并重新创建 mysql.servers 表

USE mysql

DROP TABLE IF EXISTS servers;

CREATE TABLE `servers` (

`Server_name` char(64) NOT NULL DEFAULT '',

`Host` char(64) NOT NULL DEFAULT '',

`Db` char(64) NOT NULL DEFAULT '',

`Username` char(64) NOT NULL DEFAULT '',

`Password` char(64) NOT NULL DEFAULT '',

`Port` int(4) NOT NULL DEFAULT '0',

`Socket` char(64) NOT NULL DEFAULT '',

`Wrapper` char(64) NOT NULL DEFAULT '',

`Owner` char(64) NOT NULL DEFAULT '',

PRIMARY KEY (`Server_name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='MySQL Foreign Servers table';

CREATE USER report01@'192.168.100.20';

GRANT SELECT ON *.* TO 'report02'@'192.168.100.20' IDENTIFIED BY "Password";

FLUSH PRIVILEGES;

试水-阿里云大使

发表于: 2020-01-19 by 裑軆ケ喥

阿里云9折折扣码

aliyun-9

重新生成或重新组织索引

发表于: 2020-01-142021-05-10 by 裑軆ケ喥

-- 返回有关 SQL Server 实例中所有表和索引的所有统计信息.
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) where avg_fragmentation_in_percent>10;

-- 查看库的 database_id 值.
SELECT DB_ID ('数据库名');

-- 确保已执行USE <databasename> 语句.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);

-- 从sys.dm_db_index_physical_stats函数中筛选表和索引，并将对象和索引ID转换为名称.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- 向游标声明要处理的分区列表。
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- 开启游标.
OPEN partitions;

-- 循环调用分区。
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30是在 REORGANIZE 和 REBUILD 之间切换的判断条件.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- 关闭并释放游标.
CLOSE partitions;
DEALLOCATE partitions;

-- 删除临时表.
DROP TABLE #work_to_do;
GO

-- 返回有关 SQL Server 实例中所有表和索引的所有统计信息.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) where avg_fragmentation_in_percent>10;

-- 查看库的 database_id 值.

SELECT DB_ID ('数据库名');

-- 确保已执行USE <databasename> 语句.

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

-- 从sys.dm_db_index_physical_stats函数中筛选表和索引，并将对象和索引ID转换为名称.

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- 向游标声明要处理的分区列表。

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- 开启游标.

OPEN partitions;

-- 循环调用分区。

WHILE (1=1)

BEGIN;

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30是在 REORGANIZE 和 REBUILD 之间切换的判断条件.

IF @frag < 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N'Executed: ' + @command;

END;

-- 关闭并释放游标.

CLOSE partitions;

DEALLOCATE partitions;

-- 删除临时表.

DROP TABLE #work_to_do;

Desen往事

简单生活，知足常乐

作者：裑軆ケ喥

系统升级事项及建议

网络安全设备

查看 MSSQL 的审计日志

一动一静养正气

MSSQL 查询之 NOT IN & LEFT JOIN

NOT IN 方式查询

LEFT JOIN 方式查询

总结

密码保护：Linux 系统检查用

MySQL 存储过程示例

MSSQL 查询所有表结构

MySQL 问题汇总

试水-阿里云大使

重新生成或重新组织索引