{"id":42,"date":"2024-03-30T20:16:55","date_gmt":"2024-03-30T12:16:55","guid":{"rendered":"http:\/\/www.shifengzhi.asia\/?p=42"},"modified":"2024-03-30T20:16:55","modified_gmt":"2024-03-30T12:16:55","slug":"mysql%e6%95%b0%e6%8d%ae%e5%ba%93%e5%91%bd%e4%bb%a4%e6%80%bb%e7%bb%93","status":"publish","type":"post","link":"https:\/\/boki.shifengzhi.top\/?p=42","title":{"rendered":"MySQL\u6570\u636e\u5e93\u547d\u4ee4\u603b\u7ed3"},"content":{"rendered":"

1\u5b57\u7b26\u96c6\u3001\u7cfb\u7edf\u53d8\u91cf<\/h2>\n
    \n
  1. \n

    \u67e5\u770bMYSQL\u6240\u652f\u6301\u7684\u5b57\u7b26\u96c6show charset;show character set\uff1b<\/p>\n<\/li>\n

  2. \n

    \u67e5\u770blatin1\u5b57\u7b26\u96c6\u7684\u5b57\u7b26\u5e8f\uff08\u6821\u5bf9\u89c4\u5219\uff09\uff1ashow collation like \u2018Latin1%\u2019\uff1b<\/p>\n<\/li>\n

  3. \n

    \u67e5\u770b\u670d\u52a1\u5668\u5b57\u7b26\u96c6
    \nshow variables like 'character_set_server';
    \n\u67e5\u770b\u670d\u52a1\u5668\u6821\u5bf9\u89c4\u5219
    \nshow variables like 'collation_server';<\/p>\n

    \u67e5\u770b\u6570\u636e\u5e93\u7684\u5b57\u7b26\u96c6\uff1a
    \nshow variables like 'character%'\uff1b
    \nshow create database shiyan\\G;\uff08\u96c6\u548c\u89c4\u5219\uff09
    \nstatus\uff1b\uff08use \u5e93\u540d\uff1b\u4e4b\u540e\u518d\u4f7f\u7528\u8be5\u547d\u4ee4\uff09<\/p>\n<\/li>\n

  4. \n

    \u67e5\u770b\u5ba2\u6237\u7aef\u4f7f\u7528\u7684\u5b57\u7b26\u96c6
    \nshow variables like 'character_set_client';<\/p>\n<\/li>\n

  5. \n

    \u67e5\u770b\u8fde\u63a5\u5c42\u5b57\u7b26\u96c6
    \nshow variables like 'character_set_connection';<\/p>\n<\/li>\n

  6. \n

    \u67e5\u770bMySQL\u67e5\u8be2\u7ed3\u679c\u5b57\u7b26\u96c6
    \nshow variables like 'character_set_results';<\/p>\n<\/li>\n

  7. \n

    \u67e5\u770b\u8868\u7684\u5b57\u7b26\u96c6show table status from \u5e93\u540d like \u2018\u8868\u540d\u2019;<\/p>\n<\/li>\n

  8. \n

    \u67e5\u770b\u8868\u4e2d\u6240\u6709\u5217\u7684\u5b57\u7b26\u96c6\uff1a show full columns from \u8868<\/p>\n<\/li>\n

  9. \n

    \u663e\u793a\u7cfb\u7edf\u53d8\u91cf\uff1a\u5f53\u524d\u7248\u672c\u548c\u7cfb\u7edf\u65f6\u95f4
    \nselect @@version,current_date;<\/p>\n<\/li>\n

  10. \n
      \n
    1. \u7cfb\u7edf\u53d8\u91cf\uff1a\u914d\u7f6eMySQL\u670d\u52a1\u5668\u7684\u8fd0\u884c\u73af\u5883\uff0c\u53ef\u4ee5\u7528show variables\u67e5\u770b<\/li>\n
    2. \u72b6\u6001\u53d8\u91cf\uff1a\u76d1\u63a7MySQL\u670d\u52a1\u5668\u7684\u8fd0\u884c\u72b6\u6001\uff0c\u53ef\u4ee5\u7528show status\u67e5\u770b<\/li>\n
    3. \u67e5\u770bmysql\u7684\u6240\u6709\u5168\u5c40\u53d8\u91cf\u7684\u503c SHOW GLOBAL VARIABLES \u6216\u8005 SHOW VARIABLES<\/li>\n
    4. \u67e5\u770bmysql\u7684\u5355\u4e2a\u5168\u5c40\u53d8\u91cf\u7684\u503c SHOW GLOBAL VARIABLES LIKE 'wait_timeout' \u6216\u8005 SELECT @@wait_timeout
      \n\u4e0a\u9762\u7684\u67e5\u8be2\u4e5f\u53ef\u4ee5\u7528\u6a21\u7cca\u67e5\u8be2\uff0c\u4e0e\u4e0a\u9762\u7ed3\u679c\u4e00\u6837\u7684\u6a21\u7cca\u67e5\u8be2\u8bed\u53e5
      \nSHOW GLOBAL VARIABLES LIKE 'wait_time%'
      \nSHOW GLOBAL VARIABLES LIKE '%wait%'
      \n\u6ce8\u610f:\u67e5\u770bmysql\u7684\u5168\u5c40\u53d8\u91cf\u4e5f\u53ef\u4ee5\u4e0d\u52a0global\u5173\u952e\u5b57\uff0c\u4f8b\u5982 SHOW VARIABLES
      \nSHOW VARIABLES LIKE '%wait%'
      \nSHOW VARIABLES LIKE 'wait_timeout'
      \nSHOW VARIABLES LIKE '%wait%'<\/li>\n
    5. \u8bbe\u7f6e\u5168\u5c40\u53d8\u91cf\u7684\u503c
      \nSET GLOBAL wait_timeout = 604800;
      \nSET @@GLOBAL.wait_timeout = 604800
      \nSET GLOBAL event_scheduler = ON; \uff08\u4e8b\u4ef6\u8c03\u5ea6\u5668\uff0c\u67e5\u770b\u4e8b\u4ef6\u662f\u5426\u5f00\u542f\uff09
      \nSET @@global.event_scheduler = ON;
      \nSET GLOBAL event_scheduler = 1;
      \nSET @@global.event_scheduler = 1;
      \n\u6ce8\u610f:\u6709\u7684\u65f6\u5019\u53d1\u73b0\u4fee\u6539\u4e0d\u751f\u6548\uff0c\u9700\u8981\u5173\u95ed\u4f1a\u8bdd\u91cd\u65b0\u542f\u52a8\uff0c\u4e5f\u5c31\u662f\u9000\u51fa\u4e00\u4e0b\u91cd\u65b0\u767b\u5f55\u3002\u4f46\u662f\u53d1\u73b0\u91cd\u65b0\u542f\u52a8mysql\u670d\u52a1\u5668\u53d8\u91cf\u53c8\u4e0d\u751f\u6548\uff0c\u6700\u597d\u7684\u529e\u6cd5\u5c31\u662f\u4fee\u6539mysql\u542f\u52a8\u7684\u9ed8\u8ba4\u503c\u3002
      \n(1)windows\u4e0bC:\\ProgramData\\MySQL\\MySQL Server 5.7\\my.ini\uff0c\u5728[mysqld]\u6808\u4e0b\u5199\u4e0b\u8fd9\u4e2a\u53d8\u91cf,\u4f8b\u5982\u4fee\u6539mysql\u542f\u52a8\u540e\u9ed8\u8ba4\u5f00\u542f\u4efb\u52a1\u8c03\u5ea6(windows\u662f\u4fee\u6539my.ini\u6587\u4ef6)<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

      \u4fee\u6539:C:\\ProgramData\\MySQL\\MySQL Server 5.7\\my.ini (\u6ce8\u610f\u662fProgramData\u76ee\u5f55\u4e0b\u7684my.ini\u6587\u4ef6\u624d\u80fd\u751f\u6548\uff0c\u5982\u679c\u4fee\u6539C:\\Program Files\\MySQL\\MySQL Server 5.7\\my-default.ini\u4e0d\u4f1a\u751f\u6548)<\/p>\n

      \u9000\u51famysql exit\/quit<\/p>\n

      2\u6570\u636e\u5e93\u547d\u4ee4<\/h2>\n
        \n
      1. \u521b\u5efa\u6570\u636e\u5e93\uff1acreate database(schema) \u5e93\u540d <\/li>\n
      2. \u4fee\u6539\u6570\u636e\u5e93\uff1aalter database \u5e93\u540d character set \u5b57\u7b26\u96c6\u540d
        \ncollate \u5b57\u7b26\u6821\u5bf9\u89c4\u5219\uff1b<\/li>\n
      3. \u663e\u793a\u6570\u636e\u5e93\uff1ashow databases\uff1b<\/li>\n
      4. \u67e5\u770b\u6570\u636e\u5e93\u5b57\u7b26\u96c6\uff1ashow variables like 'character%'<\/li>\n
      5. \u5220\u9664\u6570\u636e\u5e93\uff1adrop database \u5e93\u540d\uff1b<\/li>\n
      6. \u663e\u793a\u6570\u636e\u5e93\u7ed3\u6784\uff1ashow create database \u5e93\u540d\uff1b<\/li>\n
      7. \u6253\u5f00\u6570\u636e\u5e93\uff1ause \u5e93\u540d\uff1b<\/li>\n
      8. \u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u7684\u540d\u5b57\uff1aselect database();
        \n\u590d\u4e60\uff1ashow charset;\u6216\u8005 show character set\uff1b<\/li>\n<\/ol>\n

        \u7406\u8bba\uff1a<\/p>\n

          \n
        1. \u5e93\u540d\u7684\u547d\u540d\u89c4\u5219\uff1a\n
            \n
          1. \u4e0d\u80fd\u4e0e\u5176\u4ed6\u6570\u636e\u5e93\u91cd\u540d\uff0c\u5426\u5219\u5c06\u53d1\u751f\u9519\u8bef\u3002<\/li>\n
          2. \u540d\u79f0\u53ef\u4ee5\u7531\u4efb\u610f\u5b57\u6bcd\u3001\u963f\u62c9\u4f2f\u6570\u5b57\u3001\u4e0b\u5212\u7ebf\uff08_\uff09\u548c\u201c$\u201d\u7ec4\u6210\uff0c\u53ef\u4ee5\u4f7f\u7528\u4e0a\u8ff0\u7684\u4efb\u610f\u5b57\u7b26\u5f00\u5934\uff0c\u4f46\u4e0d\u80fd\u4f7f\u7528\u5355\u72ec\u7684\u6570\u5b57\uff0c\u5426\u5219\u4f1a\u9020\u6210\u5b83\u4e0e\u6570\u503c\u76f8\u6df7\u6dc6\u3002<\/li>\n
          3. \u540d\u79f0\u6700\u957f\u53ef\u4e3a64\u4e2a\u5b57\u7b26\uff0c\u800c\u522b\u540d\u6700\u591a\u53ef\u957f\u8fbe256\u4e2a\u5b57\u7b26<\/li>\n
          4. \u4e0d\u80fd\u4f7f\u7528MySQL\u5173\u952e\u5b57\u4f5c\u4e3a\u6570\u636e\u5e93\u540d\u3001\u8868\u540d\u3002<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

            3\u5b58\u50a8\u5f15\u64ce<\/h2>\n
              \n
            1. \n

              \u67e5\u770bmysql\u73b0\u5728\u5df2\u63d0\u4f9b\u4ec0\u4e48\u5b58\u50a8\u5f15\u64ce:
              \nmysql> show engines;<\/p>\n<\/li>\n

            2. \n

              \u67e5\u770b\u4f60mysql\u5f53\u524d\u9ed8\u8ba4\u7684\u5b58\u50a8\u5f15\u64ce:
              \nmysql> show variables like '%storage_engine%';<\/p>\n<\/li>\n

            3. \n

              \u67e5\u770b\u4e2a\u8868\u7528\u7684\u5b58\u50a8\u5f15\u64ce(\u5728\u663e\u793a\u7ed3\u679c\u91cc\u53c2\u6570engine\u540e\u9762\u7684\u5c31\u8868\u793a\u8be5\u8868\u5f53\u524d\u7528\u7684\u5b58\u50a8\u5f15\u64ce):
              \nmysql> show create table \u8868\u540d;<\/p>\n<\/li>\n

            4. \n

              \u4fee\u6539\u8868\u5f15\u64ce\u65b9\u6cd5
              \nalter table \u8868\u540d engine=innodb;<\/p>\n<\/li>\n

            5. \n

              \u5728\u521b\u5efa\u8868\u4e4b\u524d\u4f7f\u7528\u4e0b\u9762\u547d\u4ee4\u8bbe\u7f6e\u5b58\u50a8\u5f15\u64ce\uff0c\u4e4b\u540e\u521b\u5efa\u7684\u8868\u5c31\u662f\u8be5\u5f15\u64ce
              \nset default_storage_engine=MyISAM;<\/p>\n<\/li>\n

            6. \n

              \u521b\u5efa\u8868
              \ncreate table ld
              \n(id int not null);<\/p>\n<\/li>\n

            7. \n

              show databases;<\/p>\n<\/li>\n

            8. \n

              show engines;<\/p>\n<\/li>\n

            9. \n

              create database student;<\/p>\n<\/li>\n

            10. \n

              create database teacher;<\/p>\n<\/li>\n

            11. \n

              drop database teacher;<\/p>\n<\/li>\n

            12. \n

              show databases;<\/p>\n<\/li>\n<\/ol>\n

              4\u8868\u7a7a\u95f4\u548c\u8868\u7684\u521b\u5efa\u3001\u4fee\u6539<\/h2>\n
                \n
              1. \n

                \u53ef\u4ee5\u67e5\u770b\u6570\u636e\u5e93\u7684\u8868\u7a7a\u95f4
                \nshow variables like 'innodb_data%';<\/p>\n<\/li>\n

              2. \n

                \u67e5\u770b\u76ee\u524d\u5c5e\u4e8e\u54ea\u79cd\u8868\u7a7a\u95f4
                \nshow variables like '%per_table%';
                \n\u5171\u4eab\u8868\u7a7a\u95f4\u201cinnodb_file_per_table\u201d\u4f1a\u663e\u793a\u4e3aoff\u3002
                \n\u72ec\u7acb\u8868\u7a7a\u95f4\u201cinnodb_file_per_table\u201d\u4f1a\u663e\u793a\u4e3aon\u3002
                \nshow variables like 'Innodb_file_per_table';
                \nInnodb_file_per_table=1 \u72ec\u5360\u8868\u7a7a\u95f4
                \nInnodb_file_per_table=0 \u5171\u4eab\u8868\u7a7a\u95f4<\/p>\n<\/li>\n

              3. \n

                \u521b\u5efa\u8868(\u5982\u679c\u6ca1\u6709\u8bbe\u7f6e\u4e0d\u53ef\u4e3a\u7a7a\uff0c\u9ed8\u8ba4\u53ef\u4ee5\u4e3a\u7a7a\uff09
                \ncreate table \u8868\u540d\uff08\u5217\u540d \u6570\u636e\u7c7b\u578b not null\uff09\uff1b
                \n\u6848\u4f8b\uff1a
                \ncreate table if not exists student632(
                \nstudentno char(11) not null comment'\u5b66\u53f7',
                \nsname char(8) not null comment'\u59d3\u540d',
                \nsex enum('\u7537','\u5973') default '\u7537' comment'\u6027\u522b',
                \nbirthdate date not null comment'\u51fa\u751f\u65e5\u671f',
                \nentrance int(3) null comment'\u5165\u5b66\u6210\u7ee9',
                \nphone varchar(12) not null comment'\u7535\u8bdd',
                \nemail varchar(20) not null comment'\u7535\u5b50\u90ae\u7bb1',
                \nprimary key(studentno)
                \n);
                \n\u7279\u4f8b\uff1a
                \ndaily float(3,1) default 0
                \nprimary key(studentno,courseno)
                \nsc_no int(6) not null auto_increment \u81ea\u52a8\u589e\u91cf
                \nsc_time timestamp not null default now()<\/p>\n<\/li>\n

              4. \n

                \u67e5\u770b\u8868\u7ed3\u6784
                \ndesc+\u8868\u540d
                \nshow create table \u8868\u540d\uff1b\u53ef\u4ee5\u67e5\u770b\u8868\u7ed3\u6784\u3001\u5b57\u7b26\u96c6\u3001\u5b58\u50a8\u5f15\u64ce\u3002<\/p>\n<\/li>\n

              5. \n

                \u663e\u793a\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u6240\u6709\u8868
                \nshow tables\uff1b<\/p>\n<\/li>\n

              6. \n

                \u4fee\u6539\u8868<\/p>\n

                  \n
                1. \u65b0\u589e\u4e00\u5217\uff0c\u653e\u7f6e\u5728\u5df2\u6709\u5217\u540d\u4e4b\u540e\u6216\u7b2c\u4e00\u5217\u4e3a\u65b0\u589e\u5217\u3002
                  \nalter table \u8868\u540d add \u65b0\u5217\u540d \u6570\u636e\u7c7b\u578b \u662f\u5426\u4e3a\u7a7a after \u5df2\u6709\u5217\u540d\uff1b
                  \nalter table \u8868\u540d add \u65b0\u5217\u540d \u6570\u636e\u7c7b\u578b \u662f\u5426\u4e3a\u7a7a first\uff1b<\/li>\n
                2. \u8868\u91cd\u547d\u540d\u3002
                  \nalter table \u539f\u8868\u540d rename to \u65b0\u8868\u540d\uff1b<\/li>\n
                3. \u4fee\u6539\u6570\u636e\u7c7b\u578b
                  \nalter table \u8868\u540d modify \u5217\u540d \u65b0\u6570\u636e\u7c7b\u578b \u9ed8\u8ba4\u503c\u7b49\uff08first\u6216after \u5217\u540d\uff09\uff1b<\/li>\n
                4. \u5220\u9664\u67d0\u5217
                  \nalter table \u8868\u540d drop \u5217\u540d\uff1b<\/li>\n
                5. \u5220\u9664\u8868
                  \ndrop table \u8868\u540d\uff1b<\/li>\n
                6. \u4fee\u6539\u5b57\u6bb5\u9ed8\u8ba4\u503c\uff08\u4fee\u6539\u6027\u522b\u5217\u9ed8\u8ba4\u503c\u4e3a\u5973\uff09
                  \nalter table mytest alter sex set default '\u5973';
                  \n\u5220\u9664\u5b57\u6bb5\u7684\u9ed8\u8ba4\u503c
                  \nalter table mytest alter sex drop default;<\/li>\n
                7. \u91cd\u547d\u540d\u5217\u540dalter
                  \nalter table \u8868\u540d change \u539f\u5217\u540d \u65b0\u5217\u540d \u65b0\u5217\u6570\u636e\u7c7b\u578b \uff08first\u6216after \u5217\u540d\uff09;<\/li>\n<\/ol>\n<\/li>\n
                8. \n

                  \u4e34\u65f6\u8868<\/p>\n

                    \n
                  1. \u521b\u5efa\u4e34\u65f6\u8868
                    \ncreate temporary table \u8868\u540d\uff08\u4e0e\u6c38\u4e45\u8868\u4e00\u6837\uff09\uff1b<\/li>\n
                  2. \u4e34\u65f6\u8868
                    \ndrop table \u8868\u540d\uff1b<\/li>\n<\/ol>\n<\/li>\n
                  3. \n

                    \u4fee\u6539\u8868\u7684\u5b57\u7b26\u96c6
                    \nalter table \u8868\u540d character set \u5b57\u7b26\u96c6\u540d\u79f0;<\/p>\n<\/li>\n

                  4. \n

                    show global variables like 'local_infile';
                    \nset @@global.local_infile=1;\u6216\u8005set global local_infile=true;\uff08\u4e34\u65f6\u6539\uff0c\u91cd\u542f\u670d\u52a1\u5668\u65e0\u6548\uff09
                    \n\u6c38\u4e45\u6539\uff1amy.ini\u4e2d\u6dfb\u52a0\uff1alocal_infile=1.
                    \n\u624d\u5141\u8bb8\u5bfc\u5165CSV\u6587\u4ef6\u3002<\/p>\n<\/li>\n

                  5. \n

                    select version\uff08\uff09\uff1b\u67e5\u770b\u5f53\u524dmysql\u7248\u672c\u3002<\/p>\n<\/li>\n

                  6. \n

                    \u4fee\u6539\u8868\u4e2d\u6240\u6709\u5217\u7684\u5b57\u7b26\u96c6\u4e3autf8
                    \nalter table \u8868\u540d convert to character set utf8;<\/p>\n<\/li>\n

                  7. \n

                    \u5728\u4e0d\u540c\u7684\u5e93\u4e2d\u590d\u5236\u8868
                    \nuse \u81ea\u5df1\u7684\u5e93\uff1b
                    \ncreate table student as select * from teaching.student;<\/p>\n<\/li>\n

                  8. \n

                    \u590d\u5236\u8868\u7ed3\u6784\u521b\u5efa\u7ed3\u6784\u4e00\u6837\u7684\u8868
                    \ncreate table \u65b0\u8868\u540d like \u65e7\u8868\u540d\uff1b<\/p>\n<\/li>\n

                  9. \n

                    \u4fee\u6539\u5217\u7684\u5b57\u7b26\u96c6
                    \nALTER TABLE \u8868\u540d CHANGE \u5217\u540d \u65b0\u5217\u540d CHARACTER SET character_name [COLLATE ...];
                    \n\u5982\uff1aALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;<\/p>\n<\/li>\n<\/ol>\n

                    5\u6570\u636e\u63d2\u5165\u3001\u4fee\u6539\u3001\u5220\u9664<\/h2>\n
                      \n
                    1. insert\uff08replace\uff09\u63d2\u5165\u6570\u636e\n
                        \n
                      1. \u63d2\u5165\u4e00\u884c\u5b8c\u6574\u6570\u636e
                        \ninsert into \u8868\u540d valuses\uff08\u503c\u5217\u88681\u3001\u503c\u5217\u8868\uff09\uff1b<\/li>\n
                      2. \u63d2\u5165\u4e00\u884c\u6307\u5b9a\u5217\u6570\u636e
                        \ninsert into \u8868\u540d \uff08\u5217\u540d1\uff0c\u5217\u540d2\uff09values\uff08\u5217\u503c1\u3001\u5217\u503c2\uff09\uff1b<\/li>\n<\/ol>\n<\/li>\n
                      3. insert\uff08replace\uff09\u63d2\u5165\u591a\u884c\u6570\u636e
                        \nreplace into \u8868\u540d values\uff08\u7b2c\u4e00\u884c\u503c\uff09\uff0c\uff08\u7b2c\u4e8c\u884c\u503c\uff09\uff1b<\/li>\n
                      4. \u88682\u548c\u88681\u8868\u7ed3\u6784\u4e00\u6837\uff0c\u5c06\u88682\u7684\u6570\u636e\u590d\u5236\u5230\u88681\u4e2d\u3002
                        \ninsert into table \u88681 select * from table \u88682\uff1b<\/li>\n
                      5. set\u63d2\u5165\u4e00\u884c\u6570\u636e
                        \ninsert into \u8868\u540d set \u5217\u540d1=\u5217\u503c1\uff0c\u5217\u540d2=\u5217\u503c2\uff1b<\/li>\n
                      6. \u8868\u8bb0\u5f55\u7684\u4fee\u6539
                        \nupdate \u8868\u540d set \u5217\u540d1=\u65b0\u5217\u503c1\uff0c\u5217\u540d2=\u65b0\u5217\u503c2 where\u6761\u4ef6\uff1b<\/li>\n
                      7. \u8868\u8bb0\u5f55\u7684\u5220\u9664
                        \ndelete from \u8868\u540d where\u5b57\u53e5\uff1b<\/li>\n<\/ol>\n

                        6TXT\u6587\u4ef6\u5bfc\u5165mysql<\/h2>\n
                          \n
                        1. txt\u6587\u4ef6\u653e\u5165\u8981\u5bfc\u5165\u7684\u6570\u636e\u5e93\u6587\u4ef6\u4e2d<\/li>\n
                        2. txt\u5b57\u7b26\u96c6\u548c\u6570\u636e\u5e93\u5b57\u7b26\u96c6\uff0cclient\uff0c\u8868\uff0c\u5217\u7684\u5b57\u7b26\u96c6\u4e00\u81f4\u3002<\/li>\n
                        3. show variables like '%secure%';
                          \nmy.ini \u6587\u4ef6\u4e2d\u6dfb\u52a0secure_file_priv=""
                          \n+--------------------------+-------+
                          \n| Variable_name | Value |
                          \n+--------------------------+-------+
                          \n| require_secure_transport | OFF |
                          \n| secure_file_priv | |
                          \n+--------------------------+-------+<\/li>\n
                        4. show global variables like 'local_infile';
                          \nset @@global.local_infile=1;\u6216\u8005set global local_infile=true;\uff08\u4e34\u65f6\u6539\uff0c\u91cd\u542f\u670d\u52a1\u5668\u65e0\u6548\uff09
                          \n\u6c38\u4e45\u6539\uff1amy.ini\u4e2d\u6dfb\u52a0\uff1alocal_infile=1.
                          \n\u624d\u5141\u8bb8\u5bfc\u5165CSV\u6587\u4ef6\u3002<\/li>\n
                        5. \u5bfc\u5165\u547d\u4ee4\u4e3a
                          \nload data infile 'teacher.txt' into table \u8868\u540d;<\/li>\n<\/ol>\n

                          \u5728Windows\u5e73\u53f0\u4e0b\uff0c\u8fdb\u5165DOS\u7a97\u53e3\uff0c\u8f93\u5165\uff1achcp \uff0c\u53ef\u4ee5\u5f97\u5230\u64cd\u4f5c\u7cfb\u7edf\u7684\u4ee3\u7801\u9875\u4fe1\u606f\uff0c\u4f60\u53ef\u4ee5\u4ece\u63a7\u5236\u9762\u677f\u7684\u8bed\u8a00\u9009\u9879\u4e2d\u67e5\u770b\u4ee3\u7801\u9875\u5bf9\u5e94\u7684\u8be6\u7ec6\u7684\u5b57\u7b26\u96c6\u4fe1\u606f\u3002<\/p>\n

                          7\u6570\u636e\u5b8c\u6574\u6027<\/h2>\n
                            \n
                          1. \u521b\u5efa\u8868\u65f6\u5728\u5217\u4e0a\u521b\u5efa\u4e3b\u952e\uff08\u5217\u7ea7\u7ea6\u675f\uff09
                            \ncreate table \u8868\u540d\uff08\u5217\u540d \u6570\u636e\u7c7b\u578b not null primary key\uff0c
                            \n\u5217\u540d1 \u6570\u636e\u7c7b\u578b\uff09\uff1b<\/li>\n
                          2. \u590d\u5408\u4e3b\u952e\uff08\u8868\u7ea7\u7ea6\u675f\uff09
                            \nprimary key\uff08\u5217\u540d1\uff0c\u5217\u540d2\uff09\uff1b<\/li>\n
                          3. \u5220\u9664\u8868\u7684\u4e3b\u952e
                            \nalter table \u8868\u540d drop primary key\uff1b<\/li>\n
                          4. \u589e\u52a0\u4e3b\u952e
                            \nalter table \u8868\u540d add primary key(\u5217\u540d)\uff1b<\/li>\n
                          5. alter table \u8868\u540d add constraint \u5916\u952e\u540d
                            \nforeign key\uff08\u5217\u540d\uff09 references \u8868\u540d\uff08\u5217\u540d\uff09\uff1b<\/li>\n
                          6. \u81ea\u5b9a\u4e49\u7ea6\u675f\uff08check\uff09
                            \nalter table \u8868\u540d add constraint \u7ea6\u675f\u540d check\uff08\u5217\u540d>\u503c\uff09\uff1b
                            \n\u5220\u9664\uff1aalter table \u8868\u540d drop check \u7ea6\u675f\u540d\uff1b<\/li>\n
                          7. \u6dfb\u52a0\u552f\u4e00\u6027\u7ea6\u675f
                            \nalter table \u8868\u540d add unique\uff08\u5217\u540d\uff09\uff1b
                            \nalter table \u8868\u540d add constraint \u552f\u4e00\u6027\u7ea6\u675f\u540d unique(\u5217\u540d\uff09\uff1b
                            \n\u5220\u9664\uff1aalter table \u8868\u540d \u5217\u540d\uff1b\uff08\u5220\u9664\u8868\u4e2d\u7684\u67d0\u5217\uff09<\/li>\n
                          8. \u5220\u9664\u5916\u952e
                            \nALTER TABLE \u8868\u540d DROP FOREIGN KEY \u5916\u952e\u540d\uff1b<\/li>\n
                          9. \u521b\u5efa\u5916\u952e
                            \nalter table score add constraint \u5916\u952e\u540d foreign key
                            \n\uff08\u5217\u540d\uff09 reference \u53e6\u4e00\u8868\u7684\u8868\u540d\uff08\u5217\u540d\uff09\uff1b
                            \n\u6570\u636e\u7c7b\u578b\u4e00\u81f4\uff0c\u5b57\u7b26\u96c6\u4e00\u81f4\uff0c\u6709\u7d22\u5f15\uff0c\u8868\u4e2d\u6709\u6570\u636e\u5fc5\u987b\u6ee1\u8db3\u5916\u952e\u5426\u5219\u65e0\u6cd5\u6dfb\u52a0\u3002<\/li>\n
                          10. \u5220\u9664\u552f\u4e00\u6027\u7ea6\u675f
                            \n\u65e0\u540d\u5b50\uff1aalter table \u8868\u540d drop index \u5217\u540d\uff1b\uff08\u8be5\u5217\u4e3a\u552f\u4e00\u6027\u7ea6\u675f\u7684\u540d\u79f0\uff09
                            \n\u6709\u540d\u5b57\uff1aalter table \u8868\u540d drop index \u552f\u4e00\u6027\u7ea6\u675f\u540d\uff1b<\/li>\n<\/ol>\n

                            8\u7d22\u5f15\u548c\u89c6\u56fe<\/h2>\n
                              \n
                            1. \u67e5\u770b\u7d22\u5f15
                              \nshow index from \u8868\u540d\uff1b<\/li>\n
                            2. \u521b\u5efa\u7d22\u5f15
                              \ncreate index phone_index on student(phone asc);
                              \ncreate unique index cname_index on course(cname);
                              \ncreate index sc_index on score(studentno,courseno);<\/li>\n
                            3. \u521b\u5efa\u8868\u65f6\u521b\u5efa\u7d22\u5f15\uff0c\u524d\u7f00\u7d22\u5f15
                              \ncreate table if not exists teacher1(
                              \nteacherno char(6) not null comment'\u6559\u5e08\u7f16\u53f7',
                              \ntname char(8) not null,
                              \nmajor char(10) not null,
                              \nprof char(10) not null,
                              \ndepartment char(16) not null,
                              \nprimary key(teacherno),
                              \nunique index tname_index(tname),
                              \nindex dep_index(department(5))
                              \n);<\/li>\n
                            4. \u7ed9\u8868\u6dfb\u52a0\u7d22\u5f15
                              \nalter table teacher1 add index mark(tname,prof);<\/li>\n
                            5. \u5220\u9664\u7d22\u5f15
                              \ndrop index \u7d22\u5f15\u540d on \u8868\u540d\uff1b
                              \nalter table \u8868\u540d drop index \u7d22\u5f15\u540d\uff1b<\/li>\n<\/ol>\n

                              9\u6570\u636e\u5e93\u7684\u5bfc\u5165\u548c\u5bfc\u51fa<\/h2>\n

                              \u5bfc\u51fa\uff1a
                              \nDOS\u73af\u5883\uff1a
                              \n\u7b2c\u4e00\u6b65\uff1a\u8fdbbin\u76ee\u5f55
                              \ncmd
                              \n\u60c5\u51b51\u3001\u5982\u679cmysql\u5b89\u88c5\u5728D\u76d8
                              \nd: \/\/\u8fdb\u5165D\u76d8
                              \ncd D:\\Program Files\\MySQL\\MySQL Server 8.0\\bin
                              \n\u60c5\u51b52\u3001\u5982\u679cmysql\u5b89\u88c5\u5728C\u76d8
                              \ncd c:\\Program Files\\MySQL\\MySQL Server 8.0\\bin<\/p>\n

                              \u7b2c\u4e8c\u6b65\uff1a
                              \n\u60c5\u51b51\uff1b\u9ed8\u8ba4\u7aef\u53e33306
                              \nmysqldump -u root -p \u6570\u636e\u5e93\u540d>d:\/mydata.sql \/\/\u5c06\u6570\u636e\u5e93\u540d\u5bfc\u51fa\u5230D\u76d8\u4e0b\u7684mydata.sql\u6587\u4ef6\u3002
                              \n\u60c5\u51b52\uff1a\u4fee\u6539\u7aef\u53e3\u53f7\u4e3a3307
                              \nmysqldump -h 127.0.0.1 -P3307 -u root -p \u6570\u636e\u5e93\u540d>d:\/mydata.sql \/\/\u540c\u4e0a<\/p>\n

                              \u7b2c\u4e09\u6b65\uff1a
                              \n\u6839\u636e\u5c4f\u5e55\u63d0\u793a\u8f93\u5165\u5bc6\u7801\uff1a123456
                              \n\u5728D\u76d8\u4e0b\uff0c\u627e\u5230mydata.sql\u6587\u4ef6\u5373\u4e3a\u5bfc\u51fa\u7684\u5e93\u6587\u4ef6\u3002<\/p>\n

                              \u5bfc\u5165\uff1a
                              \n\u7b2c\u4e00\u6b65
                              \nmysql\u73af\u5883\uff1a
                              \n\u521b\u5efa\u6570\u636e\u5e93teaching
                              \n\u7b2c\u4e8c\u6b65
                              \ndos\u73af\u5883\uff0c\u8fdb\u5165bin\u76ee\u5f55\uff0c\u8f93\u5165\u5982\u4e0b\u547d\u4ee4
                              \nmysql -u root -p teaching<d:\\mydata.sql;<\/p>\n

                              \u7b2c\u4e09\u6b65\uff1a
                              \n\u6839\u636e\u5c4f\u5e55\u63d0\u793a\uff0c\u8f93\u5165\u5bc6\u7801\uff1a123456<\/p>\n

                              \u7b2c\u56db\u6b65\uff1a
                              \nmysql\u73af\u5883\u4e0b\uff0cuse teaching\uff0cshow tables \/\/\u9a8c\u8bc1\u6570\u636e\u5e93\u6210\u529f\u5bfc\u5165<\/p>\n

                              \u6570\u636e\u5bfc\u51652022\u7248<\/h3>\n
                                \n
                              1. \n

                                cmd \u6253\u5f00dos\u7a97\u53e3<\/p>\n<\/li>\n

                              2. \n

                                net start\u67e5\u770bmysql\u670d\u52a1\u662f\u5426\u6253\u5f00 net start mysql80<\/p>\n<\/li>\n

                              3. \n

                                mysql -u root -p --local-infile mysql -u root -P 3307 -p --local-infile\uff08\u5982\u679c\u5b89\u88c5mysql8\u65f6\u4fee\u6539\u7aef\u53e3\u53f7\uff0c\u7528\u6b64\u547d\u4ee4\uff09 \u8f93\u5165\u5bc6\u7801\uff08mysql\u767b\u5f55\u5bc6\u7801\uff09<\/p>\n<\/li>\n

                              4. \n

                                load data infile \u201cteacher.txt\u201d into table teacher;<\/p>\n<\/li>\n<\/ol>\n

                                teacher.txt \u53ea\u4fdd\u5b58\u6570\u636e\uff0c\u4e0d\u5305\u62ec\u5217\u540d\uff0c\u5404\u884c\u6587\u672c\u9700\u7528tab\u5206\u9694\uff0c\u6bcf\u884c\u6700\u540e\u4e5f\u7528tab\u5206\u9694\u3002<\/p>\n

                                teacher.txt\u5728teaching\u6570\u636e\u5e93\u6587\u4ef6\u5939\u4e2d\u6216\u8005D:\/ProgramData\/MySQL\/MySQL Server 8.0\/Data<\/p>\n

                                \u53ef\u4ee5\u4fee\u6539my.ini<\/p>\n

                                D:\\ProgramData\\MySQL\\MySQL Server 8.0\u627e\u5230my.ini\u6587\u4ef6\uff0c\u4fee\u6539\u5982\u4e0b\u8def\u5f84<\/p>\n

                                # Path to the database root<\/p>\n

                                datadir=D:\/ProgramData\/MySQL\/MySQL Server 8.0\/Data<\/p>\n

                                \u3000\u6570\u636e\u5e93\u8868\u6570\u636e\u5bfc\u51fa\u5bfc\u5165\u5230\u6587\u672c\u6587\u6863<\/h3>\n

                                \u7b2c\u4e00\u6b65\u4fee\u6539\uff1amy.ini
                                \nsecure-file-priv=""
                                \n\u53e6\u5b58\u4e3a\uff1a\u7f16\u7801\u683c\u5f0fANSI\u3002
                                \n\u7b2c\u4e8c\u6b65\u91cd\u542fmysql\u670d\u52a1\uff1a
                                \n\u53f3\u952e\u5355\u51fb\u6211\u7684\u7535\u8111\u2192\u7ba1\u7406\u2192\u670d\u52a1\u548c\u5e94\u7528\u7a0b\u5e8f<\/p>\n

                                \u7b2c\u4e09\u6b65\u6570\u636e\u5bfc\u51fa\u5230TXT<\/p>\n

                                select * from book
                                \ninto outfile 'd:\/myfile1.txt'
                                \nfields terminated by ','
                                \noptionally enclosed by '"'
                                \nlines terminated by '?';<\/p>\n

                                \u7b2c\u56db\u6b65\u6570\u636e\u5bfc\u5165\u5230mysql
                                \nload data infile 'd:\/myfile2.txt'
                                \ninto table book
                                \nfields terminated by ','
                                \noptionally enclosed by '"'
                                \nlines terminated by '?';<\/p>\n

                                \u673a\u623f\u7535\u8111\u6587\u6863\u6570\u636e\u5bfc\u5165mysql\u65b9\u6cd5\uff08\u4e24\u5217\u7684\u8868\u683c\uff09<\/h3>\n
                                  \n
                                1. \n

                                  \u4fee\u6539local_infile\u53d8\u91cf\u503c\uff08\u5728mysql\u63a7\u5236\u53f0\uff09
                                  \nshow global variables like 'local_infile';
                                  \nset global local_infile=true<\/p>\n<\/li>\n

                                2. \n

                                  \u4fee\u6539my.ini\u914d\u7f6e\u6587\u4ef6\uff08\u5148\u628amy.ini\u6587\u4ef6\u590d\u5236\u4e00\u4efd\uff09
                                  \nmy.ini\u6587\u4ef6\u6240\u5728\u7684\u4f4d\u7f6e\uff1aC:\\ProgramData\\MySQL\\MySQL Server 8.0
                                  \n\u4fee\u6539\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n

                                  # Secure File Priv.<\/p>\n

                                  # secure-file-priv="C:\/ProgramData\/MySQL\/MySQL Server 8.0\/Uploads"
                                  \nsecure-file-priv=""<\/p>\n

                                  \u53e6\u5b58\u4e3a ANSI<\/p>\n<\/li>\n

                                3. \n

                                  \u91cd\u542f\u670d\u52a1\u5668\uff1a\u53f3\u952e\u5355\u51fb\u201c\u8ba1\u7b97\u673a\u201d\u2192\u7ba1\u7406\u2192\u670d\u52a1\u548c\u5e94\u7528\u7a0b\u5e8f\u2192\u670d\u52a1\u2192\u53f3\u952e\u5355\u51fbmysql80\uff0c\u91cd\u65b0\u542f\u52a8<\/p>\n<\/li>\n

                                4. \n

                                  \u5728mysql\u63a7\u5236\u53f0\uff1a
                                  \nuse teaching;
                                  \nselect * from teach_course;\u67e5\u770b\u8868\u4e2d\u662f\u5426\u6709\u6570\u636e\u3002<\/p>\n

                                  load data infile "d:\\teach_course.txt" into table teach_course;
                                  \n\u6267\u884c\u8be5\u547d\u4ee4\u524d\u786e\u8ba4teach_course.txt\u6587\u6863\u5728d\u76d8\u4e0b\u3002<\/p>\n

                                  select * from teach_course;\u67e5\u770b\u8868\u4e2d\u662f\u5426\u6709\u6570\u636e\u3002<\/p>\n<\/li>\n<\/ol>\n

                                  teach_course.txt\u6587\u6863\u5185\u5bb9\u5982\u4e0b\uff1a
                                  \n\u6570\u636e\u95f4\u7528tab\u95f4\u9694\uff0c\u7528\u56de\u8f66\u6362\u884c\uff0c\u4fdd\u5b58\u4e3aANSI\u7f16\u7801\u683c\u5f0f\u3002
                                  \nt05001 c05103
                                  \nt05002 c05109
                                  \nt05003 c05127
                                  \nt05011 c05138
                                  \nt05017 c06108
                                  \nt06011 c06127
                                  \nt06023 c05127
                                  \nt07019 c08123
                                  \nt08017 c08123
                                  \nt08058 c08171
                                  \nt05017 c06172
                                  \nt06023 c06172<\/p>\n

                                  \u6570\u636e\u5e93\u8d26\u53f7\uff0c\u767b\u5f55\uff0c\u6388\u6743<\/h2>\n

                                  Mysql8.0root\uff1a
                                  \n\u521b\u5efa\u8d26\u53f7\uff1acreate user lidan@localhost IDENTIFIED by '123456';
                                  \n\u67e5\u770b\u8d26\u53f7\u6743\u9650\uff1ashow grants for 'lidan'@'localhost';
                                  \n\u7ed9\u8d26\u53f7\u6388\u6743\uff1ashow grants for 'lidan'@'localhost';<\/p>\n

                                  DOS\u7a97\u53e3\u7528\u65b0\u8d26\u53f7\u767b\u5f55\uff1a
                                  \n\u8fdb\u5165mysql\u76ee\u5f55C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin
                                  \n\u7528\u65b0\u8d26\u53f7\u767b\u5f55 mysql -u lidan -p<\/p>\n

                                  \n

                                  \u8fd9\u662f\u642c\u7684\u8001\u5e08\u7684\uff0c\u90fd\u6574\u7406\u5230\u8fd9\u4e00\u4e2a\u6587\u4ef6\u4e2d\u4e86\uff0c\u539f\u6765\u662f\u5728\u5f88\u591a\u4e2a.txt<\/code>\u6587\u4ef6\u4e2d\u7684\uff0c\u6211\u771f\u662f\u53d7\u4e0d\u4e86\u8981\u72e0\u72e0\u5410\u69fd\u4e86\uff0c\u90a3\u5c4e\u4e00\u6837\u7684\u6392\u7248\uff0c\u4e71\u4e03\u516b\u7cdf\u7684\u5e8f\u53f7\uff0c\u6709\u4e9b\u5730\u65b9\u534a\u62c9\u53ef\u673a\u83ab\u540d\u5176\u5999\u7684\u5185\u5bb9\uff0c\u6211\u771f\u662f.....\u96be\u868c\uff0c\u6211\u8fd9\u91cc\u53ea\u7b80\u5355\u7684\u5f52\u4e00\u4e0b\u5e8f\u53f7\u8c03\u4e00\u4e0b\u6392\u7248\uff0c\u6ca1\u6807\u5e8f\u53f7\u7684\u6587\u4ef6\u90fd\u653e\u5728\u4e86\u6700\u540e\u9762\u7684\u4e09\u7ea7\u6807\u9898\u91cc\u9762(\u6700\u540e\u4e00\u4e2a\u8d26\u53f7\u64cd\u4f5c\u8fd8\u662f\u4e8c\u7ea7\u6807\u9898)\uff0c\u4e0d\u8fc7 \u8fd8\u662f\u53d7\u4e0d\u4e86\uff0c\u592a\u5c4e\u4e86\uff01<\/p>\n","protected":false},"excerpt":{"rendered":"

                                  1\u5b57\u7b26\u96c6\u3001\u7cfb\u7edf\u53d8\u91cf \u67e5\u770bMYSQL\u6240\u652f\u6301\u7684\u5b57\u7b26\u96c6show charset;show character set […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[10],"class_list":["post-42","post","type-post","status-publish","format-standard","hentry","category-9","tag-mysql"],"_links":{"self":[{"href":"https:\/\/boki.shifengzhi.top\/index.php?rest_route=\/wp\/v2\/posts\/42","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/boki.shifengzhi.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/boki.shifengzhi.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/boki.shifengzhi.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/boki.shifengzhi.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=42"}],"version-history":[{"count":1,"href":"https:\/\/boki.shifengzhi.top\/index.php?rest_route=\/wp\/v2\/posts\/42\/revisions"}],"predecessor-version":[{"id":43,"href":"https:\/\/boki.shifengzhi.top\/index.php?rest_route=\/wp\/v2\/posts\/42\/revisions\/43"}],"wp:attachment":[{"href":"https:\/\/boki.shifengzhi.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=42"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/boki.shifengzhi.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=42"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/boki.shifengzhi.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=42"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}